I'm trying to import the contents of a csv file to an access database. I have a database named myDatabase with a table named myTable. It has columns A-E say. I have a csv file with headings in the first row which are called say S-Z. I want to import the csv data so that the data listed under V goes into A, X into B and Y into C. The other columns I'm not interested in taking anything from and the columns D and E in myTable I will fill up later.
This is the code I have tried so far:
Dim con As New OleDb.OleDbConnection Dim dbProvider As String Dim dbSource As String Dim ds As New DataSet Dim da As OleDb.OleDbDataAdapter Dim sql As String Dim fileToOpen As String fileToOpen = ImportFiles.SelectedItem If fileToOpen <> "" Then dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;" dbSource = "Data Source = C:\Users\Me\mydatabase.accdb" con.ConnectionString = dbProvider & dbSource con.Open() ' Connection Open sql = "TRUNCATE myTable" ' Clear the table da = New OleDb.OleDbDataAdapter(sql, con) sql = "SELECT V, X, Y INTO [myTable] FROM [Text;DATABASE=C:\Users\Me].[" & fileToOpen & "]" da = New OleDb.OleDbDataAdapter(sql, con) con.Close() ' Connection Closed Else MsgBox("Please choose a file to open from the list below!") End If
It is of course not working.
My first question would be, is it possible to import data like that with the different heading names between the csv and database?
My second question is, if so, what am I doing wrong with my code?