CSV stopping excel from changing column values

Question

Here is the deal. We are approaching the limits I think of what you can do with CSV but I am wondering if anyone has a solution.

We have a CSV file that is 357MB in size. Downloads fine, and doesn't take too long with a good connection.

However, most clients open that file in Excel. And there are several problems:

• A field 00400 will show as 40 (leading zeros removed)
• A value of 0.04088100 will become 4.08E-2 (or something like that)
• Dates and times get changed

The BAD fix: We tried adding an = character before the "0040" - it works for small files but not for a large file - Excel can't treat them all as formulas, too much memory and file never opens

The GOOD fix: We added an ASCII character 28 (a File Stop or FS, see http://www.theasciicode.com.ar/extended-ascii-code/uppercase-slashed-zero-empty-set-ascii-code-157.html) All fields with this "hidden" value showed correctly, except they are left justified (since treated a text)

The ONLY problem: Unfortunately the good fix has one problem. When you use formatting to right-justify the fields with the CHR(28) to the left, there is a small space to the right, which you can't delete because it deletes the right character instead.

A POSSIBLE fix: We did some experimenting and found that adding CHR(157) to the left of the Excel-abused field values also stopped it, AND right-justified. However, I feel very strange doing this, and wonder if there are any drawbacks.

This may seem like an obscure question; I have done a lot of searching on this. Is there a better approach? Requirements will not allow the file to be saved-as an .xlxs file etc.

Answer 1

At the risk of sounding defeatist, I'd look into an alternative solution. Here's why:

The scariest problem is A value of 0.04088100 will become 4.08E-2 because Excel is changing that value; if you save the file, you'll lose precision and the last however many digits are going to be set to 0. I have this routinely trying to match 20 or 30 digit long numbers that Excel balks at.

Also, inserting invisible characters is only going to cause headaches when people trip over them; if someone accidentally deletes the invisible character, they won't be able to re-enter the leading zeros and they won't know why. This is also going to be an issue if those lines are ever imported somewhere; although you know how Notepad & Excel will react, what if those are control characters in another application?

Best of luck.