2

So, we at first attempted to allow the user to download CSV files via their browser, which worked fine (some files are around 375MB). However, it looks like the client won't accept that because of a) formatting of numbers, b) removing leading zeros and c) changing long numbers like a state code of 157839481905 to 1.58E10.

If you have used excel you're quite familiar with these "I thought you wanted this" behaviors..

So we'll need to stream an Excel file, but are there different formats? (Assume the user has access to the latest version of Excel for this question)

I believe Excel can open a properly specified XML file as a spreadsheet, which could contain the proper "format this as text" instructions, but on large files that could really get out of hand. Is there a more native excel format that is leaner? Without as much markup? If you are aware of it, where is it described?

-- EDIT --

I thought I'd help the community out on what I've found. First, we tried using ="0040" i.e. an equals before the quotes. Works but too much size on a large file, and Excel freaks out and crashes.

We tried a special character inside the quotes before, worked but formatting was an issue.

Can you STREAM an XLSX file? NO!

Reason: an XLSX file is actually a zipped file with two key files inside, one named sharedStrings.xml which is a list of all UNIQUE cell values, and another which is a list of cells but with a number reference to the first sharedStrings.xml file. The entire folder is zipped and, behold, that's your XLSX file. So an XLSX file must be built, not streamed.

2

It's not really an answer to your question, but well to the cause of your problem.

If you want to avoid the shortening of your numbers in Excel when you open a csv file, you can use ="0123456789" instead of 0123456789, as if it was a formula.

Example:

Name,Value,Value as text
Small int,01234,="01234"
Long int,0123456789,="0123456789"
Very long int,01234567890123456789,="01234567890123456789"

Result:

Result in Excel

  • 2
    Whoever downvoted you really had no business doing so. This is a valid comment in the discussion but we have tried that route. The problem is that all those =s over a large file - plus making the columns even, even for blank values - adds too much size. Thanks though - and maybe another user can use this. – Oliver Williams Aug 30 '17 at 20:18
  • @OliverWilliams thanks but maybe I should have posted it as comment instead – thibautg Aug 30 '17 at 20:35
0

I see few options, but I am not sure if it will work (btw, I have non experience with xml):

First thing, which you have dismissed is to format like text whole spread sheet. Ok, file would be too big.

Second add "'" in front of troublesome numbers, which converts them to text. Again...

Third: The "new" xlsx should be quite good at lowering size of the file, much better than the xls format.

Fourth Change format to text and change encoding of the whole workbook to ASCII or something similarly simple. More info here https://help.surveygizmo.com/help/encode-an-excel-file-to-utf-8-or-utf-16 (if needed I will add more info here)

Not sure, but like last thing I would try to convert to csv and then change encoding to ASCII or smaller coding.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.