Generating CSV file for Excel, how to have a newline inside a value

by

You should have space characters at the start of fields ONLY where the space characters are part of the data. Excel will not strip off leading spaces. You will get unwanted spaces in your headings and data fields. Worse, the " that should be “protecting” that line-break in the third column will be ignored because it is not at the start of the field.

If you have non-ASCII characters (encoded in UTF-8) in the file, you should have a UTF-8 BOM (3 bytes, hex EF BB BF) at the start of the file. Otherwise Excel will interpret the data according to your locale’s default encoding (e.g. cp1252) instead of utf-8, and your non-ASCII characters will be trashed.

Following comments apply to Excel 2003, 2007 and 2013; not tested on Excel 2000

If you open the file by double-clicking on its name in Windows Explorer, everything works OK.

If you open it from within Excel, the results vary:

  1. You have only ASCII characters in the file (and no BOM): works.
  2. You have non-ASCII characters (encoded in UTF-8) in the file, with a UTF-8 BOM at the start: it recognises that your data is encoded in UTF-8 but it ignores the csv extension and drops you into the Text Import not-a-Wizard, unfortunately with the result that you get the line-break problem.

Options include:

  1. Train the users not to open the files from within Excel 🙁
  2. Consider writing an XLS file directly … there are packages/libraries available for doing that in Python/Perl/PHP/.NET/etc

More Related Contents:

  1. Importing CSV with line breaks in Excel 2007
  2. Is it possible to force Excel recognize UTF-8 CSV files automatically?
  3. Stop Excel from automatically converting certain text values to dates
  4. Microsoft Excel mangles Diacritics in .csv files?
  5. Excel to CSV with UTF8 encoding [closed]
  6. Excel CSV – Number cell format
  7. Export hashtable to CSV with the key as the column heading
  8. Save each sheet in a workbook to separate CSV files
  9. Export each sheet to a separate csv file
  10. How to correctly display .csv files within Excel 2013?
  11. Convert xlsx file to csv using batch
  12. How do I use awk under cygwin to print fields from an excel spreadsheet?
  13. converting an Excel (xls) file to a comma separated (csv) file without the GUI
  14. sep=”;” statement breaks utf8 BOM in CSV file which is generated by XSL
  15. How to save excel columns with long numbers into csv?
  16. Can Excel interpret the URLs in my CSV as hyperlinks?
  17. Read/Parse text file line by line in VBA
  18. filter out multiple criteria using excel vba
  19. Excel VBA autofilter all but three
  20. Getting the actual usedrange
  21. Delete a row in Excel VBA
  22. Which encoding opens CSV files correctly with Excel on both Mac and Windows?
  23. How can I run VBA code each time a cell gets its value changed by a formula?
  24. Referencing Excel Userform Control Name Value From String (VBA)
  25. Refreshing all the pivot tables in my excel workbook with a macro
  26. Countif With Multiple OR Criteria
  27. Vlookup to copy color of a cell
  28. How to delete rows in Excel based on criteria using VBA?
  29. Macro to export MS Word tables to Excel sheets
  30. How to protect Excel workbook using VBA?

Leave a Comment