20

What's the difference between - CSV (MS-Dos), CSV (Macintosh), CSV (comma delimited) file types in excel 2010? They are all listed as a save file type, but ultimately are Comma Separated Value files.

2 Answers 2

23

The difference between [them] is if you have certain special characters in text fields; for example, an accented (foreign language) character. If you export as Windows CSV, those fields are encoded using the Windows-1252 code page. DOS encoding usually uses code page 437, which maps characters used in old pre-Windows PCs. If you export as one and then import with a tool that expects the other, most things will look fine but you'll get unexpected results if, for example, you know someone with an umlaut (or other foreign character) in their name.

source

9
  • 3
    The mac options also should convert the windows CR/LF to the mac CR only standard.
    – Lamar B
    Feb 2, 2012 at 18:39
  • 4
    What about Csv comma Delimited?
    – Daniel Beck
    Feb 2, 2012 at 18:49
  • 3
    @Raystafarian OK, so that's the default format. But regarding CSV means comma separated: If it only were so easy. Localized variants of the CSV format use semicolons as delimiters.
    – Daniel Beck
    Feb 2, 2012 at 19:20
  • 1
    @LamarB: The CR only format applies only to MacOS before OSX. OSX is Unix-based, and uses LF as the line terminator. Does Excel ise the old MacOS format? Feb 2, 2012 at 19:54
  • 1
    @KeithThompson CSV (Macintosh) saves with CR as the new-line character, whereas CSV (Comma delimited) and CSV (DOS) both use CR/LF. I found this out by saving a small spreadsheet and opening it in Notepad++ with "Show All Characters" enabled.
    – Ky -
    Feb 3, 2012 at 5:44
0

What's the difference between - CSV (MS-Dos), CSV (Macintosh), CSV (comma delimited) file types in excel

Using Excel 16.8 2023 MS365:

With file (macOS 12.6 file 5.41 Darwin, et al.)

file *.csv
MSDOS.csv: CSV text
MAC.csv: ASCII text, with CR line terminators
CSV.csv: CSV text

Note: file provides a guess and is able to give variable answers depending on the length of the csv format.

The presence of carriage return (CR) and line feed (LR) bytes are the big format difference and much more clarifying when introspected.

0x0d CR 0x0a LF

 for file in *.csv; do echo -n $file" "; xxd $file | tail -2; done
CSV.csv 000012b0: 0d0a 2c2c 2c0d 0a2c 2c2c 0d0a 2c2c 2c0d  ..,,,..,,,..,,,.
000012c0: 0a2c 2c2c                                .,,,
MAC.csv 00001260: 0d2c 2c2c 0d2c 2c2c 0d2c 2c2c 0d2c 2c2c  .,,,.,,,.,,,.,,,
00001270: 0d2c 2c2c                                .,,,
MSDOS.csv 000012b0: 0d0a 2c2c 2c0d 0a2c 2c2c 0d0a 2c2c 2c0d  ..,,,..,,,..,,,.
000012c0: 0a2c 2c2c                                .,,,

0x2c is the separator in the above CSV's. It is important to observe that the final row DOES NOT receive a terminal 0x0D 0x0A (CSV), 0x0d (MAC) or 0x0D 0x0A (MSDOS). This can become a surprise as I have witnessed Excel tacking on an additional trailing 0x0D 0x0A which could break processing logic if CR and/or LF indicates is used by code to continue parsing.

00000530: 2c-- --34 3045 4330 30-- ---- --2c 3138  ,--40EC00----,18
00000540: 390d 0a                                  9..

Note: '--' are redactions as this is from a real Excel file.

I suspect such output comes from the scripted generation of CSV files within MS productivity tools like VB.

When empty, all are empty

stat -f "%z %N" *EMPTY.csv   
0 CSV_EMPTY.csv
0 MAC_EMPTY.csv
0 MSDOS_EMPTY.csv

(macOS 12.6 stat Brown, et al.)

 for file in *.csv; do echo $file" "; xxd $file | tail -2; done
CSV.csv 
000012b0: 0d0a 2c2c 2c0d 0a2c 2c2c 0d0a 2c2c 2c0d  ..,,,..,,,..,,,.
000012c0: 0a2c 2c2c                                .,,,
MAC.csv
00001260: 0d2c 2c2c 0d2c 2c2c 0d2c 2c2c 0d2c 2c2c  .,,,.,,,.,,,.,,,
00001270: 0d2c 2c2c                                .,,,
MSDOS.csv
000012b0: 0d0a 2c2c 2c0d 0a2c 2c2c 0d0a 2c2c 2c0d  ..,,,..,,,..,,,.
000012c0: 0a2c 2c2c                                .,,,

Answered elsewhere here on this Stack Exchange question are the character encoding differences.

But Excel provides a fourth method of saving a 'CSV' which brings us to "CSV UTF-8"

file UTF8_EMPTY.csv 
UTF8_EMPTY.csv: Unicode text, UTF-8 text, with no line terminators
file UTF8.csv      
UTF8.csv: Unicode text, UTF-8 (with BOM) text, with CRLF line terminators

Here, file is much more verbose and specific. The file bytes appear as:

xxd UTF8_EMPTY.csv 
00000000: efbb bf                                  ...
xxd UTF8.csv      
00000000: efbb bf31 2c32 2c33 0d0a 342c 352c 36    ...1,2,3..4,5,6

So we can see the first three bytes (byte order marker) are good. And we can see that UTF-8 encoded CSV files use CR LF termination which are also not applied to the last record.

Finally, there are more places to go regarding the differences. As noted, the character encoding, the actual separators and how affected by localization and the quoting of values that match the separator come to mind.

I felt it was useful eleven years later to provide this cautionary tale for those hoping to use 'CSV' for programmatic ingestion for human originated CSV files for data interchange. I noticed this Stack Exchange question lacked a precise file format answer for such an ambition.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .