Splitting NoName column in SQL Query

Question

I want to use a CSV file Test1.csv to create a Recordset with customized column names.

The CSV file format:

    (Blank)  |  SomeAggr |   (Blank) |   Div1  |  Div2  |  Div3
    -----------------------------------------------------------
    G0.1     |  1.23     |           |   ABC   |  DEF   |  GHI
    G0.2     |  2.45     |           |   JKL   |  MNO   |  PQR
    G0.3     |  9.02     |           |   STU   |  VWX   |  YZA
    G1.1     |  3.32     |           |   ZYX   |  WVU   |  TSR
    G1.2     |  5.53     |           |   QPO   |  NML   |  KJI
    G1.3     |  1.15     |           |   HGF   |  EDC   |  BAZ
    G1.4     |  4.65     |           |   FKJ   |  OTU   |  WKL

The 1st & 3rd Columns have blank headers. The 1st column contains data I want to split it into two columns as shown in SQL Query.

Note - I am creating a recordset and do not want to do any transformations using a WorkSheet.

The final Recordset via SQL Query should look like this:

    GVal  |   Pos    |  Aggr     |   (Blank) |   DV A  |  DV B  |  DV C
    --------------------------------------------------------------------
    0     |    1     |  1.23     |           |   ABC   |  DEF   |  GHI
    0     |    2     |  2.45     |           |   JKL   |  MNO   |  PQR
    0     |    3     |  9.02     |           |   STU   |  VWX   |  YZA
    1     |    1     |  3.32     |           |   ZYX   |  WVU   |  TSR
    1     |    2     |  5.53     |           |   QPO   |  NML   |  KJI
    1     |    3     |  1.15     |           |   HGF   |  EDC   |  BAZ
    1     |    4     |  4.65     |           |   FKJ   |  OTU   |  WKL

I am running the following code:

Option Explicit

Sub Testing()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim strDataSource$, strF1$, strFF1$, strSql$, oCon as Object, oRs as Object, i%, Fld
    strFF1 = "Test1.csv"
    strF1 = "`C:\Users\adam\Downloads\Test Folder`\"
    strDataSource = Thisworkbook.Path

    Set oCon = CreateObject("ADODB.Connection")
    Set oRs = CreateObject("ADODB.Recordset")

    strCon = "Driver=Microsoft Access Text Driver (*.txt, *.csv);Dbq=" & strDataSource & ";Extensions=asc,csv,tab,txt;HDR=Yes;"

   'Getting Top 1 row to loop through fields and create SQL string accordingly.
    strSql = "SELECT TOP 1 * FROM " & strF1 & strFF1
    oCon.Open strCon
    Set oRs = oCon.Execute(strSql)

    i = 1
    strSql = "SELECT "    
    For Each Fld In oRs.Fields

        Select Case True
            Case Is = Fld.Name = "NoName"    '1st NoName column
                If Fld.Value <> vbNullString Then
                    strSql = strSql & "CLng(Replace(Left(" & Fld.Name & ", InStr(" & Fld.Name & ", ""."") - 1), ""G"", """"))" & " AS [GVal], "
                    strSql = strSql & "CLng(Right(" & Fld.Name & ", Len(" & Fld.Name & ") - InStr(" & Fld.Name & ", ""."")))" & " AS [Pos], "
                Else
                    strSql = strSql & Fld.Name & ", "  '2nd NoName column
                End If
            Case Is = Fld.Name = "SomeAggr"
                strSql = strSql & "[" & Fld.Name & "] AS [Aggr],"
            Case Is = InStr(1, Fld.Name, "Div") > 0
                strSql = strSql & "[" & Fld.Name & "] AS [DV " & Chr(i + 64) & "], "
                i = i + 1
        End Select
    Next Fld

    If Right(Trim(strSql), 1) = "," Then strSql = Left(Trim(strSql), Len(Trim(strSql)) - 1)
    strSql = strSql & " FROM " & strF1 & strFF1

    oRs.Close

' >>> getting error on below `Set oRs` line
  [Microsoft][ODBC Text Driver] '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

    Set oRs = oCon.Execute(strSql)
Stop

ExitSub:
    oRs.Close
    oCon.Close
    Set oRs = Nothing
    Set oCon = Nothing

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

Exit Sub
ErrorHandler:
    MsgBox "Error No: " & Err.Number & vbCrLf & "Description: " & Err.Description, vbCritical + vbOKOnly, "An Error occurred!"
    Err.Clear
    On Error GoTo 0
    Resume ExitSub

End Sub

Here is the SQL Query.

SELECT CLng(Replace(Left(NoName, InStr(NoName,".")-1), "G", "")) AS [GVal], 
CLng(Right(NoName, Len(NoName) - InStr(NoName,"."))) AS [Pos], 
[SomeAggr] AS [Aggr],
[Div1] AS [DV A], 
[Div2] AS [DV B], 
[Div3] AS [DV C] 
FROM `\C:\Users\Adam\Downloads\Test Folder`\Test1.csv

The code gives me the following error:

[Microsoft][ODBC Text Driver] '' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

I don't know how to get a reference or select the 1st Blank Column to split it's values into two columns.

The Query works in MSAccess and the 1st NoName column is shown as Field1 and 2nd NoName column is shown as Field3.

Answer 1

SQL code expects single quotes as string deliminators instead of double quotes. In the For Each Fld In oRs.Fields loop, there are two lines which use double quotes instead of single quotes while constructing strSql:

strSql = strSql & "CLng(Replace(Left(" & Fld.Name & ", InStr(" & Fld.Name & ", ""."") - 1), ""G"", """"))" & " AS [GVal], "
strSql = strSql & "CLng(Right(" & Fld.Name & ", Len(" & Fld.Name & ") - InStr(" & Fld.Name & ", ""."")))" & " AS [Pos], "

These should be changed to:

strSql = strSql & "CLng(Replace(Left(" & Fld.Name & ", InStr(" & Fld.Name & ", '.') - 1), 'G', ''))" & " AS [GVal], "
strSql = strSql & "CLng(Right(" & Fld.Name & ", Len(" & Fld.Name & ") - InStr(" & Fld.Name & ", '.')))" & " AS [Pos], "

Answer 2

when i try to replicate your code i get the following: a) Not sure if it is the problem but you have two columns names [NoName] so you need to distinguish between the two. (b) Could you to put the NoName columns in square brackets and try single quotes instead of double quotes?

SELECT 
    CLng(Replace(Left(NoName, InStr(NoName, ".") - 1), "G", "")) AS [GVal],
    CLng(Right(NoName, Len(NoName) - InStr(NoName, "."))) AS [Pos], [SomeAggr] AS [Aggr],
    NoName, 
    [Div1] AS [DV A], 
    [Div2] AS [DV B], 
    [Div3] AS [DV C] 
FROM 
    `C:\Temp`\Test.csv