47

Our line-of-business software allows the user to save certain data as CSV. Since there are a lot of different formats (all called "CSV") in use in the wild, we are tying to decide what the "default format" should look like.

  • Regarding line/field separators and escaping, there is a standard we can use: RFC 4180.

  • Regarding text encoding, UTF-8 seems to have emerged in the last decade as the "default text file format", so we will use that.

The one question left open is: Should we add a BOM at the start or not? I have read multiple opinions and pros/cons on the use of BOMs in general, but is there an "official" recommendation or at least some kind of community consensus on the use of BOMs in CSV files?

Improve this question
5
  • 9
    If it has a BOM then it is not UTF-8. But what format do the programs want. If they need a BOM (mainly micro-sloth) then you need to add one, but UTF-8 + BOM ≠ UTF-8.
    – ctrl-alt-delor
    Jun 18, 2018 at 18:26
  • 3
    Even though CSV is apparently easier to generate, there are so many compatibility issues, especially if you stray out of pure 7-bit ASCII, that I would very, very, strongly recommend you generate actual XLSX if the goal is for users to open it in Excel (rather than re-import it in some other software, in which case you will have to give options for separators, encoding, etc.). There are libraries for most languages out there, and you'll save you and your users a lot of time.
    – jcaron
    Jun 18, 2018 at 22:27
  • 2
    If you do take the CSV route, check what happens when you open the file on both Mac and PC, ideally with several versions of Excel. Also be aware that some versions of Excel do not behave the same when you double-click on the file to open it or open the file via the menu.
    – jcaron
    Jun 18, 2018 at 22:30
  • 3
    Why does it matter if it opens correctly in Excel? Nothing in the question states Excel needs to be able to parse the generated file...
    – rubenvb
    Jun 19, 2018 at 14:53
  • BOM is in the UTF-8 spec. It’s use is discouraged since it has very little point, but it’s valid UTF-8.
    – Andy Lynch
    Jun 3, 2021 at 13:41

2 Answers 2

Reset to default
62

Not for UTF-8, but see the various caveats in the comments.

It's unnecessary (UTF-8 has no byte order) unlike UTF-16/32 and not recommended in the Unicode standard. It's also quite rare to see UTF-8 with BOM "in the wild", so unless you have a valid reason (e.g. as commented, you'll be working with software that expects the BOM) I'd recommend the BOM-less approach.

Wikipedia mentions some mainly Microsoft software that forces and expects a BOM, but unless you're working with them, don't use it.

Improve this answer
29
  • 34
    There's also widespread software requiring a BOM: Excel needs a BOM to correctly identify a CSV file as UTF-8 rather than "ANSI", i.e., the local compatibility locale. (But Excel also does strange things when saving such a file, so we advise users to use our "real" Excel export instead of the CSV export if they want to open the file with Excel.)
    – Heinzi
    Jun 18, 2018 at 8:55
  • 24
    @Heinzi I learnt a long time ago that you cannot really win when working with CSV and Excel. It's simply a lousy CSV-reader. Too bad it's what normal users expects.
    – pipe
    Jun 18, 2018 at 13:53
  • 9
    @Voo: Requiring a BOM for UTF-8 certainly violates the standard, considering it is "neither required nor recommended".
    – Deduplicator
    Jun 18, 2018 at 15:57
  • 14
    @Deduplicator: MS-DOS and Windows systems have a large base of legacy text files in encodings other than UTF-8. Quality applications allow a user to specify how a text file is encoded when opening it, but often include an "auto" option. If a user selects "UTF-8", a UTF-8 file will be opened correctly with or without a BOM. If a user selects "auto", some UTF-8 files that don't have a BOM may be misidentified as using some other encoding. I'm not sure what one would expect an application to do differently, since files that are "misidentified" could be bit-for-bit identical with...
    – supercat
    Jun 18, 2018 at 16:14
  • 7
    @Voo: That conflicts with many other format-specific requirements where a BOM is illegal. For example, a shell script with a BOM before the #! is invalid. At best a BOM in UTF-8 is "allowed, when no format-/application-specific requirement precludes it", not "allowed", and as such should not be used. The standards are actually clear about the SHOULD NOT.
    – R.. GitHub STOP HELPING ICE
    Jun 18, 2018 at 19:39
11

There still is no widespread convention AFAIK, though certainly UTF-8 is now generally accepted.

The BOM is an awful artifact:

It is invisible (zero-width space).

Some software might break on the first column name not containing only letters, but that strange BOM in front.

The header line might perchance be copied for value lines corrupting the first value.

It is only needed by some Windows software to distinghuish between one of the ANSI encodings used by that local Windows machine, and UTF-8. Notepad, Excel.

So the sad thing is one should support the BOM. Maybe optional.

Use a naming scheme for the files (...-utf8.txt, ...-utf8bom.txt).

In many cases we could use HTML as export alternative. This allows setting the encoding in the file. An extra feature is the background/foreground coloring of rows and cells. Which heightens the quality of the export.

Improve this answer
10
  • 16
    Whether formatting "heightens the quality of the export" is hugely dependent on the intended use of the file. CSV is often used as a simple machine readable format, and making the recipient parse HTML instead would be a big disadvantage in that case.
    – IMSoP
    Jun 18, 2018 at 11:55
  • 5
    If you're choosing a naming scheme, keep the audience in mind. -utf8-windows.csv is better. Almost everyone knows what Windows is, in the context of computers, but far fewer users know what a Byte Order Mark is.
    – MSalters
    Jun 18, 2018 at 12:49
  • 2
    @Davislor yes if it is a broadly communicated known standard. Otherwise error reports will come in about tschüß being garbage whereas tschüß should have been written. On StackOverflow many IT errors are about encodings. End users will experience problems too.
    – Joop Eggen
    Jun 18, 2018 at 13:48
  • 3
    @JoopEggen "Broadly communicated known standard" in what community exactly? I've been doing software development for nearly 10 years now and I've never seen that - not even on windows, and certainly not on Linux or OSX where you almost always deal with utf-8.
    – Cubic
    Jun 18, 2018 at 14:16
  • 1
    @JustinTime yes since some years even, but not before. The MS developers are not that bad (Posix compliance, now UTF-8 support).
    – Joop Eggen
    Jun 19, 2018 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.