48

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?

6
  • 10
    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. Commented Jun 18, 2018 at 18:26
  • 4
    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
    Commented 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
    Commented Jun 18, 2018 at 22:30
  • 4
    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
    Commented Jun 19, 2018 at 14:53
  • 1
    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
    Commented Jun 3, 2021 at 13:41

4 Answers 4

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.

30
  • 35
    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
    Commented Jun 18, 2018 at 8:55
  • 27
    @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
    Commented 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". Commented 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
    Commented 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. Commented 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.

12
  • 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
    Commented Jun 18, 2018 at 11:55
  • 6
    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
    Commented 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
    Commented 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
    Commented 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
    Commented Jun 19, 2018 at 9:17
2

The only public definition of CSV format is RFC 4180 and such CSV file should use UTF-8 encoding and no byte-order-mark (BOM) because UTF-8 files do not depend on CPU byte order and therefore do not require BOM, unlike UTF-16 files used by most Microsoft Windows applications.

However, if your CSV file must be Microsoft Excel compatible and you want to use UNICODE codepoints outside US ASCII, you have to use BOM at the start of Microsoft variant of CSV files. Note that some variants of MS Excel also require replacing all commas with semicolons – in Microsoft universe the C in CSV stands for semicolon sometimes. Logically these should be called SSV files for "semicolon separated values" but Microsoft calls them CSV files, too.

In practice, you can generate either RFC 4180 compliant CSV files, or you can generate Microsoft compatible CSV files.

7
  • 1
    This is exactly what we ended up doing: The user can choose between "CSV (RFC 4180)", "CSV (Excel compatible)" and native Excel (xlsx) export. Like you, all our customers use Windows regional settings with a decimal comma (instead of point) and the semicolon as the list separator, so "CSV (Excel compatible)" defaults to that.
    – Heinzi
    Commented Nov 22, 2023 at 11:05
  • 1
    BOM is a misnomer anyway. In utf16 it’s a zero width space, or an explicitly illegal character if you guessed the byte order wrong. In utf-32 everything is an illegal character you guess the byte order wrong. In utf-8 it is an indicator that it is utf-8 and not some other encoding.
    – gnasher729
    Commented Nov 23, 2023 at 13:52
  • Yes, and thanks to re-using zero width space for BOM, there's no standard way to transmit data that should start with a zero width space. If you send if once, the other end may strip it away. If you send it twice and the other end doesn't strip it away, you end up doubling one character. I personally think BOM should not be used for anything and you have to differentiate between UTF-16LE and UTF-16BE some other way. Commented Nov 23, 2023 at 15:35
  • @MikkoRantalainen Kind of agree. On the other hand, if I WANT to send this character I would precede it with a BOM, and if I receive the data I’d always remove the first BOM.
    – gnasher729
    Commented Nov 27, 2023 at 16:55
  • 1
    @Junaga: different Excel versions have different behavior when it comes to CSV files. So there's no "that version" for CSV handling in Microsoft universe. Commented Oct 30 at 14:52
0

When you are not told what encoding text is, you have to make an educated guess. Sometimes it’s simple: JSON must be some UTF encoding, starting with an ASCII character and not containing zero code points, and that’s enough to determine the encoding from the first four bytes. You can check for BOM and ignore it if present, it’s just pain.

Some software adds BOMs to UTF-16 or UTF-32 to indicate byte ordering or to UTF-8 to indicate its UTF-8. A sequence of bytes resembling a BOM in another encoding is so rare, you could ignore it.

Now CSV is supposed to be UTF-8. For security reasons, a reader will verify this. So I wouldn’t add a BOM. I would absolutely not send Windows-1252 because that’s going to be trouble, but as a reader I would handle it. I would also handle the BOM as a reader.

Your Answer

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

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