[RESOLVED] Import date from csv into MySQLBulkloader wiith different date format

I have the following to import from a .csv

Code:

---

Dim bl As New MySqlBulkLoader(conn)
bl.TableName = "elektrisiteit_staging"
bl.FieldTerminator = ","
bl.LineTerminator = "\r\n"
bl.FileName = DataFilesInvoer & "Eskom - Consumption History.csv"
bl.NumberOfLinesToSkip = 3
bl.FieldQuotationCharacter = """"





Try
conn.Open()
Dim count As Integer = bl.Load()
'' MsgBox("Inside try")
conn.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try

---

The date column in the csv is in format 25-04-2021. Importing it in MySQL gives an error : incorrect date value '25-04-2021'. I understand the issue. MySQL takes a different date format. Although searching and trying everywhere, I just cannot figure out how to change the bulkloader.

I know I should be using something like :

Code:

---

(@Datum,TimeOfUse,UnitsOfMeasurement,Verbruik,EnergyCharge)
  SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')

---

But how?

Regards

I've never actually done this myself so I'm not 100% sure but I believe that the Expressions property is a collection of SQL statements used to transform source data to appropriate values for the destination. I'd suggest looking for some examples, because the documentation has bugger all, but I would guess that you would add your SET statement to that collection in some form.

Hi jmcilhinney

OK so this is working in MySQL workbench :

Code:

---

LOAD DATA INFILE "D:/Wingerdbou/LutzvilleProgram/InvoerDataFiles/Eskom - Consumption History.csv"
INTO TABLE elektrisiteit_staging
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES

(@Datum,TimeOfUse,UnitsOfMeasurement,Verbruik,EnergyCharge)
SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')

---

This is working fine.



I then use this in VB.Net :
(Important is to to add "Allow User Variables=True" after the password in the connectionstring.)

Code:

---

Dim InsertElektrisiteit_stagingQuery As String = "LOAD DATA INFILE '" & InvoerFile & "' " &
                                                            "INTO TABLE elektrisiteit_staging " &
                                                            "FIELDS TERMINATED BY ',' " &
                                                            "LINES TERMINATED BY '\r\n' " &
                                                            "ENCLOSED BY ' " ' " &
                                                            "IGNORE 3 LINES " &
                                                            "(@Datum,TimeOfUse,UnitsOfMeasurement,Verbruik,EnergyCharge) " &
                                                            "SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y') "

Dim mycommandInsertElektrisiteit_staging As New MySqlCommand(InsertElektrisiteit_stagingQuery)
        mycommandInsertElektrisiteit_staging.Connection = conn
        conn.Open()
        mycommandInsertElektrisiteit_staging.ExecuteNonQuery()
        mycommandInsertElektrisiteit_staging.Connection.Close()

---

However the last single quote in "ENCLOSED BY ' " ' " & comments out the rest of the line and the query does not execute.

Code:

---

' "ENCLOSED BY ' " & Chr(34) & " ' " &

---

and ...

Code:

---

enclosed by '""' " &

---

Nothing I have tried seems to work.

Any ideas?

Did you try following the advice I provided?

Hi jmcilhinney

I could not find anything to help me. I have the following MySql query that works perfectly :

Code:

---

LOAD DATA INFILE "D:/Wingerdbou/LutzvilleProgram/InvoerDataFiles/Eskom - Consumption History.csv"
INTO TABLE elektrisiteit_staging
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 3 LINES
(@Datum,TimeOfUse,UnitsOfMeasurement,Verbruik,EnergyCharge)
SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')

---

I now want to use the above in a vb.Net command, but I cannot the problem is with ENCLOSED BY '"' .

Quote:

---

Originally Posted by GideonE

I now want to use the above in a vb.Net command, but I cannot the problem is with ENCLOSED BY '"' .

---

There is no problem. It's simply a literal double quote in a literal string, so you handle that the same way you always do. You already know how to do that because you're already doing it in the code in post #1.

Quote:

---

Originally Posted by GideonE

I could not find anything to help me.

---

Other than your own code and what I explicitly told you, that is. Look at your SQL code and look at your VB code. Notice how there's a pretty much 1:1 correspondence between directives in the SQL and properties in the VB? As I already said, the last line of your SQL code basically corresponds to the Expressions property so you presumably need to add that line to that collection. I would expect that one of these two options would do the job:

vb.net Code:

b1.Expressions.Add("SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")

or:

vb.net Code:

b1.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")

Thank you for your continued support.

Your first suggestion b1.Expressions.Add("SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')") gives the following error:

MySql.Data.MySqlClient.MySqlException (Ox80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')' at line 1

BTW I am using MySQL 8.0.23 (MySQLCommunity - GPL)

Your second suggestion b1.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')") gives the following error :

MySql.Data.MySqlClient.MySqlException (Ox80004005): Incorrect date value: '25-04-2021' for column 'Datum' at row 1

I am attaching my .csv file as a .txt. Once again thank you in advance.

Regards

The first option is obviously wrong then, which I expected might be the case. Each expression exists specifically to set a column, so the SET is obviously implicit. It appears that the second option is correct and the error message doesn't actually relate to that but is just your original issue again, i.e. the data in the file is being mapped directly to the column instead of being processed by that expression.

As I said previously, there's pretty much a 1:1 correspondence between the parts of the SQL code and the parts of the VB code. Each line of that SQL code basically maps to a property assignment in the VB code. The reason for that correspondence is that, under the hood, the MySqlBulkLoader class is going to generate that SQL code based on your VB code and then execute it against the database.

Look at the second last line. What is that? It's the list of columns to be mapped with an @ prefix on the one that won't be mapped directly. Have you read the documentation for the MySQLBulkloader class? I did and you should have too. It's a bit thin but you get the basics, including the fact that there is a Columns property that is a list of column names. Hey! You've got a list of column names in your SQL code. I wonder if there's a correspondence there. I suspect that what you need to change in your original code would be this:

Code:

---

Dim bl As New MySqlBulkLoader(conn)
bl.TableName = "elektrisiteit_staging"
bl.FieldTerminator = ","
bl.LineTerminator = "\r\n"
bl.FileName = DataFilesInvoer & "Eskom - Consumption History.csv"
bl.NumberOfLinesToSkip = 3
bl.FieldQuotationCharacter = """"
bl.Columns.AddRange({"@Datum", "TimeOfUse", "UnitsOfMeasurement", "Verbruik", "EnergyCharge"})
bl.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")

---

That may not be exactly right but, if it's not, I suspect that it will be fairly close.

Thank you jmcilhinney.

That works perfectly. Thank you for your patience.

Regards