Using SQL on columns without headers?

[Robert2100]

I'd like to extract data from a CSV file which has no header.
The consequence of this is that the first row is always different every time the data file is created.
Can I refer to columns generically (eg. Column1, Column2 etc...)?

My trial code to extract the first column from the CSV file "Bob.txt" is below. You can see that following the SELECT statement there is some date/time data. In this particular file this is the first entry.

vSQLStrTime = "SELECT Bob.`2005/05/20 11:27:12`"

How do I make this column reference generic?
Your help would be appreciated.

vPathName = "C:\Documents and Settings\User\Desktop;"

vArrayStr = "ODBC;DefaultDir="
vArrayStr = vArrayStr & vPathName
vArrayStr = vArrayStr & ";Driver={Microsoft Text Driver(*.txt; .csv)};DriverId=27;FIL=text;Ma"
    
'Build SQL String
vSQLStrTime = "SELECT Bob.`2005/05/20 11:27:12`"
vSQLStrTime = vSQLStrTime & Chr(13) & "" & Chr(10)
vSQLStrTime = vSQLStrTime & "FROM Bob.txt Bob"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array(vArrayStr)), Destination:=Range("A1"))
   .CommandText = Array(vSQLStrTime)
   .Name = "QueryArbinResults"
   .FieldNames = False
   .RefreshStyle = xlOverwriteCells
   .Refresh BackgroundQuery:=False
End With

 

[BrianB]

Record a macro of an import .csv. Saves all sorts of headaches. Without headers in the file Excel obligingly puts the field header names F1, F2, .. etc.

.Sql = Array( _
"SELECT TEST.F1, TEST.F2, TEST.F3, TEST.F4, TEST.F5, TEST.F6" & Chr(13) & "" & Chr(10) & "FROM TEST.csv TEST")

 

[Robert2100]

Hi Brian,

Thanks for the response. I tried using the TEST.F1 format you suggested, but had no luck. I assume that you're suggesting "F1", "F2" etc as generic column reference headers.

'Build SQL String 
vSQLStrTime = "SELECT Bob.F1" 
vSQLStrTime = vSQLStrTime & Chr(13) & "" & Chr(10) 
vSQLStrTime = vSQLStrTime & "FROM Bob.txt Bob"

Just for completion I also tried this where the file Bob is .txt and also .csv.

I'd previously recorded both a query and an import text process. They both assign whatever text is in the first row as the column header, which in my case changes every time the data is refreshed.

Am I missing something?

 

[Robert2100]

Generic SQL reference to column header in CSV?

I haven't had any luck with this request. Can anyone shed any light on how I refer to columns in a text file with no header?

 

[BrianB]

Have you tried my suggestion that you import the file via "File/Open .. ?

 

[Robert2100]

Brian,

Yes I did. No luck. There is no reference to headers unless I use:
- Data/Get External Data/New Database Query/
And then it just picks up the first row of data as the column header.

Neither
- File/Open, or
- Data/Get External Data/Import Text File/
Makes any reference to column headers.

Do you have some code that illustrates what you do to import only defined, but un-named, columns from a text file (eg Columns 1 and 3)?

Bob

 

[BrianB]

I have successfully produced a worksheet containing just columns 1 and 3 of a textfile with headings "F1" and "F3". A recorded macro of the process ran correctly. It was a bit problematical because unless certain options are chosen en route other options are not available.

I suggest you set the macro recorder and follow this method :-

'============================================
'Data/Get External Data/Create New QueryTable
'============================================
'============================================
'*Choose Data Source*
'============================================
'   <New DataObject Source> ... (OK)

'============================================
'*Create New Data Source*
'============================================
    '1. Name
    '2. Driver ... Microsoft Text .csv,.txt
    '3. Connect
    
'============================================
'*ODBC Text Setup*
'============================================
    '[Options] ... *.txt
    'Use Current Directory .. uncheck !!
    '[Define format]
    
'============================================
'*Define Text format*
'============================================
    'Select file
    'Column Name Header ... uncheck !!
    '[Guess]
    '... (OK)
    
'============================================
'*ODBC Text Setup*   (OK)
'============================================
'============================================
'*Create New DataObject source*
'============================================
    '4. .. select file .... (OK)
    
'============================================
'*Query wizard - Choose Columns*
'============================================
    '....... etc.

 

[Robert2100]

Brian,

I followed your steps and he presto! it works. What I found wierd is that everytime you do that it creates a file called "schema.ini" that has data about all the files that 'qualified' to be selected for import (ie. every *.csv and *.txt file).

If you try and run the macro code without the presence of schema.ini, the code falls over. Did you see this?

The problem is that you have to create schema.ini before you can run the code. And since schema.ini contains information about the columns and the format of the data they contain, I'm back to where I started!

Regards,

Bob

 

[BrianB]

Back to first principles then. I think you will only need to change the file path here :-

'=================================================
'- MACRO TO IMPORT DELIMITED TEXT
'- WILL HANDLE MULTIPLE DELIMITERS
'- Brian Baulsom
'=================================================
Sub IMPORT_PIPE_DELIMITED()
    Dim FileName As String
    Dim FileNum As Integer
    Dim ToRow As Long
    Dim ToCol As Integer
    Dim ws As Worksheet
    Dim TextLine As String
    Dim MyDelimiter1 As String
    Dim MyDelimiter2 As String
    Dim MyField As String
    Dim LineLength As Integer
    Dim c As String
    Dim n
    '-------------------------------------
    '- initialise
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    FileName = "C:\TEST\Sheet1.txt"
    Set ws = ActiveSheet
    ws.Cells.ClearContents
    ToRow = 2
    MyDelimiter1 = "|"  ' pipe
    MyDelimiter2 = ","  ' comma
    '-------------------------------------
    '- MAIN LOOP
    FileNum = FreeFile()
    Open FileName For Input As #FileNum
    Do While Seek(FileNum) <= LOF(FileNum)
        Application.StatusBar = _
            "Importing Row :  " & ToRow
        '-----------------------------------------------
        '- parse line
        Line Input #FileNum, TextLine
        LineLength = Len(TextLine)
        ToCol = 1
        MyField = ""
        For n = 1 To LineLength
            c = Mid(TextLine, n, 1)
            If c = MyDelimiter1 Or c = MyDelimiter2 Then
                ws.Cells(ToRow, ToCol).Value = MyField
                ToCol = ToCol + 1
                MyField = ""
            Else
                MyField = MyField & c
            End If
            ws.Cells(ToRow, ToCol).Value = MyField
        Next
        '-----------------------------------------------
        '- check max row number for worksheet
        If ToRow = 65536 Then
           Set ws = ActiveWorkbook.Sheets.Add ' add a sheet
           ToRow = 2
        Else
           ToRow = ToRow + 1
        End If
    Loop
    '- finish ----------------------------------
    Close #FileNum
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    MsgBox ("Imported " & ToRow - 1 & " lines.")
End Sub

 

[Robert2100]

Brian,

Thanks for that, it works perfectly. I'd been trying to avoid having to open and read the file line by line. SQL is such a wonderful tool, when it does what you want.

Thanks for your time and effort. I've gone with your code. One day perhaps I'll find an SQL route to do the same thing.

Bob