How to make Import[...., "Table"] not convert strings to numbers and not convert numbers to strings?

Question

Bug introduced in 8 or earlier and persisting through 11.2.0

---

I have to to use r = Import["foo.txt", "Table", "FieldSeparators" -> {","}] since my data is Table which is comma separated and it contains a mixture of strings and numerical data. Here is an example of file, of two lines:

0,"a","0"

1,"c","xyz"

The problem is that

r = Import["foo.txt", "Table", "FieldSeparators" -> {","}]

will convert the field "0" to numeric 0 when imported. This causes me problems since that field can sometimes actually be a string (much longer than shown).

If I use

r = Import["foo.txt", "Table", "FieldSeparators" -> {","}, "Numeric" -> False]

Then all the fields are imported as String.

I looked and can't find an option to tell Import to please please just leave my data "is as" and just import it as it is on the file.

I could import everything as strings, and then convert those fields that I know should really be numerical (in this example, the fields in the first column), back to numerical after importing the data using a post-processing step, as last resource (using ToExpression applied to each field in each record which is meant to be numeric).

But thought to ask if there is an option I overlooked. For reference

 ref/format/Table

Version 11.1

Answer 1

Diagnosis

According to the Documentation, the "Table" format (as well as "CSV" and "TSV")

• Stores numerical and textual information, formatted as a table.

and for separating textual information from numerical in these data formats we have

• Import and Export option:
  "TextDelimiters" Automatic string or list of strings used to delimit non-numeric fields

Based on this I consider the following behavior as a bug:

ImportString["0000123,\"0000123\"", "CSV", "TextDelimiters" -> "\""]

{{123, 123}}

Below is what I would expect to get from Import according to the Docs and the common sense:

{{123, "0000123"}}

Tracing the evaluation shows that Import removes the substrings specified via "TextDelimiters" before converting the fields which match (an analog of) NumberString into numbers. The correct implementation would be to remove the delimiters after converting numeric fields into numbers.

The Documentation statement

• Double-quote characters delimiting text fields are not imported by default.

is in accord with my interpretation: with the option "TextDelimiters" -> "\"" the textual field above should be imported as "0000123" (i.e. without the double-quotes inside of the string).

Current behavior of "TextDelimiters" -> None is correct – it imports all non-NumberString fields "as is":

ImportString["0000123,\"0000123\", Sin[x] ,'another field'", "CSV", 
 "TextDelimiters" -> None]    

{{123, "\"0000123\"", " Sin[x] ", "'another field'"}}

ImportString["0000123,\"0000123\", Sin[x] ,'another field'", "CSV", 
 "TextDelimiters" -> None, "Numeric" -> False]

{{"0000123", "\"0000123\"", " Sin[x] ", "'another field'"}}

But the case of "TextDelimiters" -> " " demonstrates another bug:

ImportString["0000123,\"0000123\", Sin[x] ,'another field'", "CSV", 
 "TextDelimiters" -> " "]

{{"0000123,\"0000123\", Sin[x] ,'another field'"}}

Below is what I would expect to obtain instead:

{{123, "\"0000123\"", "Sin[x]", "'another field'"}} 

---

A workaround

A workaround is to use "TextDelimiters" -> None with postprocessing:

ImportString["0,\"a\",\"0\"
1,\"c\",\"xyz\"", "CSV", "TextDelimiters" -> None]

{{0, "\"a\"", "\"0\""}, {1, "\"c\"", "\"xyz\""}}

% /. field_String :> 
  StringReplace[field, StartOfString ~~ "\"" ~~ s___ ~~ "\"" ~~ EndOfString :> s]

{{0, "a", "0"}, {1, "c", "xyz"}}

The suggested approach works correctly in the general case:

ImportString["0000123,\"0000123\",Sin[x],'another field'", "CSV", 
  "TextDelimiters" -> None] /. 
 field_String :> 
  StringReplace[field, StartOfString ~~ "\"" ~~ s___ ~~ "\"" ~~ EndOfString :> s]

{{123, "0000123", "Sin[x]", "'another field'"}}

---

P.S. Another shortcoming of the CSV importer which may be considered as a bug both in "TextDelimiters" and "LineSeparators":

• Importing CSV with multiline text fields