2
$\begingroup$

Here's an example CSV file (Example 1):

"Text","”","False","False","False","False","0","1","24","0","9","450","450"

and the code to import it (Code 1):

Import[filepath, "CSV"]
(* Outputs: {{"Text", "", "False", "False", "False", "False", 0, 1, 24, 0, 9, 450, 450}} *)

Here's another file with different delimiters (Example 2):

"Text"|"”"|"False"|"False"

And here's the code to import it (from the answer here) (Code 2):

Import[filepath, "Table", "FieldSeparators" -> "|"]
(* Outputs: {{"Text", "â", "False", "False"}} *)

Either method does not seem to be correctly importing single or double quotation marks. In the example, the second value is being incorrectly imported. Weirdly enough, the following CSV file—which is just generated by copying the second value from the example—does work.

"”","”","”","”"

With different delimiters, this does not work again. What could be causing this unexpected behaviour? I tried the default CSV reader in Python and it doesn't seem to have any issues parsing the file.


Question Update

Based on MarcoB's comment, I realized that different file encodings are giving different results. Here are the following outputs of the example CSV files with different file encodings:

Example 1, ANSI:

{{"Text", "", "False", "False", "False", "False", 0, 1, 24, 0, 9, 450, 450}}

Example 1, UTF-8:

{{"Text", "\[CloseCurlyDoubleQuote]", "False", "False", "False", 
  "False", 0, 1, 24, 0, 9, 450, 450}}

Example 2, ANSI:

{{"Text", "", "False", "False"}}

Example 2, UTF-8:

{{"Text", "â", "False", "False"}}

In summary, the only CSV file which seems to be imported correctly is the Example 1 (comma separated values, no custom delimiters), encoded in UTF-8. All other cases do not seem to work. Python is able to import all the example cases without any problems. How do I make it work for all cases? I want to be able to just specify the delimiter and let Mathematica do the rest, like in Python.

$\endgroup$
  • $\begingroup$ Although I am not sure that I understand the problem you have, I don't think I can reproduce any problems on my end. I copied your string into a text file, then imported it using your Import command, and the result was {{"Text", "\[CloseCurlyDoubleQuote]", "False", "False", "False", "False", 0, 1, 24, 0, 9, 450, 450}} . That seems fine to me. What were you expecting? I am on Win10-64 and MMA 12. $\endgroup$ – MarcoB Dec 10 '19 at 20:08
  • $\begingroup$ @MarcoB How weird! It works now for "," delimiters, it was just showing up as some unknown blank character previously. However, the "|" delimiter still does not work (using the code above): "Text"|"”"|"False"|"False" $\endgroup$ – WeavingBird1917 Dec 11 '19 at 5:38
  • $\begingroup$ It does not work depending on the file encoding. Please see the updated the question. $\endgroup$ – WeavingBird1917 Dec 11 '19 at 20:42
  • $\begingroup$ When producing the output for your example, are you giving explict CharacterEncoding options or do you let Import find out the encoding itself? I wouldn't have much hope that Import always gets this right automatically, if it even tries to (and I'm not sure whether it does or not)... $\endgroup$ – Albert Retey Dec 11 '19 at 22:36
  • $\begingroup$ @Albert Retey I did not specify the CharacterEncoding. ANSI appears to be a valid character encoding on the help page reference.wolfram.com/language/guide/CharacterOperations.html, but I do not have it listed under $CharacterEncodings. Is this normal? I don't know which one to choose. $\endgroup$ – WeavingBird1917 Dec 11 '19 at 22:47
2
$\begingroup$

Based on Albert Retey's comment, I was able to get it to work by specifying the CharacterEncoding. I checked the file encoding using Windows' Notepad. I used the following code for the case with the pipe delimiters:

(* ANSI file *)
Import[filepath, "Table", "FieldSeparators" -> "|", CharacterEncoding -> "WindowsANSI"];
(* UTF-8 file *)
Import[filepath, "Table", "FieldSeparators" -> "|", CharacterEncoding -> "UTF8"];

Weirdly enough, "ANSI" is listed as a valid character encoding on the help page, but it doesn't exist under $CharacterEncodings. On my version of Mathematica, it is named WindowsANSI instead.

For comma separated delimiters with the normal import method (code 1), the file encoding only needs to be specified with ANSI files, so I presume that Mathematica imports it as UTF-8. However, with custom delimiters, the file encoding has to be specified, so I'm not sure what character encoding Mathematica uses by default in that case.


Update: As GenericAccountName recommended in the comment below, we can use the documentation for Table (.dat) (under the general category "Import/Export format") to find out the default CharacterEncoding for importing and exporting, located under Options. Apparently the default CharacterEncoding used when importing is "ISOLatin1", but note that it is "UTF-8" when exporting.

As of now, looking up the documentation is probably the best method, as it appears that programmatically searching up Options for Import/Export is not implemented yet, based on this question: Programmatic access to Export options for filetype

|improve this answer|||||
$\endgroup$
  • $\begingroup$ You could check the documentation to see what the default CharacterEncoding is. For "Table" it is ISOLatin1, and for "CSV" it is "UTF-8", falling back to ISOLatin1 if there are invalid UTF8 byte sequences. $\endgroup$ – GenericAccountName Dec 27 '19 at 16:26
  • $\begingroup$ Thank you, I was able to find it here: reference.wolfram.com/language/ref/format/Table.html under Options > Import Options > CharacterEncoding. It's useful as we can see now that the default import CharacterEncoding is ISOLatin1, but the default CharacterEncoding for exporting is UTF-8. $\endgroup$ – WeavingBird1917 Dec 28 '19 at 11:38
  • $\begingroup$ This is for historic reasons at this point.. I think in the future it will be best to update Table to match CSV/TSV (UTF8ISOLatin1) $\endgroup$ – GenericAccountName Dec 30 '19 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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