Excel中使用VBA将txt导入Access

mctrix

观看“zhaogang1960”版主的帖子【用ExcelVBA将多个列数不完全相同的文本文件或csv数据导入到Access数据库中的三种方法】
然后自己在用方法3时，报错“INSERT INTO语法错误”，不知道问题在哪里，贴出来请大家看看问题出在哪里。
介绍：
1.附件中三个文件，1个txt文本，1个Access数据库（包含1个test表），1个Excel工作薄（含VBA代码的空白工作薄）；
2.想在Excel中使用VBA将txt文本的内容导入到Access数据库test表下面（不删除原有数据）；
3.txt文本内容使用TAB间隔，含多行数据；
4.Excel、Access使用的均为2013版本；

附上代码：

1. Sub txtImportAccess()
   
2.     Dim cnn As Object   'ADODB连接对象
   
3.     Dim myPath$, myFile$, SQL$
   
4.     Set cnn = CreateObject("ADODB.Connection")
   
5.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
   
6.     myPath = ThisWorkbook.Path & ""
   
7.     myFile = Dir(myPath & "*.txt")
   
8.     If myFile <> "" Then
   
9.         SQL = "select * from [Text;FMT=TabDelimited;HDR=NO;DATABASE=" & myPath & ";].[" & myFile & "]"
   
10.     End If
    
11.     SQL = "Insert Into test" & SQL '向access数据表中添加数据语句
    
12.     cnn.Execute SQL
    
13.     MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
    
14.     cnn.Close
    
15.     Set cnn = Nothing
    
16. End Sub

复制代码

报错信息：


使用VBA将txt导入Access.zip (1.41 MB, 下载次数: 146)

2016-1-25 17:09 上传

点击文件名下载附件

问题已经解决：你在这个帖子中可以获取到。再次感谢zhaogang1960老师！！！
1. 在Excel中使用VBA把文本文件数据导入到Access数据库；
2. 如果被导入的文本文件中第一列文本与数字混排，需要指定各自的数据类型；
关键字：VBA，TXT，ACCESS，SCHEMA.INI，ADODB.CONNECTION，SQL， [Text;FMT=TabDelimited;HDR=NO]
贴上关键代码如下：

1. Sub txtImportAccess()
   
2.     Dim cnn As Object
   
3.     Dim myPath$, MyFile$, SQL$, s$
   
4.     Set cnn = CreateObject("ADODB.Connection")
   
5.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
   
6.     myPath = ThisWorkbook.Path & ""
   
7.     MyFile = ThisWorkbook.Path & "\Test.txt"
   
8.     If Dir(ThisWorkbook.Path & "\Test.txt") <> "" Then
   
9.         SQL = "Insert Into test select f1 as 号码,f2 as 批号,f3 as 日期,f4 as 时间,f5 as 备注 from [Text;FMT=TabDelimited;HDR=NO;DATABASE=" & myPath & ";].[Test.txt]"
   
10.         s = "[Test.txt]" & vbCrLf & "COLNAMEHEADER = false" & vbCrLf & "Format = TabDelimited" & vbCrLf & "Col1=f1 Char" & vbCrLf & "Col2=f2 Char" & vbCrLf & "Col3=f3 DATE" & vbCrLf & "Col4=f4 Char" & vbCrLf & "Col5=f5 Char"
    
11.         Open myPath & "schema.ini" For Output As #1
    
12.             Print #1, s
    
13.         Close #1
    
14.         cnn.Execute SQL
    
15.         Kill ThisWorkbook.Path & "\schema.ini"
    
16.     End If
    
17.     MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
    
18.     cnn.Close
    
19.     Set cnn = Nothing
    
20. End Sub

复制代码

问题已经解决。再次感谢zhaogang1960老师，老师对Excel中VBA与数据库的操作研究很深。在这个帖子中你可以学到：
1. Excel中使用VBA将txt文本导入到Access中：可以是单个文本、也可以是多文本；
2. ADODB连接对象在执行SQL语句时，会读取Schema.ini中设置；
3. 被导入Access的txt文本若第一行数字与文本混排，则需要在Schema.ini中各自指定其数据类型；

关键字：VBA，TXT，文本，ACCESS，ADODB，SQL，SCHEMA.INI，[Text;FMT=TabDelimited;HDR=NO;]

贴上关键代码：

1. Dim cnn As Object
   
2.     Dim myPath$, MyFile$, SQL$, s$
   
3.     Set cnn = CreateObject("ADODB.Connection")
   
4.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
   
5.     myPath = ThisWorkbook.Path & ""
   
6.     MyFile = ThisWorkbook.Path & "\Test.txt"
   
7.     If Dir(ThisWorkbook.Path & "\Test.txt") <> "" Then
   
8.         SQL = "Insert Into test select f1 as 号码,f2 as 批号,f3 as 日期,f4 as 时间,f5 as 备注 from [Text;FMT=TabDelimited;HDR=NO;DATABASE=" & myPath & ";].[Test.txt]"
   
9.         s = "[Test.txt]" & vbCrLf & "COLNAMEHEADER = false" & vbCrLf & "Format = TabDelimited" & vbCrLf & "Col1=f1 Char" & vbCrLf & "Col2=f2 Char" & vbCrLf & "Col3=f3 DATE" & vbCrLf & "Col4=f4 Char" & vbCrLf & "Col5=f5 Char"
   
10.         Open myPath & "schema.ini" For Output As #1
    
11.         Print #1, s
    
12.         Close #1
    
13.         cnn.Execute SQL
    
14.         Kill ThisWorkbook.Path & "\schema.ini"
    
15.     End If
    
16.     MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
    
17.     cnn.Close
    
18.     Set cnn = Nothing

复制代码

zhaogang1960

此程序仅适用于逗号分割的“标准”文本文件，用其他分隔符时，ADO每查询一个文件，都要在该目录下生成一个Schema.ini文件，即不能将多个文本文件一次性导入到Access数据库中

mctrix

zhaogang1960 发表于 2016-1-25 17:28
此程序仅适用于逗号分割的“标准”文本文件，用其他分隔符时，ADO每查询一个文件，都要在该目录下生成一个S ...

非常感谢。对txt导入ACCESS的操作没有概念，如果这个方法不行，那该如何实现将txt导入Access？另外，我现在操作的就是一个txt文本，报错提示也是“Insert Into语法错误”？

zhaogang1960

mctrix 发表于 2016-1-25 17:34
非常感谢。对txt导入ACCESS的操作没有概念，如果这个方法不行，那该如何实现将txt导入Access？另外，我现 ...

如果仅导入一个文本文件：
Sub txtImportAccess()
    Dim cnn As Object
    Dim myPath$, MyFile$, SQL$, s$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
    myPath = ThisWorkbook.Path & "\"
    MyFile = ThisWorkbook.Path & "\Test.txt"
    If Dir(ThisWorkbook.Path & "\Test.txt") <> "" Then
        s = "[Test.txt]" & vbCrLf & "COLNAMEHEADER = false" & vbCrLf & "Format = TabDelimited"
        SQL = "Insert Into test select f1 as 号码,f2 as 批号,f3 as 日期,f4 as 时间,f5 as 备注 from [Text;FMT=TabDelimited;HDR=NO;DATABASE=" & myPath & ";].[Test.txt]"
        Open myPath & "schema.ini" For Output As #1
        Print #1, s
        Close #1
        cnn.Execute SQL
        Kill ThisWorkbook.Path & "\schema.ini"
    End If
    MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
    cnn.Close
    Set cnn = Nothing
End Sub

zhaogang1960

如果想导入多个文本文件：
Sub txtImportAccess_2() '导入多个文本文件
    Dim cnn As Object
    Dim myPath$, MyFile$, SQL$, s$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb" '连接数据库
    myPath = ThisWorkbook.Path & "\"
    MyFile = Dir(myPath & "*.txt")
    While MyFile <> ""
        s = "[" & MyFile & "]" & vbCrLf & "COLNAMEHEADER = false" & vbCrLf & "Format = TabDelimited"
        SQL = "Insert Into test select f1 as 号码,f2 as 批号,f3 as 日期,f4 as 时间,f5 as 备注 from [Text;FMT=TabDelimited;HDR=NO;DATABASE=" & myPath & ";].[" & MyFile & "]"
        Open myPath & "schema.ini" For Output As #1
        Print #1, s
        Close #1
        cnn.Execute SQL
        MyFile = Dir()
    Wend
    MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
    cnn.Close
    Set cnn = Nothing
    Kill ThisWorkbook.Path & "\schema.ini"
End Sub

zhaogang1960

下面附件中有导入单个、多个文本文本两个程序，请测试：
使用VBA将txt导入Access.rar (1.2 MB, 下载次数: 284)

2016-1-25 17:49 上传

点击文件名下载附件

mctrix

zhaogang1960 发表于 2016-1-25 17:28
此程序仅适用于逗号分割的“标准”文本文件，用其他分隔符时，ADO每查询一个文件，都要在该目录下生成一个S ...

谢谢。那么s 在这里的作用是什么呢？

zhaogang1960

mctrix 发表于 2016-1-25 21:38
谢谢。那么s 在这里的作用是什么呢？

Schema.ini文件内容，本程序中：
[Test.txt]
COLNAMEHEADER = false
Format = TabDelimited

schema.rar (136 Bytes, 下载次数: 53)

2016-1-25 21:44 上传

点击文件名下载附件

mctrix

zhaogang1960 发表于 2016-1-25 21:44
Schema.ini文件内容，本程序中：
[Test.txt]
COLNAMEHEADER = false

谢谢谢谢。这么晚还回我。我用手机登录的，明天电脑打开看下。有疑问再向你请教！

mctrix

zhaogang1960 发表于 2016-1-25 21:44
Schema.ini文件内容，本程序中：
[Test.txt]
COLNAMEHEADER = false

我做了两次实验：
1. 把删除schema.ini那部分代码删除掉 --> 看到了向ini中写入的内容；
2. 把操作schema.ini的所有代码删除掉（打开、写入、关闭、删除） --> 报错提示至少一个参数没有被指定值；

想知道ADODB对象在执行SQL语句操作txt文本的时候，操作流程是什么样子的？Schema.ini跟这些操作有什么关联？
谢谢！