将 .csv 文件拉入 Access 数据库

发布于 2024-11-01 06:46:51 字数 932 浏览 0 评论 0原文

我有一个案例,需要将 .csv 文件提取到 Access 数据库中。我需要将数据保留在当前结构中。可能有多个 .csv 文件,因此我需要“即时”制作表格。

我正在尝试下面的代码,但我收到的错误消息(在执行行上)是: “查询输入必须包含至少一个查询或表”

只是一两个简短的说明: 连接确实打开,并且由于它是一个 .csv 文件,因此它会输入该文件的路径而不是文件本身(如您所见)。 .csv 确实有行,我可以执行“Select * From tocopy.csv”并且按预期工作。

我正在尝试的代码:

        Dim ConnectionString As String
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test\;Extended Properties=""Text;HDR=Yes"""

        Dim myConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        myConnection.Open()

        Dim cmd As New System.Data.OleDb.OleDbCommand
        Dim sSQL As String = ""

        sSQL = "SELECT * INTO [MS Access;Database=C:\test\intake.mdb].[DynamicTable] FROM [tocopy.csv]"
        cmd.CommandText = sSQL
        cmd.Connection = myConnection

        cmd.ExecuteNonQuery()

        myConnection.Close()
        myConnection = Nothing

I have a case where I need to pull .csv files into an Access database. I need to keep the data in its current structure. There could be multiple .csv files, so I need to make the tables 'on the fly'.

I'm trying the code below, but the error message I'm receiving (on the execute line) is:
'Query input must contain at least one query or table'

Just a quick note or two:
The connection does open, and as it is a .csv file, it gets fed the path to that file not the file itself (as you'll see). The .csv does have rows, I can do a 'Select * From tocopy.csv' and that works as expected.

Code I'm trying:

        Dim ConnectionString As String
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test\;Extended Properties=""Text;HDR=Yes"""

        Dim myConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        myConnection.Open()

        Dim cmd As New System.Data.OleDb.OleDbCommand
        Dim sSQL As String = ""

        sSQL = "SELECT * INTO [MS Access;Database=C:\test\intake.mdb].[DynamicTable] FROM [tocopy.csv]"
        cmd.CommandText = sSQL
        cmd.Connection = myConnection

        cmd.ExecuteNonQuery()

        myConnection.Close()
        myConnection = Nothing

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

羞稚 2024-11-08 06:46:51

您真的需要将多个列结构完全不同的 CSV 文件导入到您的数据库中吗?那么您将如何处理导入的数据呢?

如果数据的结构非常不相关,因为您没有对其运行查询,请尝试将 CSV 文件中的行导入到只有一个 MEMO 类型字段的表中,然后使用一些 VBA 代码处理这些记录。

Do you really have the need to import several CSV files with completely different column structure into your database? In what way will you process the imported data then?

If the structure of the data is quite irrelevant, because you don't run queries on it, try to import the lines from the CSV files into one table with just one field of type MEMO, and then process these records with some VBA code.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文