VBA访问:导入带有附加标头数据的CSV [英] VBA Access: Import CSV with additonal header data

问题描述

我是VBA编码的新手.想知道大家是否都能帮助我?我有一个CSV文件，其结构如下: -前22行覆盖特定的标头数据(这全部加载到excel中的一列中) -表的列标题位于第23行 -数据实际上位于第24行以后.

I am new to coding VBA. Was wondering if you all could help me? I have a CSV file which is structured as the following: - First 22 rows cover the specfic header data(this all loads in one column in excel) - column headers for table are in Row 23 - the data is actually located from row 24 onward.

代码需要做的是将此数据插入具有正确列标题的新表中.另外，在插入时，还需要在表的前几列中输入文件名和标题数据.

What the code needs to do is insert this data in new table with the right column titles. Also while inserting it needs to input the file name and header data in the first few columns of the table.

到目前为止，我已经将整个CSV导入了一个我相信的数组中:

So far I have imported the entire CSV into an array I believe:

看看我到目前为止有什么:

See what I have so far:

Sub readCSV()
Dim fs As Object
Dim fso As New FileSystemObject
Dim tsIn As Object
Dim sFileIn, filename As String
Dim aryFile, aryHeader, aryBody As Variant

sFileIn = "C:\doc\test.csv"

Set filename = fso.GetFileName(sFileIn)

Set fs = CreateObject("Scripting.FileSystemObject")
Set tsIn = fs.OpenTextFile(sFileIn, 1)
sTmp = tsIn.ReadAll
aryFile = Split(sTmp, vbCrLf)

For i = 1 To 22
    aryHeader(1, i) = aryFile(i)
Next i

For i = 23 To UBound(aryFile)
    aryBody(i) = Split(aryFile(i), ",")
    DoCmd.RunSQL "INSERT INTO MAINS VALUES (filename,aryHeader(1),aryBody(i))"
Next i

End Sub

这是正确的吗?谁能看到我正在采取正确的方法

is this correct? Can anyone see of i am taking the right approach

更新-对此进行了重新编码

UPDATE - recoded this a bit

推荐答案

这就是我最终得到的结果:

this is what i ended up:

Sub ReadCSV2()
Dim fs As Object
Dim filename As String
Dim tsIn As Object
Dim sFileIn As String
Dim aryHeader, aryBody As Variant
Dim Text As String
Dim sqlcre As String
Dim sqlsta As String

sFileIn =   "C:\test\test.csv"
filename = GetFilenameFromPath(sFileIn) 'function to get the file name
Set fs = CreateObject("Scripting.FileSystemObject")
Set tsIn = fs.OpenTextFile(sFileIn, 1)

For i = 1 To 23
    Tmps = tsIn.ReadLine
Next i

aryHeader = Split(Tmps, ",")

On Error Resume Next
DoCmd.RunSQL "DROP TABLE tempdata"
On Error GoTo 0

sqlcre = "CREATE TABLE tempdata ([Filename] Text,"
For k = LBound(aryHeader) To UBound(aryHeader)
    sqlcre = sqlcre & "[" & aryHeader(k) & " " & k + 1 & "] Text,"
Next k
k = k - 1
sqlcre = Left(sqlcre, Len(sqlcre) - 13) & ")"
'Debug.Print k
'Debug.Print sqlcre
DoCmd.RunSQL sqlcre
DoCmd.SetWarnings False         
While Not tsIn.AtEndOfStream
     Tmps = tsIn.ReadLine
     aryBody = Split(Tmps, ",")
     sqlsta = "INSERT INTO tempdata VALUES ('" & filename & "','"
     For M = LBound(aryBody) To UBound(aryBody)
         sqlsta = sqlsta & Replace(aryBody(M), "'", "`") & "', '"
     Next M
     M = M - 1
     Debug.Print M
     If M < k Then
         Text = ""
         For i = 1 To (k - M)
             Text = Text & "', '"
         Next i
         sqlsta = sqlsta & Text
     End If
    sqlsta = Left(sqlsta, Len(sqlsta) - 7) & ")"
     'Debug.Print sqlsta
     'Debug.Print k
     DoCmd.RunSQL sqlsta

Wend
DoCmd.SetWarnings True
End Sub

这篇关于VBA访问:导入带有附加标头数据的CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！