LOAD DATA LOCAL INFILE ... help!

[shamim429]

Dear All,

Does any one know how to allow user select a .cvs file and upload that file into MySQL database. I have tried to use "LOAD DATA LOCAL …" but did not work. Does any one have working sample for me?
I am not sure how to use a text file (.csv file) with MySqlLoader. Is MySqlLoader a substitute of "LOAD DATA LOCAL INFILE.." command? Also, how do I set client Direct=false mode?

Code: Select all

   Private Sub btn_LoadMyList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_LoadEmailList.Click

        Dim myConnection As MySqlConnection = New MySqlConnection(SSL_Conn_MainDB())
        myConnection.SslOptions.CACert = sslCACert()
        myConnection.SslOptions.Cert = sslCert()
        myConnection.SslOptions.Key = sslKey()

        Dim loader As MySqlLoader
        loader = New MySqlLoader
        loader.Connection = myConnection
        loader.TableName = "load_table"
        Try
            loader.CreateColumns()
            loader.Open()
            Dim i As Integer
            For i = 1 To 10000
                loader.SetValue("id", i)
                loader.SetValue(1, "test string")
                loader.SetValue("date_field", DateTime.Now)
                loader.NextRow()
            Next i
            loader.Close()
        Finally
            myConnection.Close()
        End Try
    End Sub

Below are steps I took to load .csv file to load into MySQL, but very slow process if data file has grater than 50k records.
- read .csv file in to datagrid
- read each cell to create INSERT query

Code: Select all

Private Sub btnReadCSVFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadCSVFile.Click
 Try
   Me.Cursor = Cursors.WaitCursor

   'code to read .csv or .txt file (eg. _*.csv)
   Dim openFile As New OpenFileDialog
   openFile.Filter = "CSV File (*.csv) | *.csv|Text Files (*.txt)|*.TXT"
   Dim myResult As DialogResult
   myResult = openFile.ShowDialog

   If openFile.FileName  "" Then
       ‘seperate file name and directory
       Dim file_source(1) As String
       file_source = openFile.FileName.Split("_"c)
       file_source(1) = "_" & file_source(1)

       Dim SQL As String = "SELECT * FROM " & file_source(1)
       Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file_source(0) & ";Extended Properties=text;"


       Dim objConn As New OleDbConnection(sConnectionString)

       objConn.Open()

       Dim objCmdSelect As New OleDbCommand(SQL, objConn)

       Dim objAdapter1 As New OleDbDataAdapter

       objAdapter1.SelectCommand = objCmdSelect
       Dim objDataset1 As New DataSet
       objAdapter1.Fill(objDataset1, "Test")

       dg1RCount.Text = objDataset1.Tables("Test").DefaultView.Count
       dg1CCount.Text = objDataset1.Tables("Test").Columns.Count

       ‘load data in datagrid1 for preview
       DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
       objConn.Close()
       
   End If

        Catch ex As Exception
   MsgBox("Error: " & ex.Message, MsgBoxStyle.Exclamation, " Line 5890")
   Exit Try
   Finally
   Me.Cursor = Cursors.Default

   btnLoadData.Enabled = True
 End Try

End Sub




Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click

   Try
   Dim personid, company_id, user_name, password, e_code, qEden As String

   Me.Cursor = Cursors.WaitCursor
   Dim qstr As String

   'Read each Row
   Dim R As Integer = 0
   For R = R To dg1RCount.Text - 1
	'assign new personid, company_id, user_name, password for each record
       personid = Mid(Guid.NewGuid.ToString.Replace("-", ""), 1, 12).ToUpper
       company_id = Mid(Guid.NewGuid.ToString.Replace("-", ""), 1, 12).ToUpper

       user_name = personid
       password = company_id

       'Read each Column
       Dim C As Integer = 0
       qstr += "(" & Chr(34) & personid & Chr(34) & "," & Chr(34) & company_id & Chr(34) & "," & Chr(34) & user_name & Chr(34) & "," & Chr(34) & password & Chr(34) & "," & Chr(34)
       For C = C To dg1CCount.Text - 1
           qstr = qstr & Me.DataGrid1(R, C) & Chr(34) & "," & Chr(34)
       Next C
       'qstr = Mid(qstr, 1, qstr.Length - 2)
       qstr = qstr & txtUserName.Text & Chr(34) & "),"

   Next R
   qstr = Mid(qstr, 1, qstr.Length - 1)
   qstr = "INSERT INTO person_db (personid,company_id,user_name,password,title,first_name,mi,last_name,name_suff,job_title,company_name,department,address1,address2,city,state,zip,country,phone,fax,email,list_name,imn_users) VALUES " & qstr

'use exe_Query sub to run Query   
exe_Query(strQuery)

   Me.Cursor = Cursors.Default
   MsgBox("Data uploaded successfully.", +MsgBoxStyle.Information, "Information Management Network, LLC")

   '
   Catch ex As Exception
   MsgBox("Error: " & ex.Message, MsgBoxStyle.Exclamation, " Line 10517")
   Exit Try
  Finally
 Me.Cursor = Cursors.Default
 
End Try

End Sub

Any help or comments will be greatly appreciated.

Thank you,
http://shamim.us

[Alexey]

Set Direct=false in your connection string. This will enable usage of LOAD DATA LOCAL INFILE query.