|
文本文件第一列数字和文本混排,需要指定各列数据类型:
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
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]"
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"
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 |
|