Results 1 to 9 of 9

Thread: Using MysqlBulkloader

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2016
    Posts
    70

    Using MysqlBulkloader

    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
    Last edited by GideonE; Mar 3rd, 2021 at 08:43 PM.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,586

    Re: Using MysqlBulkloader

    Quote Originally Posted by GideonE View Post
    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.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,586

    Re: Using MysqlBulkloader

    Quote Originally Posted by GideonE View Post
    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.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,586

    Re: Using MysqlBulkloader

    Quote Originally Posted by GideonE View Post
    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.
    vb.net Code:
    1. Dim loader As New MySqlBulkLoader
    2.  
    3. 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.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Nov 2016
    Posts
    70

    Re: Using MysqlBulkloader

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

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Nov 2016
    Posts
    70

    Re: Using MysqlBulkloader

    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
    Code:
    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 :

    Code:
    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

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,288

    Re: Using MysqlBulkloader

    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.
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Nov 2016
    Posts
    70

    Re: Using MysqlBulkloader

    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

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    106,586

    Re: Using MysqlBulkloader

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

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