VB6批量读取CSV文件到access?

yuk_yu · 发表于 2013-1-18 16:53

VB6批量读取CSV文件到access,如何处理文本字符问题？例如：0100为何加入到access就变为100，如何处理？

yuk_yu · 发表于 2013-1-19 12:10

期待大家帮忙，解答！

zhaogang1960 · 发表于 2013-1-19 18:19

短信收到，请测试：

Sub 联合查询后导入() 'VBA中测试通过
'引用Microsoft ActiveX Data Objects 2.x Library
'VB6批量读取CSV文件到access?
'http://club.excelhome.net/thread-973334-1-1.html
Dim xlapp As Object
Dim cnn As New ADODB.Connection
Dim myPath$, MyFile$, SQL$
Set xlapp = GetObject(, "Excel.Application") 'VB6中
myPath = xlapp.ActiveWorkbook.Path & ""
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "Database.accdb" '连接数据库
MyFile = Dir(myPath & "*.csv")
Do While MyFile <> "" '创建多个文本文件的联合查询
If Len(SQL) Then
SQL = SQL & " union all select f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"
Else
SQL = "select f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"
End If
MyFile = Dir()
Loop
SQL = "insert into BackflushRecord select * from (" & SQL & ")" '向access数据表中添加数据语句
cnn.Execute SQL
MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
cnn.Close
Set cnn = Nothing
End Sub

复制代码

zhaogang1960 · 发表于 2013-1-19 18:22

请看工作簿附件：

VB6批量读取CSV文件到access(第一步VBA代码).rar (8.49 KB, 下载次数: 84)

zhaogang1960 · 发表于 2013-1-19 18:44

VB6窗体代码

Private Sub Command1_Click()
Dim gxlApp As Object
Dim cnn As Object
Dim myPath$, MyFile$, SQL$
Set gxlApp = GetObject(, "Excel.Application") 'VB6中
myPath = gxlApp.ActiveWorkbook.Path & ""
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "Database.accdb" '连接数据库
MyFile = Dir(myPath & "*.csv")
Do While MyFile <> "" '创建多个文本文件的联合查询
If Len(SQL) Then
SQL = SQL & " union all select f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"
Else
SQL = "select f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"
End If
MyFile = Dir()
Loop
SQL = "insert into BackflushRecord select * from (" & SQL & ")" '向access数据表中添加数据语句
cnn.Execute SQL
MsgBox "已经成功将csv文件数据保存为数据库！", vbInformation
cnn.Close
Set cnn = Nothing
End Sub

复制代码

zhaogang1960 · 发表于 2013-1-19 19:35

代码封装为FormShow.dll文件，工作簿中测试代码:
DLL文件类模块：
Sub FormShow()
Load Form1
Form1.Show 1
End Sub

Sub DLL文件测试()
Dim 试试看 As New Class1
试试看.FormShow
End Sub

yuk_yu · 发表于 2013-1-19 19:48

zhaogang1960 发表于 2013-1-19 19:35
代码封装为FormShow.dll文件，工作簿中测试代码:
DLL文件类模块：
Sub FormShow()

太感谢了，我测试看看！！

zhaogang1960 · 发表于 2013-1-21 12:54

请看附件

Get.rar (20.98 KB, 下载次数: 95)

yuk_yu · 发表于 2013-1-21 09:16

zhaogang1960 发表于 2013-1-19 18:44
VB6窗体代码

还有点问题：1. .Fields("FormID").Value = Mid(MyFile, 10, 7) ，和.Fields("FormType").Value = Mid(MyFile, 1, 8)如何加入？ 2. 加入的文本变成了数值，例如： CSV中的 modelname为0111113325001加入到access中.Fields("ModelName")就变为111113325001，前面的0没有了。请版主再帮忙看看，谢谢

zhaogang1960 · 发表于 2013-1-21 10:36

本帖最后由 zhaogang1960 于 2013-1-21 10:48 编辑

yuk_yu 发表于 2013-1-21 09:16
还有点问题：1. .Fields("FormID").Value = Mid(MyFile, 10, 7) ，和.Fields("FormType").Value = Mid( ...

Sub 联合查询后导入() 'VBA中测试通过
'引用Microsoft ActiveX Data Objects 2.x Library
'VB6批量读取CSV文件到access?
'http://club.excelhome.net/thread-973334-1-1.html
Dim xlapp As Object
Dim cnn As New ADODB.Connection
Dim myPath$, MyFile$, SQL$
Set xlapp = GetObject(, "Excel.Application") 'VB6中
myPath = xlapp.ActiveWorkbook.Path & ""
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "Database.accdb" '连接数据库
MyFile = Dir(myPath & "*.csv")
Do While MyFile <> "" '创建多个文本文件的联合查询
If Len(SQL) Then
SQL = SQL & " union all select '" & Mid(MyFile, 10, 7) & "' as FormID,'" & Mid(MyFile, 1, 8) & "' as FormType,f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"
Else
SQL = "select '" & Mid(MyFile, 10, 7) & "' as FormID,'" & Mid(MyFile, 1, 8) & "' as FormType,f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"
End If
MyFile = Dir()
Loop
SQL = "insert into BackflushRecord select * from (" & SQL & ")" '向access数据表中添加数据语句
cnn.Execute SQL
MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation
cnn.Close
Set cnn = Nothing
End Sub

复制代码

		自动登录	找回密码
密码			免费注册

[求助] VB6批量读取CSV文件到access?

评分