Import CSV file and transpose to tabular table

I have a csv file. The format is a kind of Cross Table.

I intend to import that file in tabular form to my Access table.

Screenshot for illustration:

The data to be imported always starts from row 4 (the number of rows is variable) and from columns 'A' to 'G'.

From column 'H' onwards up to the number of columns is variable. It may only be column 'H' that has data. I have up to '200' columns, that is, column 'H' to 'GY'.

I want to transpose column 'H' to column 'C1' of my table, only the rows that have the value in that column 1 with their respective data from that row.

Column 'C2' of my table values 1 from column 'H'.

In column 'C3' the value corresponding to that row, column and so on consecutively with the rest of the data. For this, I have been working on this code, but I get an error:

Runtime Error Subscript 9 out of range

from the commented line in the following code:

Sub Import()
 Dim rst As New ADODB.Recordset
 Dim tbl() As Variant
 Dim fld As Integer
 Dim flds() As Variant

    rst.CursorLocation = adUseClient
    rst.Open "SELECT Top 10 * FROM [Text;Database=C:\dataimport\].quote.csv;", CurrentProject.Connection, adOpenStatic, adLockReadOnly

    ReDim flds(rst.Fields.Count - 1 - 7) '<-- Here, the error
    For fld = 0 To UBound(flds)
        flds(fld) = fld + 7
    Next
    rst.Move 4 'Nos situamos en la fila 4
    tbl = rst.GetRows(2, , flds)
    rst.Close
    For fld = 0 To UBound(tbl, 1)
        CurrentDb.Execute _
        "Insert Into MiTabla (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) " & _
        "Select '" & Left(tbl(fld, 0), 8) & "', '" & Right(tbl(fld, 1), 2) & "', F1, F2, F3, F9, F11, F7, F8, F10 From [Text;Database=C:\dataimport\].quote.csv Where F" & fld + 1 + 7& "=1"
    Next
    
End Sub

I tried to reproduce the error. The problem seems to be that ADODB wants to assign a type to the fields (e.g., String, or Integer). The first row is assumed to contain field names and the remaining ones data. But the data rows contain mixed types. For instance on row 4 the second field is "NBR", i.e., a String, but on row 5 there is 104 which is an Integer.

If ADODB decided that a field is an Integer but the field contains text, then it cannot be imported and its value will be set to Null.

Solution: You must delete the first three rows manually, so that the CSV looks like this:

PART,NBR,SUB,ACT DIM, PART N,DIM,AE908,AE8909,AE9910,AE9911,AE9912
G4037,104,T,100,,150,1,1,,,1
G4037,11,H,300,,300,,,1,1,
G5699,151,ST,45,,45,1,,,,
G4037,155,H,445,,445,1,1,,,1
G4037,156,H,445,,445,,1,,,1

Then you can import like this (for testing I just outputted the result with Debug.Print):

Sub Import()
    Dim rst As New ADODB.Recordset
    Dim names() As String
    Dim tbl() As Variant
    Dim i As Long, row As Long
    
    rst.CursorLocation = adUseClient
    rst.Open "SELECT * FROM [Text;Database=C:\Users\Oli\Desktop\].quote.csv;", CurrentProject.Connection, adOpenStatic, adLockReadOnly
    
    'Get pivoted names
    ReDim names(rst.Fields.Count - 6 - 1)
    For i = 6 To rst.Fields.Count - 1
        names(i - 6) = rst(i).Name
    Next
    
    tbl = rst.GetRows 'The array variable is automatically dimensioned to the correct size
                      'The first subscript identifies the field and the second identifies the record
    rst.Close
    For i = 0 To UBound(names)
        For row = 0 To UBound(tbl, 2)
            If Not IsNull(tbl(i + 6, row)) Then
                Debug.Print names(i) & ", " & tbl(i + 6, row) & ", " & tbl(0, row) & ", " & tbl(1, row) & ", " & tbl(2, row) & ", " & tbl(3, row)
            End If
        Next
    Next
End Sub

Note that the first line with the field names is not a row in the Recordset. Instead, the field names can be accessed with rst(i).Name, where i is a column index.

With the CSV data shown above this test prints:

AE908, 1, G4037, 104, T, 100
AE908, 1, G5699, 151, ST, 45
AE908, 1, G4037, 155, H, 445
AE8909, 1, G4037, 104, T, 100
AE8909, 1, G4037, 155, H, 445
AE8909, 1, G4037, 156, H, 445
AE9910, 1, G4037, 11, H, 300
AE9911, 1, G4037, 11, H, 300
AE9912, 1, G4037, 104, T, 100
AE9912, 1, G4037, 155, H, 445
AE9912, 1, G4037, 156, H, 445