Fog Creek Software
Discussion Board




Export data to Excel headaches?

Hello all,

I have a web application here, with a dynamic SQL query engine I wrote. This works fine, but when I try to export data to Excel, by setting the mime-type header to the excel one (application/vnd.ms-excel) it opens incorrectly.

I format the output as a CSV file, so the data looks like:

FIELD1;FIELD2;FIELDn
FOO;BAR;TEST
DUMMY;BLAH;BAR

and so forth. Problem is when Excel opens itself, all the data is displayed in the A column, since it just opens the resulting text file, and not running its import feature.

Is there a way to tell Excel it should expect CSV formatted data when starting it like this from a web application?

Suggestions and comments are much appreciated.

Thank you.

Patrik
Friday, September 19, 2003

From working with Excel a couple of contracts back, try these things:

1) don't use a semi-colon, use a tab

or

2) output your data as an HTML-formatted table - Excel _will_ correctly read these tables (including formatting infotmation, if you need it).

My project eventually used the second option, but the first would be easier for you to try.

RocketJeff
Friday, September 19, 2003

I bet you'd have better luck with commas, even.  After all CSV doesn't stand for (semi-)colon separated values.  We use a lot of tab delimited files here, and we can drag and drop them into excel, even though they have non standard extensions, and excel opens them without complaint.

Keith Wright
Friday, September 19, 2003

If the file type is CSV, Excel will be expecting a comma delimited file. You can use tabs also because Excel translates a Tab into a Next Cell.

One caution on commas, if your data has any commas in it you're in for a world of hurt, so use tabs in that case. 

Lou
Friday, September 19, 2003

Ditto. The CS in CSV stands for Comma Separated.

Nick
Friday, September 19, 2003

Thank you all for your suggestions.

Tab worked just fine.

Patrik
Friday, September 19, 2003

Excel 97 had a "text to columns" command that allowed you to specify the column separator. I can't believe they would have removed this in later releases.

A.T.
Friday, September 19, 2003

A.T., they didn't remove the feature. It's right in the same place it's always been.

Troy King
Friday, September 19, 2003

and try using quotes around fields if they contain characters that might be confused for delimiters etc

i like i
Friday, September 19, 2003

I opened a semi-colon delimited file just today in Excel 2000 and it worked fine once I told Excel what the delimiter was (I was using the data import wizard under data/get external data / open text file) Now I need to write a script to do it for me and I am in business.  Tab delimited is the default and CSV files are expecting commas ,but this must be a property that can either be scripted or put in to a macro.

A Software Build Guy
Friday, September 19, 2003

From what I can remember from my pass excel exporting projects. HTML Tables are pretty good, but if you want to use CSV, use commas.. numbers that don't have commas or crap in them can be un-doublequoted. All the rest needs to be doublequoted. When there is an embedded doublequote, you can double it to escape it.

Li-fan Chen
Saturday, September 20, 2003

We also use ODBC Driver to export files directly, it has worked pretty well, once you figure out some of the more bizarre bugs.

Li-fan Chen
Saturday, September 20, 2003

RocketJeff,

Thanks for pointing out the HTML-formatting option; I rewrote the code to produce a HTML table instead of just tab-separating the data. Very useful to make nice looking colored reports and stuff.

I would also like to say thanks to all the others that pointed alternatives out, and being helpful.

Patrik
Monday, September 22, 2003

Glad it worked for you - It really saved my butt when a client wanted formatted Excel output but couldn't insure that Excel was actually installed on the machine (i.e. I couldn't use COM to talk with Excel directly).

RocketJeff
Tuesday, September 23, 2003

*  Recent Topics

*  Fog Creek Home