Opening web-located text files in MS Excel

Stefan Lennerbrant
Stefan Lennerbrant used Ask the Experts™
on
I generate text files in a web application, and I would like the client (mainly IE11 running on Windows 7 and 8) to open the files in Excel.

I serve the files as:
Content-Type: application/vnd.ms-excel
Content-Disposition: filename="myfile.txt"

Open in new window

and the content of the files is something like:
abc <tab> def <tab> ghi
123 <tab> 456 <tab> <789>

Open in new window

I have also tried using filename="myfile.htm" with a html content: <table><tr><td>...several rows/cells...</td></tr></table>


I run into several problems with this.
The biggest one is that this only works on small files. As soon as the file is larger than "quite small", WIndows and/or the browser "re-classifies" the file and renames it to myfile.xls (obviously(?) due to the mime type used). And then the Excel extension hardening kicks in and will issue a warning to the user. Not good!

In Chrome, there is a completely different problem. The file will always be downloaded to disk and will "loose" the connection to its mime type. The filename (extension) is never changed, as happens with IE, but saved on disk is is "only a text file" and will open in Notepad or similar, and not in Excel.
In IE, I get the same problem (of course) if the user chooses to save the file on disk, instead of opening it. In addition, IE sometimes renames the file to myfile.xls - which adds to the confusion :-)


So, in short - what is best practice?
How do I generate web content in non-binary form (tab-separated fields, comma-separated fields, html, or other text formats) that a modern browsers will open in MS Excel?
(the same question actually relates to MS Word, as well)

Any ideas?

/Stefan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you use an extension that is associated with Excel (such as CSV) does it behave closer to what you want?
NB: the fact that is called CSV won't stop excel parsing as tab-delimited but CSV is one of Excel default types whereas .txt obviously defaults to Notepad

Author

Commented:
Interesting, I actually did no test that combination (big file and .csv)
Using myfile.csv the "big content" does not get renamed. I assume that csv is "enough coupled" to excel, just as you suggested.

However, my Excel does not accept <tab> as a delimiter i CSV files - it did some years ago but not today (as it seems). So I get all data in one single column.
I could however quote all such data, of course. Another problem with CSV is that excel uses "," in some coutnries and ";" in other countries, as far as I know. However, this might be a way to handle the problem.
But... with CSV files, if possible I prefer using <tab> as delimiter, to avoid these "quoting issues".

/Stefan

Author

Commented:
Yes, I just tested. The comma/semicolon issue is a problem :-(
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

yes I found the same, I'm sure it used to handle the import better in earlier versions. Its interesting that double clicking behaviour also seems different - if you load excel first and then use file, open it takes you to the import dialog whereas double click just loads text in to the column.


It may be getting more complicated than you would like but what about using the 'XML Spreadsheet 2003' option - if you save a test file you will find all the tags that are needed and I imagine some trial and error will allow you to cut that down (if needed)

As an aside - why do you use "content-type: ms-word" rather than excel?

Author

Commented:
Sorry sorry sorry, typo! Hm, how do I correct my initial question?
Of course I use "Content-Type: application/vnd.ms-excel"

Some time ago (years), tab characters were always accepted as delimiters in CSV files. So I didn't need to choose between , and ; based on the nationality of the individual user.

Using "XML 2003" is absolutely an option, I'll look into that.
But I am guessing that it works just as bad (?) as using HTML as file content. The XML extension isn't linked to Excel, so why shouldn't Windows rename the file to .xls just as happens to HTM files?

I'm trying to investigate why small files keep the .txt (and .htm) extension filename, but large files are renamed to .xls
As the mimetype is "application/vnd.ms-excel", I understand that the Windows mime sniffing is not triggering any actions, so this must be due to some other reason?

/Stefan
XML is much more proscriptively managed than text/html so the filetype should be maintained - be aware that 2003 spreadsheet is limited to 65536 rows.

On my system the xml extension is associated with Excel, I suppose there may be a problem if the default is another app unless you are issuing a qualified command line.

Author

Commented:
On my (and my collegues') clients, XML is not associated to Excel.
But XML is definitively something to look into. However, walking down that road I really would prefer HTM instead, as it makes it easier to create nice layouts, if needed.

The "problem" with getting different behaviour using small and large content files (IE renames the file to .xls only when the file is not very small)...
Is this something that is known to the community here?

/Stefan
I've never come across this behaviour so it might be worth referring the original question for attention to get more eyes on it.

It might also be worth restating the question - I think what you are trying to achieve is that although the data is exported as text the mime-type opens Excel and there may be a different way of achieving that

In the meantime I found the following at stack overflow, about half way down someone has provided a powershell script that fixes up a text file so Excel will import it an accurate CSV representation of it - even if the locale changes comma to semi-colon. I post it as a pointer to other people wrestling with similar problems rather than a solution as you would need to know the locale of the user making the request and then use this transform to give them a suitable file - but hopefully it will offer other directions to look.

stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates

Author

Commented:
Together with adding a top row "sep=x" (the x is the "tab character"), one working solution seems to be to download the data as CSV.
IE/Windows does not rename CSV to XLS due to the mime type, not even for large files, (probably as CSV is listed as an Excel format) and Excel seems to correctly parse tab-separated with this (quite undocumented) "sep" setting.

Still, there is no solution to feeding HTML content to Excel, but at least CSV makes it possible to download/open tab-separated text files.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial