Results 1 to 3 of 3

Thread: [RESOLVED] MySQL Bulk Loader from .csv without column names

  1. #1

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

    Resolved [RESOLVED] MySQL Bulk Loader from .csv without column names

    I have the following vb.net code to load a .csv file very fast into a MySQL table :

    Code:
     Dim bl As New MySqlBulkLoader(conn)
            bl.TableName = "elektrisiteit_staging"
            bl.FieldTerminator = ","
            bl.LineTerminator = "\r\n"
            bl.FileName = InvoerFile
            bl.NumberOfLinesToSkip = 3
            bl.FieldQuotationCharacter = """"
            bl.Columns.AddRange({"@Datum", "TimeOfUse", "UnitsOfMeasurement", "Verbruik", "@EnergyCharge"})
            bl.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")
            bl.Expressions.Add("EnergyCharge =   Replace((substring(@EnergyCharge, instr(@EnergyCharge, 'R') + 1)),',','') ")
    
    
    
            Try
                conn.Open()
                Dim count As Integer = bl.Load()
                '' MsgBox("Inside try")
                '' conn.Close()
            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            End Try

    However how do I handle that if the .csv does not have headers ?

    From MySQL I understand you can use


    Code:
    (column1, column2, @column3, ...)
    How can I use the above in a VB.net command. I have tried removing the quotes, replacing the names with column1,column2 etc.
    Also I think the bl.Columns.AddRange is wrong. I still want to do column mapping using user variables.

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

    Re: MySQL Bulk Loader from .csv without column names

    I wouldn't think that it matters whether the file has headers or not. You are providing the mapping of source columns to variables and destination columns here:
    vb.net Code:
    1. bl.Columns.AddRange({"@Datum", "TimeOfUse", "UnitsOfMeasurement", "Verbruik", "@EnergyCharge"})
    and variables to destination columns here:
    vb.net Code:
    1. bl.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")
    2. bl.Expressions.Add("EnergyCharge =   Replace((substring(@EnergyCharge, instr(@EnergyCharge, 'R') + 1)),',','') ")
    The source column headers appear to be irrelevant and only the column positions seem to matter. I would think the only difference it would make is that you'd have one less row to ignore here:
    vb.net Code:
    1. bl.NumberOfLinesToSkip = 3

  3. #3

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

    Re: MySQL Bulk Loader from .csv without column names

    Thank you jmcilhinney. You were right (as always).
    I was over-complicating things. Thank you for getting my mind out of a rabbit hole.

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