[RESOLVED] Optimize import to DB

[bodylojohn]

Hello Everybody,

With the code below I want to import a .csv file into MS Access.
The .csv file can contain more than 25.000 lines.
The code I have works! But... it runs like 10 minutes.
My questios is: Can I optimize my code so it runs much faster?

Thanks in advance...

Private Sub LoopDoorRegels()
   'Open the .csv file
    Dim sr As StreamReader = File.OpenText(strBestandslocatie)
    ProgressBar1.Minimum = 0
    ProgressBar1.Maximum = intTotIntelezen
    ProgressBar1.Step = 1
    ProgressBar1.Value = 0

    Dim line() As String
    Dim intAantalIngelezen As Integer = 0
    Do While sr.Peek() >= 0
      line = sr.ReadLine.Split(";")
      If line(3).Trim.ToString = strCheckPeriode Then
        ProgressBar1.Minimum = 0 : ProgressBar1.Maximum = 1 : ProgressBar1.Value = 1
        MsgBox("Deze periode is reeds ingelezen en verwerkt.")
        blnTblBestaat = True
        Exit Sub
      End If
      InsertDetails(line(0).Trim, line(1).Trim, line(2).Trim, line(3).Trim, line(4).Trim, line(5).Trim, line(6).Trim)
      intAantalIngelezen = intAantalIngelezen + 1
      ProgressBar1.PerformStep()
      Me.lblTeller.Text = "Aantal ingelezen: " & intAantalIngelezen.ToString & "/"
      Me.lblTeller.Refresh()
    Loop
    sr.Close()
    sr.Dispose()
    blnTblBestaat = False
  End Sub

  Private Sub InsertDetails(ByVal strKP As String, ByVal strPersNR As String, ByVal strNaam As String, ByVal strPeriode1 As String, _
                            ByVal strLooncode As String, ByVal strWaarde As String, ByVal strBTW As String)
    Dim strInsert_SQL As String = String.Format("INSERT INTO tblIMPORT " & _
                                      "(Kostenplaats,PersoneelsNummer,Naam,Periode,Looncode,Waarde,BTW,DatumInlezen) " & _
                                      "VALUES " & _
                                      "(@Kostenplaats,@PersoneelsNummer,@Naam,@Periode,@Looncode,@Waarde,@BTW,@DatumInlezen)")

    Dim cmdInsert As New OleDbCommand(strInsert_SQL, connection)

    cmdInsert.Parameters.AddWithValue("@Kostenplaats", strKP.Trim)
    cmdInsert.Parameters.AddWithValue("@PersoneelsNummer", strPersNR.Trim)
    cmdInsert.Parameters.AddWithValue("@Naam", strNaam.Trim)
    cmdInsert.Parameters.AddWithValue("@Periode", strPeriode1.Trim)
    cmdInsert.Parameters.AddWithValue("@Looncode", strLooncode.Trim)
    cmdInsert.Parameters.AddWithValue("@Waarde", strWaarde.Trim)
    cmdInsert.Parameters.AddWithValue("@BTW", strBTW.Trim)
    cmdInsert.Parameters.AddWithValue("@DatumInlezen", DateTime.Now.ToString("dd-MM-yyyy HH:mm"))

    connection.Open()
    cmdInsert.ExecuteNonQuery()
    connection.Close()
    cmdInsert.Dispose()
    cmdInsert = Nothing
  End Sub

[dunfiddlin]

A .csv file isn't delimited with ';' and doesn't contain extraneous spaces. Reading the file line by line and interrupting the process to update a progress bar certainly isn't helping. Read the file in one go and manipulate it in memory. As you are repeating the same operation it doesn't make a lot of sense to open and close the database on every single transaction nor to dispose of the command. Rather than having a separate sub why not include the database commands in the same loop as you are processing the lines?

[bodylojohn]

Thanks for the reply dunfiddlin,

I tried your pointers:

 Private Sub LoopDoorRegels()
    LeesSysteemInstelling(1) 'Haal de periode op uit de systeeminstellingen welke is opgeslagen na het selecteren van het in te lezen bestand.
    ControleerPeriode("tblFACTUUR", strSI_Waarde) 'Vergelijk de periode uit de bestandsnaam met de periode uit de tabel tblIMPORT
    'Open het .csv bestand en lees dit uit
    Dim sr As StreamReader = File.OpenText(strBestandslocatie)
    ProgressBar1.Minimum = 0
    ProgressBar1.Maximum = intTotIntelezen
    ProgressBar1.Step = 1
    ProgressBar1.Value = 0
    Dim line() As String
    Dim intAantalIngelezen As Integer = 0

    Dim strInsert_SQL As String = String.Format("INSERT INTO tblIMPORT " & _
                                     "(Kostenplaats,PersoneelsNummer,Naam,Periode,Looncode,Waarde,BTW,DatumInlezen) " & _
                                     "VALUES " & _
                                     "(@Kostenplaats,@PersoneelsNummer,@Naam,@Periode,@Looncode,@Waarde,@BTW,@DatumInlezen)")

    Dim cmdInsert As New OleDbCommand(strInsert_SQL, connection)
    connection.Open()
    Do While sr.Peek() >= 0
      line = sr.ReadLine.Split(";")
      If line(3).ToString = strCheckPeriode Then
        ProgressBar1.Minimum = 0 : ProgressBar1.Maximum = 1 : ProgressBar1.Value = 1
        MsgBox("Deze periode is reeds ingelezen en verwerkt.")
        blnTblBestaat = True
        Exit Sub
      End If
      cmdInsert.Parameters.AddWithValue("@Kostenplaats", line(0))
      cmdInsert.Parameters.AddWithValue("@PersoneelsNummer", line(1))
      cmdInsert.Parameters.AddWithValue("@Naam", line(2))
      cmdInsert.Parameters.AddWithValue("@Periode", line(3))
      cmdInsert.Parameters.AddWithValue("@Looncode", line(4))
      cmdInsert.Parameters.AddWithValue("@Waarde", line(5))
      cmdInsert.Parameters.AddWithValue("@BTW", line(6))
      cmdInsert.Parameters.AddWithValue("@DatumInlezen", DateTime.Now.ToString("dd-MM-yyyy HH:mm"))
      cmdInsert.ExecuteNonQuery()
      intAantalIngelezen = intAantalIngelezen + 1
    Loop

    connection.Close()
    cmdInsert.Dispose()
    cmdInsert = Nothing
    sr.Close()
    sr.Dispose()
    blnTblBestaat = False
  End Sub

But now it seems to be runnin even longer.

[jmcilhinney]

I would use a TextFieldParser to read the data and populate a DataTable, then use a data adapter to save the whole lot in one go.

On a separate note, why on earth are you saving the current date and time to the database as text? Pretty much every database can store date/time values and Access is no exception. If you have a date/time value and a database can store a date/time value, why would anyone think that it's a good idea to convert the date/time to text and then store that? If you wanted to store numbers would you convert them to text? If not then why would you do so with dates/times?

[bodylojohn]

I would use a TextFieldParser to read the data and populate a DataTable, then use a data adapter to save the whole lot in one go.

On a separate note, why on earth are you saving the current date and time to the database as text? Pretty much every database can store date/time values and Access is no exception. If you have a date/time value and a database can store a date/time value, why would anyone think that it's a good idea to convert the date/time to text and then store that? If you wanted to store numbers would you convert them to text? If not then why would you do so with dates/times?

Thanks JM,

I think that I have my datatable filled with data from the .csv file.

How can I save the data from my datatable to an excisting table in MS Access? I know I have to use the data adapter. But as far as I know using the data adapter means saving the data to the table row by row???
Do I have to do it row by row?
Or is it also possible at once?

Thanks again for the help

[jmcilhinney]

The whole point of a data adapter is specifically so you don't have to save row by row. You call Update and pass the DataTable and that's it. That's one of the examples in my Retrieving & Saving Data thread in the CodeBank, which you can find by following the CodeBank link in my signature.

[bodylojohn]

The whole point of a data adapter is specifically so you don't have to save row by row. You call Update and pass the DataTable and that's it. That's one of the examples in my Retrieving & Saving Data thread in the CodeBank, which you can find by following the CodeBank link in my signature.

I DID IT!!!!!

Thanks to you great help JMC...

I read your post again and this is what I came up with:

Private Sub CSV2Datatable()
    Try
      Dim dtTable As New DataTable

      With dtTable.Columns
        .Add("Kostenplaats", GetType(String))
        .Add("PersoneelsNummer", GetType(String))
      End With

      Dim filePath As String = "D:\Johnny\DTBS\DTBS_TEST_OUD\DTB201207.csv"

      Using reader As New TextFieldParser(filePath)
        reader.Delimiters = New String() {";"}
        Do Until reader.EndOfData
          Dim fields As String() = reader.ReadFields()
          Dim name As String = fields(0)
          Dim value As String = fields(1)
          Dim row As DataRow = dtTable.NewRow()
          row("Kostenplaats") = If(String.IsNullOrEmpty(name), CObj(DBNull.Value), name)
          row("PersoneelsNummer") = If(String.IsNullOrEmpty(value), CObj(DBNull.Value), value)
          dtTable.Rows.Add(row)
        Loop
        Dim adapter As New OleDbDataAdapter("select * from tblIMPORT", connection)
        Dim insert As New OleDbCommand("insert into tblIMPORT (Kostenplaats,PersoneelsNummer) values (@Kostenplaats,@PersoneelsNummer)", connection)
        insert.Parameters.Add("@Kostenplaats", OleDbType.VarChar, 10, "Kostenplaats")
        insert.Parameters.Add("@PersoneelsNummer", OleDbType.VarChar, 10, "PersoneelsNummer")
        adapter.InsertCommand = insert

        adapter.Update(dtTable)


      End Using
    Catch ex As Exception
      MessageBox.Show(ex.ToString())
    End Try
    MsgBox("klaar")
  End Sub

Within 10 seconds the import is ready instead of 10 minutes.

THANK you for pointing me in the right direction!!!!

[bodylojohn]

Here is the new code:

It takes under 20 seconds to run instead of 13 munutes.

Thanks!!!!

 Private Sub LoopDoorRegels()
    LeesSysteemInstelling(1) 'Haal de periode op uit de systeeminstellingen welke is opgeslagen na het selecteren van het in te lezen bestand.
    ControleerPeriode("tblFACTUUR", strSI_Waarde) 'Vergelijk de periode uit de bestandsnaam met de periode uit de tabel tblIMPORT
    ProgressBar1.Minimum = 0
    ProgressBar1.Maximum = 10
    ProgressBar1.Step = 1
    ProgressBar1.Value = 0

    Try
      Dim dtTable As New DataTable

      With dtTable.Columns
        .Add("Kostenplaats", GetType(String))
        .Add("PersoneelsNummer", GetType(String))
        .Add("Naam", GetType(String))
        .Add("Periode", GetType(String))
        .Add("Looncode", GetType(String))
        .Add("Waarde", GetType(String))
        .Add("BTW", GetType(String))
        .Add("DatumInlezen", GetType(Date))
      End With

      Dim filePath As String = strBestandslocatie

      Using reader As New TextFieldParser(filePath)
        reader.Delimiters = New String() {";"}
        Do Until reader.EndOfData
          Dim fields As String() = reader.ReadFields()
          Dim _kostenplaats As String = fields(0)
          Dim _personeelsnummer As String = fields(1)
          Dim _Naam As String = fields(2)
          Dim _Periode As String = fields(3)
          Dim _Looncode As String = fields(4)
          Dim _Waarde As String = fields(5)
          Dim _BTW As String = fields(6)

          Dim row As DataRow = dtTable.NewRow()
          row("Kostenplaats") = If(String.IsNullOrEmpty(_kostenplaats), CObj(DBNull.Value), _kostenplaats)
          row("PersoneelsNummer") = If(String.IsNullOrEmpty(_personeelsnummer), CObj(DBNull.Value), _personeelsnummer)
          row("Naam") = If(String.IsNullOrEmpty(_Naam), CObj(DBNull.Value), _Naam)
          row("Periode") = If(String.IsNullOrEmpty(_Periode), CObj(DBNull.Value), _Periode)
          row("Looncode") = If(String.IsNullOrEmpty(_Looncode), CObj(DBNull.Value), _Looncode)
          row("Waarde") = If(String.IsNullOrEmpty(_Waarde), CObj(DBNull.Value), _Waarde)
          row("BTW") = If(String.IsNullOrEmpty(_BTW), CObj(DBNull.Value), _BTW)
          row("DatumInlezen") = DateTime.Now.ToString("dd-MM-yyyy HH:mm")

          dtTable.Rows.Add(row)
        Loop
        Dim adapter As New OleDbDataAdapter("select * from tblIMPORT", connection)
        Dim insert As New OleDbCommand("insert into tblIMPORT (Kostenplaats,PersoneelsNummer,Naam,Periode,Looncode,Waarde,BTW,DatumInlezen) " & _
                                       "values (@Kostenplaats,@PersoneelsNummer,@Naam,@Periode,@Looncode,@Waarde,@BTW,@DatumInlezen)", connection)
        insert.Parameters.Add("@Kostenplaats", OleDbType.VarChar, 10, "Kostenplaats")
        insert.Parameters.Add("@PersoneelsNummer", OleDbType.VarChar, 10, "PersoneelsNummer")
        insert.Parameters.Add("@Naam", OleDbType.VarChar, 50, "Naam")
        insert.Parameters.Add("@Periode", OleDbType.VarChar, 6, "Periode")
        insert.Parameters.Add("@Looncode", OleDbType.VarChar, 5, "Looncode")
        insert.Parameters.Add("@Waarde", OleDbType.VarChar, 11, "Waarde")
        insert.Parameters.Add("@BTW", OleDbType.VarChar, 1, "BTW")
        insert.Parameters.Add("@DatumInlezen", OleDbType.Date, 12, "DatumInlezen")
        adapter.InsertCommand = insert
        adapter.Update(dtTable)
      End Using
    Catch ex As Exception
      MessageBox.Show(ex.ToString())
    End Try

    blnTblBestaat = False
    ProgressBar1.Value = 10
  End Sub

[bodylojohn]

Hello,

One more question before I leave this thread alone ;-)

I tried to fill a datatable and then save that datatable to a table with a data adapter.
Nothing is saved to the table.

This is the code i'm using:

Private Sub VulTabelFactuurNIEUW()
     Dim strConnectionstring As String = strConnectionstringAccess2003 & strDatabasePath
    Dim connection As New OleDbConnection(strConnectionstring)

    Dim strSQL As String = String.Format("SELECT Periode,AfdelingsCode,Waarde,AdminKosten,BTW,Percentage,Code,AdministratieKostenPerc,KlantNummer,Code_S,BTW_S,Percentage_S," & _
                                         "'FACTUREN INGELEZEN EN AFGEDRUKT' AS Status FROM Q_FACTUUR_GROUP WHERE Periode = '{0}' ORDER BY AfdelingsCode", strSI_Waarde)
    Dim adapter As New OleDbDataAdapter(strSQL, connection)
    Dim dtTable As New DataTable
    adapter.Fill(dtTable)

    Try
      Dim adapter2 As New OleDbDataAdapter("select * from tblFACTUUR", connection)
      Dim cmdInsert As New OleDbCommand("INSERT INTO tblFACTUUR " & _
        "(Afdelingscode,Periode) " & _
        "VALUES " & _
        "(@Afdelingscode,@Periode)", connection)


      cmdInsert.Parameters.Add("@Afdelingscode", OleDbType.VarChar, 10, "Afdelingscode")
      cmdInsert.Parameters.Add("@Periode", OleDbType.VarChar, 6, "Periode")

      adapter2.InsertCommand = cmdInsert
      adapter2.Update(dtTable)

    Catch ex As Exception
      MessageBox.Show(ex.ToString())
    End Try
  End Sub

dtTable is filled with data but nothing is saved.

What am i doing wrong?

[jmcilhinney]

There are only three possible outcomes:

1. Update succeeds and returns a non-zero value, meaning that many records were saved.
2. Update succeeds and returns zero, meaning there were no changes in the DataTable to save.
3. Update fails and an exception is thrown.

Which is it in your case?

[bodylojohn]

Dear JMC...

I think it's option 2.

1. nothing is saved to the table tblFACTUUR
3. no exception is thrown.

The datatable dtTable contains data.
The problem is that the data from dtTable doesn't get inserted into the table tblFACTUUR.

I just cant figure out where i am going wrong.

[jmcilhinney]

It's not what you think that matters. It's what actually happens that matters. Does Update return zero or does it not?

[bodylojohn]

Hello JMC,

Below is the result of dataadapter2

UpdateCommand = Nothing
UpdateCommand (DbDataAdapter) = Nothing

Is this the result you mean?

[jmcilhinney]

I'm not sure why this is so hard. For the third time, what value is returned when you call the Update function? Obviously you already know how to get the result of a function because you're already doing it in the code you posted.

[bodylojohn]

Thanks again JMC,

nothing is returned because there isnt a sngle record inserted into the table.
I don't mean to frustrate you. So I am sorry if I don't exactle understand.

[si_the_geek]

What he is saying is that Update is a function... functions return a value, but in your code you are ignoring the value it returns:

adapter2.Update(dtTable)

In order to work out why it is failing, you need to check the value, eg:

      MsgBox(adapter2.Update(dtTable))

[bodylojohn]

THANK YOU SI!!!!!!

Dear JMC... the value is 0 (zero)

I hope you are now able to help me on my way solving my problem..

[jmcilhinney]

OK, so that means that there are no changes in the DataTable to save. The Update method saves changes to the database, i.e. the contents of any DataRow with a RowState of Added, Modified or Deleted. All your rows have a RowState of Unchanged, so there's no changes to save. I probably should have spotted that from the start.

If you want to insert records from a DataTable into a database then the DataRows need to have a RowState of Added. When you call Fill, all the DataRows are added to the DataTable and then AcceptChanges is called, setting all the RowStates to Unchanged. That makes perfect sense as the default behaviour because otherwise you couldn't update rows, only insert them. In your case you don't want that though, so you need to set the AcceptChangesDuringFill property of the first data adapter to False. That way, AcceptChanges is not called on the DataTable and all DataRows retain a RowState of Added, ready to be inserted.

[bodylojohn]

Thanks again JMC..

Very clear explenation.
So inmediately I changed the code to:

 Private Sub VulTabelFactuurNIEUW()
    Dim strConnectionstring As String = strConnectionstringAccess2003 & strDatabasePath
    Dim connection As New OleDbConnection(strConnectionstring)
    Dim strSQL As String = String.Format("SELECT Periode,AfdelingsCode FROM Q_FACTUUR_GROUP WHERE Periode = '{0}' ORDER BY AfdelingsCode", strSI_Waarde)

    Dim adapter As New OleDbDataAdapter(strSQL, connection)
    Dim dtTable As New DataTable

    adapter.AcceptChangesDuringFill = False
    adapter.Fill(dtTable)

    Dim adapter2 As New OleDbDataAdapter("select * from tblFACTUUR", connection)
 
    Dim cmdInsert As New OleDbCommand("INSERT INTO tblFACTUUR " & _
      "(Afdelingscode,Periode) " & _
      "VALUES " & _
      "(@Afdelingscode,@Periode)", connection)

    cmdInsert.Parameters.Add("@Afdelingscode", OleDbType.VarChar, 10, "Afdelingscode")
    cmdInsert.Parameters.Add("@Periode", OleDbType.VarChar, 6, "Periode")
      adapter2.InsertCommand = cmdInsert
    adapter2.Update(dtTable)
    End Sub

when I run the code an exception is thrown on: adapter2.Update(dtTable)

"an index or primary key cannot contain a null-value"

So there is something I am doing wrong.

[jmcilhinney]

If the error message is telling you that an index or primary key cannot be null then obviously you are trying to insert null into an index or primary key.

[bodylojohn]

JMC......

I am sorry for asking the previous STUPID question.
My table consists out of 3 keys.
My problem is resolved.

Thank you very very much!!!

SI_THE_GEEK also thanks a bunch!!!!!!!!!!!

[bodylojohn]

Hello all,

This also works:

 Private Sub vultabelfactuurnieuw()
    Dim strConnectionstring As String = strConnectionstringAccess2003 & strDatabasePath
    Dim dt As New DataTable()
    Dim cmdSelect As OleDb.OleDbCommand

    Using cnn As New OleDb.OleDbConnection(strConnectionstring)
      cmdSelect = New OleDb.OleDbCommand("SELECT periode FROM Q_FACTUUR_BORDEREL WHERE periode = '" & strSI_Waarde & "' ORDER BY AfdelingsCode")
      cmdSelect.Connection = cnn
      Dim ad As New OleDb.OleDbDataAdapter(cmdSelect)
      ad.AcceptChangesDuringFill = False
      ad.Fill(dt)
    End Using

    'Insert Data into Access   
    Using cnn As New OleDb.OleDbConnection(strConnectionstring)
      Dim cmdSelect1 As OleDbCommand = New OleDbCommand("select periode from tblOVERZICHT_HISTORY_BORDEREL")
      cmdSelect1.Connection = cnn
      Dim ad As New OleDb.OleDbDataAdapter(cmdSelect1)
      Dim cmdBuilder As New OleDb.OleDbCommandBuilder(ad)
      Dim cmd As OleDb.OleDbCommand = cmdBuilder.GetInsertCommand()
      cmd.Connection = cnn
      ad.InsertCommand = cmd
      ad.Update(dt)
    End Using
  End Sub

[jmcilhinney]

These lines are pointless:

      Dim cmd As OleDb.OleDbCommand = cmdBuilder.GetInsertCommand()
      cmd.Connection = cnn
      ad.InsertCommand = cmd

More than that, that code can be simplified significantly.

Dim connectionString = strConnectionstringAccess2003 & strDatabasePath
Dim table As New DataTable
 
Using sourceAdapter As New OleDbDataAdapter("SELECT periode FROM Q_FACTUUR_BORDEREL WHERE periode = '" & strSI_Waarde & "' ORDER BY AfdelingsCode",
                                            connectionString)
    sourceAdapter.AcceptChangesDuringFill = False
    sourceAdapter.Fill(table)
End Using
 
Using destinationAdapter As New OleDbDataAdapter("select periode from tblOVERZICHT_HISTORY_BORDEREL",
                                                 connectionString)
    Dim builder As New OleDbCommandBuilder(destinationAdapter)
 
    destinationAdapter.Update(table)
End Using

Kinda easier to read, right?

[bodylojohn]

These lines are pointless:

      Dim cmd As OleDb.OleDbCommand = cmdBuilder.GetInsertCommand()
      cmd.Connection = cnn
      ad.InsertCommand = cmd

More than that, that code can be simplified significantly.

Dim connectionString = strConnectionstringAccess2003 & strDatabasePath
Dim table As New DataTable
 
Using sourceAdapter As New OleDbDataAdapter("SELECT periode FROM Q_FACTUUR_BORDEREL WHERE periode = '" & strSI_Waarde & "' ORDER BY AfdelingsCode",
                                            connectionString)
    sourceAdapter.AcceptChangesDuringFill = False
    sourceAdapter.Fill(table)
End Using
 
Using destinationAdapter As New OleDbDataAdapter("select periode from tblOVERZICHT_HISTORY_BORDEREL",
                                                 connectionString)
    Dim builder As New OleDbCommandBuilder(destinationAdapter)
 
    destinationAdapter.Update(table)
End Using

Kinda easier to read, right?

I could not agree with you more Sir!!!!!

Thanks again...I realy learned a lot in this thread.
And I value the lessons and the kwonledge I learned here very much.