Export data to Excel headaches?
From working with Excel a couple of contracts back, try these things:
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.
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.
Ditto. The CS in CSV stands for Comma Separated.
Thank you all for your suggestions.
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., they didn't remove the feature. It's right in the same place it's always been.
and try using quotes around fields if they contain characters that might be confused for delimiters etc
i like i
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
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.
We also use ODBC Driver to export files directly, it has worked pretty well, once you figure out some of the more bizarre bugs.
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).
Fog Creek Home