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:
Content-Type: application/
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 LennerbrantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stefan LennerbrantAuthor 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 LennerbrantAuthor Commented:
Yes, I just tested. The comma/semicolon issue is a problem :-(
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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?
Stefan LennerbrantAuthor Commented:
Sorry sorry sorry, typo! Hm, how do I correct my initial question?
Of course I use "Content-Type: application/"

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/", I understand that the Windows mime sniffing is not triggering any actions, so this must be due to some other reason?

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.
Stefan LennerbrantAuthor 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?

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.
Stefan LennerbrantAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.