I have two csv files and they appear the same when I look at them in a text editor like notepad++. However, when I open them both in Excel (2003 at this moment) one of them shows double quotes in fields and the other does not. Does anyone know why this is the case?

Note: The columns are in different orders and have different number of them

File 1 shows " in excel

"1794", "FEMALE", "9", "", "CHRISTENSEN", "JAMES FAKE", "LUCIA", "8/28/2006", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "40890", "209417", "767-544-033", ""

File 2 does not show " in excel

"1794","CHRISTENSEN","JAMES FAKE","LUCIA","8/28/2006","9","FEMALE","","","","","","",""

What other offending variables should I look for? Does encoding matter?

  • 1
    not considering the order both looks the same for me, values are surrounded by " in both lines – Máté Juhász May 20 '16 at 14:08
  • 1
    The first file has spaces following the commas. – DavidPostill May 20 '16 at 14:31
  • I suspect encoding... – Tyson May 20 '16 at 14:32
  • 1
    According to RFC 4180, spaces outside quotes in a field are not allowed; however, the RFC also says that "Spaces are considered part of a field and should not be ignored." and "Implementors should 'be conservative in what you do, be liberal in what you accept from others' (RFC 793 [8]) when processing CSV files." – DavidPostill May 20 '16 at 14:34
  • 1
    In some CSV implementations, leading and trailing spaces and tabs are trimmed (ignored). Such trimming is forbidden by RFC 4180, which states "Spaces are considered part of a field and should not be ignored." – DavidPostill May 20 '16 at 14:35
up vote 1 down vote accepted

One of them shows double quotes in fields and the other does not

Does anyone know why this is the case?

The first file has spaces following the commas.

Remove the spaces to get the same behaviour with both files.

According to RFC 4180, spaces outside quotes in a field are not allowed; however, the RFC also says that "Spaces are considered part of a field and should not be ignored." and "Implementors should 'be conservative in what you do, be liberal in what you accept from others' (RFC 793) when processing CSV files.

1997, Ford, E350

is not same as

1997,Ford,E350

Source Comma-separated values

  • I believe that the quoted Wikipedia article is wrong. It was introduced in this change. I explain it in why it is wrong in this comment. Please ping me if I'm wrong. – nh2 Jun 1 '16 at 21:40

As the OP commented above, removing the spaces solves the problem but if you don't want to manipulate the file before import, simply mark both Comma and Space as delimiters and check the "Treat consecutive delimiters as one" checkbox in the Text Import Wizard.

enter image description here

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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