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
00400will 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.