Import CSV file into Access 2007...

[lidds]

I have a CSV file which is delimited by a ~ and I am strugling with VB.Net code to be able to import the CSV file into Access 2007.

My CSV file is located in c:\temp\drv.csv
Database located in c:\temp\drv.accdb

I have been searching on google for sometime now and can't find the solution, any help would be really apreshiated.

Code so far, but this does not work:

Dim myConn As OleDb.OleDbConnection
myConn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\DRV.accdb;Persist Security Info=False;")
Dim cmd As New OleDb.OleDbCommand("INSERT INTO drv_data (link_ID,drv_type,drv_text) SELECT * FROM [Text;Database=C:\Temp;Hdr=No].[C:\temp\drv.csv]", myConn)
myConn.Open()
cmd.ExecuteNonQuery()
myconn.Close()

Thanks in advance

Simon

 

[jmcilhinney]

If it's delimited by tildes then it's not a CSV, is it? CSV stands for "comma-separated values" so if the values aren't separated by commas then it's not a CSV. You would presumably need to have a schema.ini file if the delimiters are not commas.

Also, what does this question have to do with WinForms? The answer is nothing, so this thread doesn't belong in the WinForms Data Access forum. Please post in the most appropriate forum for the topic.

 

[rcombs4]

You have a few different options. I've always either simply used the Split function or a TextFieldParser

the first option is easiest logically

Read all lines of a file line by line

for each line split the line by whatever delimiter you have.

insert into the DB passing in each index from the split as parameters