Import large CSV file into MS Access using Visual Basic (.net)

See more:

VB

Hi! I have torn off nearly all hair on my head trying to import .CSV data into an MS Access database (.accdb)

It is for a routine operation at work, the CSV files contain much data (100.000+ rows).


How to I do this? Using OleDBConnection or something else?
How would the SQL syntax be, to insert data from the csv into the accdb ?
(INSERT INTO Tablename(fields,fields,etc) FROM .csv ?)

Tried googling this topic a LOT, but still cannot get it working.
Selecting files (csv and accdb) with the openfiledialog works fine, though.

***

Here is some of the jibberish I've been through:

Imports System.Data.OleDb

Public Class Form1


Dim ImportFile As String = ""
Dim DBFile As String = ""
Dim CSVFileName As String = ""
Dim CSVFilePath As String = ""


Private Sub btnVelgCSV_Click(sender As System.Object, e As System.EventArgs) Handles btnVelgCSV.Click

Dim strFileNameAndPath As String = OpenFile()
If (strFileNameAndPath = "") Then
MsgBox("You did not select a file!")
Else
'MsgBox("You selected this file: " & strFileNameAndPath & vbCrLf & _
'"The filename is: " & System.IO.Path.GetFileName(strFileNameAndPath) & vbCrLf & _
'"Located in: " & System.IO.Path.GetDirectoryName(strFileNameAndPath) & vbCrLf & _
'"It has the following extension: " & System.IO.Path.GetExtension(strFileNameAndPath) & vbCrLf & _
'"The file was created on " & System.IO.File.GetCreationTime(strFileNameAndPath) & vbCrLf & _
'"The file was last written to on " & System.IO.File.GetLastWriteTime(strFileNameAndPath) _
')


End If
End Sub
Public Function OpenFile() As String
Dim strFileName = ""
Dim fileDialogBox As New OpenFileDialog()
fileDialogBox.Filter = "Squirrel CSV Export (*.csv) |*.csv"
fileDialogBox.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
If (fileDialogBox.ShowDialog() = DialogResult.OK) Then
strFileName = fileDialogBox.FileName
ImportFile = fileDialogBox.FileName
lblCSV.Text = "Valgt CSV-fil: " & fileDialogBox.FileName
End If
Return strFileName
End Function

Public Function OpenFile2() As String
Dim strFileName = ""
Dim fileDialogBox As New OpenFileDialog()
fileDialogBox.Filter = "Microsoft Access Database (*.accdb) |*.accdb"
fileDialogBox.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
If (fileDialogBox.ShowDialog() = DialogResult.OK) Then
strFileName = fileDialogBox.FileName
DBFile = fileDialogBox.FileName
lblDB.Text = "Valgt databasefil: " & fileDialogBox.FileName
End If
Return strFileName
End Function

Private Sub load_csv()
'Dim myConnToCSV As OleDbConnection
Dim myConnToAccess As OleDbConnection
Dim mySQLCommand As OleDbCommand
Dim mySQLStrg As String
Dim dt As New DataTable
'myConnToCSV = New OleDbConnection("Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & ImportFile & ";Extended Properties=text;HDR=Yes;FMT=Delimited")
myConnToAccess = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & DBFile)
Using smooth As New OleDbDataAdapter("Select * from [" & DBFile & "]", myConnToAccess)
smooth.Fill(dt)
End Using

'myConnToCSV.Open()
'myConnToAccess.Open()
'mySQLStrg = "SELECT * INTO [MS Access;Database=" & DBFile & "].[Import_fra_CSV] FROM [" & ImportFile & "]"
'mySQLStrg = "INSERT INTO Import_fra_CSV(KONTOSTRENG,OPPDRAG_GJELDER_ID,KODE_VENTESTATUS,BILAGS_REF,DATO_OVERFORES,DATO_BEREGNET,STATUS_DATO,DATO_PERIODE_FOM,DATO_PERIODE_TOM,SATS,BELOP,BELOP_FORMATERT,KODE_FAGGRUPPE,KODE_FAGOMRAADE) * FROM [Text;DATABASE=" & CSVFilePath & ";HDR=YES].[" & CSVFileName & "]" ', myConnToAccess
'& ImportFile
'mySQLCommand = New OleDbCommand(mySQLStrg, myConnToAccess)
'Try
'mySQLCommand.ExecuteNonQuery()

' myConnToAccess.Close()

'Catch ex As Exception
' MsgBox(ex.Message)
' End Try
'mySQLCommand = New OleDbCommand(mySQLStrg, myConnToAccess)
'mySQLCommand.ExecuteNonQuery()
'MsgBox("All done!")


MsgBox("All done?")

End Sub

Private Sub btnDB_Click(sender As System.Object, e As System.EventArgs) Handles btnDB.Click
Dim strFileNameAndPath As String = OpenFile2()
If (strFileNameAndPath = "") Then
MsgBox("You did not select a file!")
Else
'MsgBox("You selected this file: " & strFileNameAndPath & vbCrLf & _
'"The filename is: " & System.IO.Path.GetFileName(strFileNameAndPath) & vbCrLf & _
'"Located in: " & System.IO.Path.GetDirectoryName(strFileNameAndPath) & vbCrLf & _
'"It has the following extension: " & System.IO.Path.GetExtension(strFileNameAndPath) & vbCrLf & _
'"The file was created on " & System.IO.File.GetCreationTime(strFileNameAndPath) & vbCrLf & _
'"The file was last written to on " & System.IO.File.GetLastWriteTime(strFileNameAndPath) _
')
CSVFileName = System.IO.Path.GetFileName(strFileNameAndPath)
CSVFilePath = System.IO.Path.GetDirectoryName(strFileNameAndPath)
End If
End Sub

Private Sub btnImport_Click(sender As System.Object, e As System.EventArgs) Handles btnImport.Click
Call load_csv()
End Sub
End Class