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

 

[Robert2100]

This problem has come back to haunt me - I need SQL

Hi All,

A while ago I posted the problem discussed below. This line-by-line approach works, but it's slow and these CSV data files are getting big (up to 170mb).

Has anyone got an SQL solution to my problem of importing CSV data from files without headers?

Bob

 

[Richard Schollar]

Hi Robert

That's a big file! Have you got access to MS Access? Importing/linking to the csv in Access would then make running a query on it pretty straightforward (even if you didn't want to actually utilise Access in any way ie you could drive it out of Excel).

Best regards

Richard

 

[Robert2100]

Hi Parsnip,

I do have Access, but some people who need to process the data do not. The file's get a little ridiculous sometimes. The creator is trying to be all things to all people. That's one of the reasons I want to be able to use SQL queries, it cuts the size down to a manageable size.

It's just a wierd problem which I find frustrating. If you look at the comments above, especially the creation of the ancilliary file "schema.ini" it looks like the system can do it, but there doesn't seem to be a direct route to the result and I hate it when Excel/Query hide things from you.

I was hoping that someone would have an "aha!" moment and share their wisdom. It's been a while since I've been to the board and it's getting ridiculously big! It's become pseudo on-line chat. Thanks for the response.

Bob

 

[Richard Schollar]

Bob

I think you missed my point: if you create a new Access database and then link it to your csv file (no import) then you can run a query thru Excel to the Access database. Via MSQuery the data will be appended with Field1, Field2,... etc on which you can base your queries. Nobody actually has to use Access.

What do you think?

Richard

 

[parry]

mmm havent tried it but you may want to create a tmptable with F1 etc for the headings then use a select into statement to trfr data then you can do a select on the column of choice.

Personally I would simplify and insert a row in the csv with my own headings then perform the sql functions or straight VBA depending upon what your doing. You can always delete the header row out of the csv when finished.

hth

 

[Robert2100]

Parsnip,

I think you've lost me there.
- From Excel, I create an MS database with fields corresponding to the data?
- From Excel I link the CSV into the MS database?
- Then I can query my heart away?
Can you offer some tips on how to do this?


Parry,

Gidday. I was thinking about this, but got cold feet messing with other peoples files (which are still in use). I also considered creating a copy with the headers, but the files are so big that this could be a problem.

I'm trying to avoid importing the data into Excel before processing, because it's so slow. I'm reading the file line by line, parsing it, then if it meets my criteria writing the selected data into Excel. It's kind of an indirect and inefficient query.

Bob

 

[Richard Schollar]

Bob

1. Create a new Access database
2. Go File>Get External Data>Link Tables
3. Choose Files Of Type "Text Files" and navigate to this monster csv.
4. Click Link
5. On the "Link Text Wizard" dialog, choose "Delimited"
6. Click Next, make sure Comma is selected as the delimiter, and ensure that the "First Row Contains Field Names" checkbox is Unchecked
7. Click Next, and Access automatically creates default headers for you (like Field1, Field2, Field3 etc) - if you click the "Advanced" button, then you can choose how Access interprets these fields (ie as text/numeric etc) you can also choose meaningful header names to be displayed here.
8. Click Finish

Now, you can link to the csv file through MSQuery via Access (ie link to Access via MSQuery and call up the mdb file that links to the csv). At no point have you actually duplicated data, you are just going through a convoluted linking process. It does mean that you can now have meaningful headers for your file.

Does this help? Let me know if you get stuck!

Best regards

Richard

 

[Robert2100]

Hi Parsnip/Richard,

I understand what you're suggesting and it does seem to work (except that Access doesn't like the particular file that I'm trying to query, it thinks it's read only).

This isn't quite what I had in mind. It requires that someone have Access to create the link. There are many different files that are getting generated and they each have different names. The only common thread is that each user will see the data file and have Excel.

After consulting the experts here and trawling through the literature I've given up on a stand-alone query method and I may have managed to convince the creator of the files that they are at fault! They must add a header. Anything else is just bad data management. I'm waiting to see how long it'll take them to do so.

Thanks for your help, I'm putting this one to bed.

Regards,

Bob