0

I have the following code that aims to convert multiple csv files into a single array in VBA. The code works perfectly except for the one part.

This works just fine:

Sub CreateArrayFromCSV()
Dim cn As ADODB.Connection, RS As ADODB.Recordset, strSQL As String
Dim rsArr As Variant, dataArr As Variant, yearArr As Variant
Dim i As Long, j As Long

strPath = "path"

Set cn = CreateObject("ADODB.Connection")
strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=No;FMT=TabDelimited"";"
cn.Open strcon

This is now the part that makes trouble:

strSQL = vbNullString
For i = 2012 To 2014
    If strSQL <> vbNullString Then strSQL = strSQL & " UNION ALL "
    strSQL = strSQL & "SELECT *, " & i & " as Jahr FROM Data_" & i & ".txt"
Next i
strSQL = strSQL & ";"
Debug.Print strSQL
Set RS = cn.Execute(strSQL)
rsArr = Application.WorksheetFunction.Transpose(RS.GetRows)
RS.Close

To be precise, the trouble is only in that line:

strSQL = strSQL & "SELECT *, " & i & " as Jahr FROM Data_" & i & ".txt"

I want to add a column to the Select *, because I don't have headers in the .csv file. The solution could be two-fold, either: - What are the column names? I've tried col1 and Column 1, no avail. - How can I add a column to the Select *?

This works just fine again: 

ReDim dataArr(1 To (UBound(rsArr)) / 2, 1 To 3)

For j = LBound(rsArr) To UBound(rsArr) Step 2
    yearArr = Split(rsArr(j + 1, 1), vbTab)
    dataArr(1 + Int(j / 2), 1) = Left(rsArr(j, 1), 5) * 1
    dataArr(1 + Int(j / 2), 2) = yearArr(14)
    dataArr(1 + Int(j / 2), 3) = i
Next j

Set cn = Nothing
End Sub
Spurious
  • 1,903
  • 5
  • 27
  • 53
  • 1
    What exactly is the "trouble"? Please post the error message and in which line it occurs. Btw.: The field names are `F1`, `F2`, ... `Fn`. – Axel Richter May 19 '15 at 10:09
  • `Runtime error 9, index...` is the error, so the generic one. – Spurious May 19 '15 at 11:25
  • But @AxelRichter please add a solution with `"SELECT F1, " & i & " as Jahr FROM Data_" & i & ".txt"` so I can upvote because that solved my problem. – Spurious May 19 '15 at 11:27
  • No, because this is not really a solution. Runtime Error 9 is "Subscript out of Range" and it occurs because of your misunderstanding what `rsArr` contains. And this misunderstanding results out of the fact, that `FMT=TabDelimited` in the connection string will not work deviant to the Registry. If the Registry says `Format=Delimited(,)` but your `*.txt` contains tabulator delimited content, then you will ever get all columns from the `*.txt` in one Field with the `\t` as field content. So what you do now is a workaround, but not a solution. – Axel Richter May 19 '15 at 11:52
  • Well my question was: What are the fields in a .csv SQL connection called and the answer to that is `F1`, `F2`, etc. Refer to my question above: `The solution could be two-fold, either: - What are the column names?` – Spurious May 20 '15 at 15:54

0 Answers0