Data Extraction from external source... HELP!

[Thomas]

Ok, this one's driving me absolutely nuts:

I've been using Excel's Query wizard for some time now to do SQL-type extractions, but I'm befuddled on this one. I'm attempting to query a .csv file, but for some reason all the cells are returning as text values, even though there are either numbers or dates ~only~ in them. If I go to one of these cells, push F2, then enter, it assumes its proper numerical value. If I open the .csv file into Excel, all the number-type values are reading as they should.

Can anyone help on this? Is there a way to set up the text driver in the Data Extraction wizard to accept numerical values? I'd even settle for a macro or VBA code that activates when the data is pulled; the only issue with this is I'm pulling 45,000+ rows and up to 100 columns at a time.

PLEASE HELP!

Thanks in advance
~ Thomas

 

[Mark W.]

Make a copy of your .csv file and change it's extension to .txt. Edit the file. Are your values quoted?

 

[Thomas]

I tried converting it to a .txt file, but that doesn't seem to do the trick.

The frustrating thing in this is when I look at the file that I am querying itself, all the formats are as they should be. I've done SQL extractions in the past that preserve the date/time and numerical formats as they should be, so I can't figure this one out.

I also tried importing into an Access spreadsheet; the data read in fine, but when I attempt to query it through Excel, I get the same problems.

Any other thoughts?

Thanks.
~Thomas

P.S. - FYI, what I'm doing here is creating a database in SAS, and exporting to a public directory as a .csv file. There is an auto FTP set up in the Excel workbook that automatically transfers the database from a UNIX server and posts the data into an Excel spreadsheet, then formats the date values to be read by Excel properly. Once all the auto-processing is complete, the user is allowed to query the data file.

 

[PaddyD]

Probably missing the point here, but if you're using SAS, why not use PROC DBLOAD to export directly to excel- you can then control all the formatting behaviour directly....

Paddy