CODEWITHSUNDEEP
sqlvb.netcsv
Andrew Chitty
Andrew Chitty

Reputation: 37

Using VB.Net to import my CSV file to my Access DB

I am using the below code to import a CSV file to my Access DB. I just have a couple of questions.

    Con.Open()
    Dim strSqlCommand = "SELECT F1 AS id, F2 AS firstname " &
                        "INTO MyNewTable " &
                        "FROM [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=" & GlobalVariables.strDefaultDownloadPath & "].Airports.csv;"
    Dim sqlCommand = New System.Data.OleDb.OleDbCommand(strSqlCommand, Con)
    sqlCommand.ExecuteNonQuery()
    Con.Close()

How can I change the Character Set to UTF-8? If I enter utf8 instead of 850 I get an error.

Also, the first line of my CSV file contains the column names. Can I amend the above code to take that in to account?

Regards,

Andrew

Upvotes: 1

Views: 3784

Answers (1)

Ňɏssa Pøngjǣrdenlarp
Ňɏssa Pøngjǣrdenlarp

Reputation: 38915

You could run into trouble trying to import and select all at once, for one thing you may not want to leave converting data types up to Access. For that, you will need 2 connections and SQL string to select from one another to insert into the other.

The connection string will need to look something like this: 

 "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp\Tmp;Extended Properties='TEXT;HDR=Yes;FMT=Delimited;CharacterSet=ANSI'"

Note that just the path is listed and the Extended Properties are enclosed in ticks. If the first line has headers/field names then HDR=Yes will skip them in the result set. One of the benefits of having field names as the first line is that OleDB will use them as column names (no need for F1 As foo, F2 As bar; in fact that will fail because they have been renamed from F1, F2...).

The SQL to read from the CSV:

 "SELECT * FROM filename.csv"

There are several ways to process it. You could use a reader to read a row at a time to INSERT them into the Access database. This is probably simpler: get all the data from the CSV into a DataTable and use it to INSERT into Access:

Private myDT As DataTable    ' form level variable

...
Dim csvStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Temp\Tmp;Extended Properties='TEXT;HDR=Yes;FMT=Delimited;CharacterSet=ANSI'"
Dim csvSQL = "SELECT * FROM Capitals.csv"    ' use YOUR file name

Using csvCn = New OleDbConnection(csvStr),
         cmd As New OleDbCommand(csvSQL, csvCn)

    Using da As New OleDbDataAdapter(cmd)
        myDT = New DataTable
        da.Fill(myDT)
    End Using
End Using

For Each r As DataRow In myDT.Rows
    'ToDo: INSERT INTO Access
Next

The Connection, Command and DataAdapter are all resources, so they are in USING blocks to dispose of them when we are done with them. myDT will have a collection of Rows, each with a collection of Items representing the fields from the CSV. Just loop thru the rows adding the desired items to the Access DB.

You will very likely have to do same data type conversion from String to Integer or DateTime etc.

As for the question about UTF8 - you can use the Codepage identifier. If you leave it off the connection string it will use whatever is in the Registry which may also work. For UTF8 use CharacterSet=65001.

Upvotes: 1

Related Questions