使用VB.Net将我的CSV文件导入我的Access数据库 [英] Using VB.Net to import my CSV file to my Access DB

问题描述

我使用以下代码将CSV文件导入我的Access数据库。我只有几个问题。

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()

如何将字符集更改为UTF-8？如果我输入utf8而不是850，我得到一个错误。

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

此外，我的CSV文件的第一行包含列名称。我可以修改上述代码，将其加入帐户吗？

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

尊敬的，

推荐答案

您可能会遇到麻烦，试图导入和一次选择，一件事你可能不想离开转换数据类型到Access 。为此，您将需要2个连接和SQL字符串来选择彼此插入到另一个。

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.

连接字符串将需要如下所示： p>

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'"

请注意，并且扩展属性包含在tick中。如果第一行有头/字段名称，则 HDR = Yes 将在结果集中跳过它们。使用字段名作为第一行的好处之一是OleDB将使用它们作为列名称（不需要 F1 As foo，F2 As bar ;实际上，失败，因为它们已从F1，F2 ...重命名。）

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...).

从CSV中读取的SQL：

The SQL to read from the CSV:

 "SELECT * FROM filename.csv"

是几种方法来处理它。您可以使用阅读器一次读取一行，以将其插入Access数据库。这可能更简单：将所有数据从CSV到一个DataTable，并使用它INSERT到Access：

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

Connection，Command和DataAdapter都是资源，所以他们使用USING块来处理它们。 myDT 将有一个 Rows 的集合，每个集合 Items 表示CSV中的字段。只需循环通过将所需的项目添加到Access数据库的行。

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.

您很可能必须从String到Integer或DateTime等进行相同的数据类型转换。

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

对于有关UTF8的问题，您可以使用代码页标识符。如果你离开连接字符串，它将使用任何在注册表中也可以工作。对于UTF8，使用 CharacterSet = 65001 。

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.

这篇关于使用VB.Net将我的CSV文件导入我的Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！