Results 1 to 9 of 9

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Resolved [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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import date from csv into MySQLBulkloader wiith different date format

    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.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import date from csv into MySQLBulkloader wiith different date format

    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?

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import date from csv into MySQLBulkloader wiith different date format

    Did you try following the advice I provided?

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import date from csv into MySQLBulkloader wiith different date format

    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 '"' .

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import date from csv into MySQLBulkloader wiith different date format

    Quote Originally Posted by GideonE View Post
    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 View Post
    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:
    1. b1.Expressions.Add("SET Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")
    or:
    vb.net Code:
    1. b1.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import date from csv into MySQLBulkloader wiith different date format

    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
    Attached Files Attached Files
    Last edited by GideonE; May 31st, 2021 at 04:19 AM.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Import date from csv into MySQLBulkloader wiith different date format

    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.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Nov 2016
    Location
    South Africa
    Posts
    141

    Re: Import date from csv into MySQLBulkloader wiith different date format

    Thank you jmcilhinney.

    That works perfectly. Thank you for your patience.

    Regards

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width