[RESOLVED] Using MysqlBulkloader

[GideonE]

Hi all

I am using Mysql and VB.Net (Visual Studio) to create an application.

I have large .csv files to import into Mysql tables. I would like to use MYSQLBulkloader to speed up things. I do not want all the columns in the csv files to be imported. Is column mappings possible with MySQLBulkloader?. Also some of the csv files has headers and others not.

I have searched everywhere but can only get examples/tutorials on C#. Converting these to VB.net were unsuccessful.
The MySQL table has a ID field(first) which is a primary key with auto increment.

Can any please direct me to a good tutorial?

Regards

[jmcilhinney]

I have searched everywhere but can only get examples/tutorials on C#. Converting these to VB.net were unsuccessful.

I suggest that you download and install Instant VB from Tangible Software Solutions. It is the best C# to VB converter I have seen and will probably do anything you need. If there are any issues with the converted code, post the relevant parts of both and we will probably be able to help you fix it.

[jmcilhinney]

Also some of the csv files has headers and others not.

I've never used the MySqlBulkLoader class - I'd never even heard of it before reading your post - but a web search led me to the documentation and about 30 seconds of perusing that led me to the NumberOfLinesToSkip property. It is set to zero by default but you would presumably set it to 1 for files with column headers.

[jmcilhinney]

Is column mappings possible with MySQLBulkloader?

Also courtesy of the documentation, there's a Columns property that is a List(Of String) that contains the column names. You add the names of the columns you want populated to that, e.g.

Dim loader As New MySqlBulkLoader
 
loader.Columns.AddRange({"Column1", "Column2", "Column3"})

There is also an Expressions property if you don't want a direct 1:1 mapping of data in the source to columns in the destination.

If you haven't already, you really ought to read that documentation. It's also well worth reading the documentation for the SQL statement that the class uses under the hood.

[GideonE]

Thank you, I agree a much better converter than the ones I tried.

[GideonE]

Thank you jmcilhinney.

First let me acknowledge you for your help.

I have read your suggested documentation. I must say MySQLBulkloader is very fast.

The data was successfully loaded when I do not have a ID column (PK,NN,UQ,AI).
When I inserted a ID column and use

bl.Columns.AddRange({"LidNo", "Lidnaam", "Plaasno"})

I get an error message :Row 1 was truncated; it contained more data then there was input columns.
I cannot figure out what my mistake is. I understand that the names of columns in the code refers to the fields in the database table and not the .csv (the names differ).


My complete code looks like this :

Option Explicit On
Option Infer On

Imports MySql.Data.MySqlClient
Public Class Form1
	Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
		Dim connStr As String = "server=localhost;userid=root;password=GiddyMySQL#1;database=Lutzville"
		Dim conn As New MySqlConnection(connStr)
		''Dim foreign_key_checks As [SET] = 0
		Dim bl As New MySqlBulkLoader(conn)
		bl.TableName = "oesskattings"
		bl.FieldTerminator = ","
		bl.LineTerminator = "\r\n"
		bl.FileName = "D:/Wingerdbou/LutzvilleProgram MySQL/EzyWineData/skedp_01.csv"
		bl.NumberOfLinesToSkip = 1
		bl.FieldQuotationCharacter = """"

		bl.Columns.AddRange({"LidNo", "Lidnaam", "Plaasno"})

		''bl.Columns = {"LidNo", "Lidnaam", "Plaasno"}


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



	End Sub

End Class

Regards

[techgnome]

The table has 4 columns, you supplied 3, so it's telling you the row didn't contain enough input. It worded weirdly, I know. It's been a long time since I've used the SQLBulkImport, but it's a fantastic class. I used it to build a mass imported for my last emplyer were we used XML files to dynamically configure different files to be imported on the fly. Ideally the best way when dealing with mass import data is to import it into a staging table first, where the table mirrors the data to be imported. this gives you the chance to scrub the data and make sure it's clean first before importing it into its final table. I'm trying to think how I've handled Identity columns before. You may need to poke around the documentation. There may be away to tell the loader to ignore columns, or that a column is an index auto column or something. The problem with Bulk operations, including this one, is that it bypasses the normal insert and logging functions, so i don't remember how well it handles identity columns. It may be a reason why I always opted for staging tables too. Dump my data into there, scrub it, make sure it's good, then 90% of the time, I have to do lookups, or transformations anyways, so that's a good place for it, then do a massive Select insert into all of the rows that passed the scrubbing.

-tg

EDIT : I kept referencing SQLBulkImport, which is the SQL Server version... I completely skipped over the fact that you're using MySQLLoader, for MySQL... so your experience may vary.

[GideonE]

Thank you techgnome

As far as I understand MySQLBulkloader differs from SQLBulkCopy that it reads from a file whereas SQLBulkCopy reads from a datatable. See this reply https://stackoverflow.com/a/23537155.
Also the MySQL table has many columns not only 4.

Regards

[jmcilhinney]

So how about you show us all the relevant information, like a simple example of files that work and don't work, as well as the table schema?

[GideonE]

Thank you all. I keep forgetting to reply. The solution was as suggested to use a staging table. That is an identical table as the original excluding the identity column. I then loaded data into this table from the .csv. Then I copied the staging table's values to the actual table.