Data Extraction from external source... HELP!

Thomas · May 9th, 2002, 02:39 PM

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. · May 9th, 2002, 03:15 PM

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

Thomas · May 9th, 2002, 04:37 PM

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 · May 9th, 2002, 08:16 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 9th, 2002, 02:39 PM	#1
Thomas Board Regular Join Date: May 2002 Location: Dallas Posts: 365	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

May 9th, 2002, 03:15 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Make a copy of your .csv file and change it's extension to .txt. Edit the file. Are your values quoted?

May 9th, 2002, 04:37 PM	#3
Thomas Board Regular Join Date: May 2002 Location: Dallas Posts: 365	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.

May 9th, 2002, 08:16 PM	#4
PaddyD MrExcel MVP Join Date: May 2002 Posts: 13,278	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