Using SQL on columns without headers?

Robert2100

New Member
Joined
May 26, 2003
Messages
38
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.

Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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")
 
Upvote 0
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.

Code:
'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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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 :-
Code:
'============================================
'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.
 
Upvote 0
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
 
Upvote 0
Back to first principles then. I think you will only need to change the file path here :-
Code:
'=================================================
'- 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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,046
Messages
6,105,582
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top