1

I am using MySqlBulkLoader to import a pipe delimited file, with over 250K rows, to a MySQL database table using a vb.net application.

This will be a daily task so I am trying to get this as automated and fast as possible.

I have no control over the creation of the files.

All seems to work fine with a couple of exceptions. The one I am currently dealing with is an anomaly in a single row of the data where one of the pipes is preceded by a backslash |

The backslash is a genuine part of the data that needs to be imported, however, combined with the pipe seems to act as a control. The result is the error:

Row X doesn't contain data for all columns

I am currently trying various workarounds but wonder if anyone has experience with this, or knows a solution to this issue.

Using MySQLConnection
            bulk.TableName = "MyTable"
            Application.DoEvents()
            bulk.FieldTerminator = "|"
            bulk.LineTerminator = "\r\n"  
            bulk.FileName = SourceFile        
            bulk.NumberOfLinesToSkip = 1    

            bulk.Columns.Clear()
            For Each s In cols
                bulk.Columns.Add(s)         
            Next

            rows = bulk.Load()
        End Using

EDIT

I added the following to prep the file prior to import

My.Computer.FileSystem.WriteAllText(SourceFile, My.Computer.FileSystem.ReadAllText(SourceFile).Replace("\|", "|"), False)

This works and allows to import to complete, however the search and replace adds approximately 8 minutes on to the time, which is much longer than the time it takes to actually import the 250K+ rows, so not ideal.

EDIT

This is a sample of the data in the format received

Standard Row - This works

345634534556|1|100|0|NA|2|55402|1.2|NA|0|0|0|0|NEW||1.2|1||||178-|||The Green||||Manchester||M1 XXX|M1|XXX|Customer 1|DEV||2019-05-19|NEW|0|2021-12-02|0||02903428||90X590X473|

Anomalous Row - Causing the error

767655444567|1|100|0|NA|2|52270|2.2|NA|0|0|0|0|NEW||2.3|1||||154\|||The Green||||Manchester||M1 XXX|M1|XXX|Customer 2|DEV||2020-07-19|NEW|0|2021-11-25|0||09304415||9X059X6087|
JPC
  • 33
  • 4
  • Can you post sample data of what the file looks like? – Timothy G. Jul 21 '22 at 17:17
  • Hi @TimothyG., I have added two rows as a sample of the data, 2 rows. Any help is appreciated. – JPC Jul 21 '22 at 17:42
  • Use the commandline "mysql" and enter a suitable `LOAD DATA` statement. – Rick James Jul 22 '22 at 03:42
  • Hi @RickJames, thanks for the reply. I'm relatively new to MySQL and have never used the command line to use any of it's functions. Can this be accomplished remotely over a network and be called from within a vb.net application? Do you have an example of the code? Thanks for any help. – JPC Jul 22 '22 at 10:33
  • Hi @JPC Looking at the example data you have put in Excel and then doing Text to columns shows that there are 44 pipes in the first example and 45 in the second example - could one of the data fields be containing a pipe that isn't being enclosed and it throwing this off? – clcordell Jul 22 '22 at 13:25
  • Hi @clcordell, thanks for the feedback. That would have been great, unfortunately this is not the answer. I had unfortunately introduced the additional pipe in the second example while modifying the data so no personal data was posted on stackoverflow. Completely my fault, I have now corrected this, but the original issue is still unresolved. – JPC Jul 25 '22 at 14:33

0 Answers0