How to stop Excel from mangling data

[Vaughan]

Folks

This is more an Excel problem... Ms Excel 2000 SR-1 on Windows 2000 SP4.

The clients want to export to Excel, delete unwanted rows and columns, maybe change a few cells, then save the file. It's got to be in csv format. It's not working because Excel mangles the application number.

The "application number" field has the format YY-XXXX eg., 04-0122, or 05-2356. The problem is that whenever the data is exported as csv and opened in Excel, Excel *without prompting* converts the application numbers into dates. Not all application numbers are converted, but most are. Changing the cell format to general, number or text does not change the data back to its original value.

Here are some examples: the first columns is the original application number; the second is the date that Excel converts it to; the third is the data that is left when the cell format is changed to general, number or text.

04-6315 | Apr-15 | 1612637

04-6316 | Apr-16 | 1613003

04-6317 | Apr-17 | 1613368

What I would like to know: is there a way to stop Excel from modifying the data? Or to prompt before modifying the data?

Thanks.

[Wim Decorte]

Same on XP with Excel 2003.

If you name the export file with a .TXT extension, you can choose the format for each column. If you want to automate it you'll need an Excel VBA macro or a VBscript...

Maybe somebody else can come up with a better solution. I tried single and double quotes around the first field but that doesn't help.

[Barbecue]

Excel is a great tool for quickly manipulating and cleaning up data, but for handling delimited-text file formats, it sucks.

To prevent this from happening you need to change the cell format during the import to Excel. As Wim pointed out, you can do this by changing the extension, which will give you the wizard.

If the wizard is undesireable, another way to do this is to insert a leading space in front of the problem string. That will force Excel to interpret it as a text value during import. You can then trim the leading space afterward.

If the goal is to have a CSV file at the end, you can export from FM to XML, edit that with Excel, and then save the results as a CSV from there.