csv import to access

[Tinnin]

Hi All,

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?

[Begginnerdev]

You could split the line using the String.Split function and reference the index of that value.

Example:

        Dim sr as New StreamReader("MyPath")
        Dim da As New OleDbDataAdapter("SELECT * FROM myTable", "MyConnectionString")
        Dim dt As New DataTable

        da.Fill(dt)
        'This will loop until every line is read.
        Do While sr.Peek <> -1
            lstString.Add(sr.ReadLine.Split(",").ToList)
        Loop

        'To reference the values for insert, do something like this:

        'Set index to 1 to skip first row (headers)
        For i = 1 To lstString.Count - 1
            Dim dr As DataRow = dt.Rows.Add

            dr("Column1") = lstString(i)(4) 'Where 4 is the index of the column ***REMEMBER THAT ARRAYS ARE ZERO BASE
            dr("Column2") = lstString(i)(7)
        Next

        da.UpdateCommand = New OleDbCommandBuilder(da).GetUpdateCommand
        da.Update(dt)

[Tinnin]

Thanks Begginnerdev. I'll see how that works for me.

[Begginnerdev]

And it appears that my first copy/paste attempt of the first code snippet is missing the lstString type.

lstString is declared at a List(Of List(Of String))

Dim lstString As List(Of List(Of String))

My appologies.

[annaharris]

I faced similar issue transferring CSV file into database, but after splitting the string using String.split() function the files are properly inserted into the table.

[deceptikon]

I faced similar issue transferring CSV file into database, but after splitting the string using String.split() function the files are properly inserted into the table.

Until you're given a complex CSV file where one or more fields contain embedded commas or line breaks. ;) The ReadLine() and Split() approach is viable only for simple "CSV" files where the only commas are separating fields and the only line breaks are the record terminator.