将 .csv 文件拉入 Access 数据库
我有一个案例,需要将 .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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您真的需要将多个列结构完全不同的 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.