Click here to Skip to main content
15,567,466 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

I appreciate you wanting to do this yourself. If this is a learning exercise that's great, although I wouldn't start with someone else's code, but rather read up on System.IO.File, System.String, System.Text.RegularExpressions, System.Data.OleDbDataAdapter, and System.Data.OldDbCommandBuilder; look at each of the examples and see how each component works.

There three main parts to your project:
- the CSV reader
- the Access writer
- a console application or (not recommended due to added complexity) a simple Windows Forms application to act as a container for the above.

There are great articles on each of these steps on this website.

If you just want to get this done, I would strongly suggest posting it on Freelancer.com where someone in a third-world country will gladly do it for $25. It will get done quickly and an overqualified programmer will feed his/her family for a week.
 
Share this answer
 
I'm not sure why you are using .NET as Access itself has the tools built in the you can save the import and run a saved import from code, but if .NET has to be the solution, you can use the Jet (Access) provider to bring the CSV into an ADO.NET dataset. There are then many methods to copy form the dataset into Access.

You seem to be reading the file in yourself (there's a lot of commented out code there!). If you deal with the CSV line-by-line and parse the text, you can write to Access on a record-by-record basis. Make sure that your data types/field lengths are appropriate. If there's any doubt about the quality of the CSV, the record-by-record approach will allow you to validate and/or log errors while still allowing the rest of the import.

Finally, you could try automating Access itself from .NET. I've never tried this. There's a PIA for Access, this should work well. My approach would be to set up the import manually in Access, save the import definition and use the Interop assembly to run the saved import. Tis approach may well be the easiest and most robust technique.
 
Share this answer
 
Comments
Tom-Inge Nilsen 20-Aug-14 10:57am    
Thanks for quick replies. I do this in vb.net because it is the one language I know. I have done quite some automation in excel using VBA. But the programming is done on a laptop, since the work computers have very restricted access to it's software. (Citrix solutions, government). Most people at work have a hard time operating auto filters in excel (not to mention even knowing that access IS..), hence I have to make something that is easy for them to use to achieve the wanted results. Therefore it is a windows forms application, that does the dirty work of importing and running the necessary queries.

I have used streamwriter and streamreader before with success, but isn't that very very inefficient when importing 100.000 + rows from csv files ? Or should I use it to read line by line into some sort of array, and then writing it all to the database?
PhilLenoir 20-Aug-14 11:04am    
None of these solutions require the user to run Access. I still say that the best solution is to automate Access using the PIA. The only prerequisite is that Access (and the PIA) must be installed on the target machine. The manual preparation in Access is not even a programming task and should be easy for you. Using the PIA should be no different from any other Office automation task. Use the RunSavedImportExport of the DoCmd object.
Tom-Inge Nilsen 20-Aug-14 11:00am    
And - all the commented lines was merely to show different things I've tried, getting this to work...
Tom-Inge Nilsen 20-Aug-14 11:59am    
I can look into the PIA, hopefully it doesn't have to be installed on the work computers, otherwise that is not an option.. I have no rights to install anything there. :/
Tom-Inge Nilsen 20-Aug-14 11:59am    
I meant, hope it is installed already.. =)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900