Opening web-located text files in MS Excel
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:
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
I serve the files as:
Content-Type: application/vnd.ms-excel
Content-Disposition: filename="myfile.txt"abc <tab> def <tab> ghi
123 <tab> 456 <tab> <789>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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, I just tested. The comma/semicolon issue is a problem :-(
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?
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?
ASKER
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"
/Stefan
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"
/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.
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.
ASKER
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
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/question
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/question
ASKER
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.
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.
ASKER
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