How to force excel not to drop rows when opening csv-file?

Background:

We have a web application where the user can export orders in csv-format. For users with Microsoft Excel installed it's the default program. They simply click 'Open' after the file is downloaded. Users mainly use Internet Explorer, Firefox and Chrome. No difference in behaviour.

The problem:

If the user just open the csv-file (from browser or explorer) the file is opened by Excel and data is loaded automagically. But sometimes rows are just missing. Gone. No exception, no message, nothing.

The data is there, if you open the file with notepad you'll see it.

(I suspect it has something to do with special chars, quotes, commas etc but I can't find a root cause for this)

How to make it work:

If you save the file to disk, open excel and selects File -> Open -> Format: Textfiles (*.prn, *.txt, *.csv, *.skv) -> Open excel will launch a import wizard and everything will work perfectly.

I开发者_如何学Gos there anything I can do with the export-file to either force the Import Wizard or just tell Excel not to exclude our critical order information without warning?

I think I've found the cause of this. It seems to be due to regional settings as described in this post and sugesstions from superuser.com

I solved it by wrapping all fields except numbers with quotes and now it works just fine.

Make sure that you use a consistent delimiter scheme across all lines in the file. For example, check if you have both \n and \r\n sequences as line delimiters.