CSV import with delimiters not working for certain characters (curly quotation marks)

Question

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.

Answer 1

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