-
Jun 15th, 2021, 11:45 PM
#1
Thread Starter
Addicted Member
[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.
-
Jun 16th, 2021, 12:17 AM
#2
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:
bl.Columns.AddRange({"@Datum", "TimeOfUse", "UnitsOfMeasurement", "Verbruik", "@EnergyCharge"})
and variables to destination columns here:
vb.net Code:
bl.Expressions.Add("Datum = STR_TO_DATE(@Datum, '%d-%m-%Y')")
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:
bl.NumberOfLinesToSkip = 3
-
Jun 17th, 2021, 03:23 AM
#3
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|