Importing a string such as "3d456" from a CSV file causes trouble

Question

I have a CSV file I'm importing with Import and it consistently crashes Mathematica. It turns out the culprit is one particular value of an ID field that was something like "3d456" but with a bunch more numbers after the 456.

The problem becomes clear if you try to import that as CSV directly:

ImportString["3d456", "CSV"]

The output is a 3 followed by 456 zeros. In other words, it's treating that string as a number in scientific notation. (Same thing if the "d" is an "e", which would be the more common representation.)

This threw me for quite a loop. And Mathematica is doing this despite that ID being in double quotes in the actual CSV file, which seems wrong.

So I think the most general version of my question is: How do I ensure that fields in a CSV file that are in quotes get imported as strings?

Or other workarounds for not letting something like "3d456" parse as a 457-digit integer.

Answer 1

Maybe a little preprocessing followed by a little post-processing?

First, let's contrive some random but reproducible data with of the kind you working with.

SeedRandom[1];
  With[{m = 3, n = 5},
  data = RandomInteger[100, {m, n}];
  Module[{i, j},
    {i, j} = RandomChoice /@ {Range[m], Range[n]};
    data[[i, j]] = "3d456"; 
    data]]

{{80, 14, 0, 67, 3}, {65, 100, 23, 97, 68}, {74, 15, "3d4", 4, 100}}

Now let's pretend that we have read in a CSV file with the above data as a single string.

csvData = ExportString[data, "CSV"]

"80,14,0,67,3\n65,100,23,97,68\n74,15,'3d456',4,100\n"

which looks like

in a notebook. The preprocessing changes the internal double-quotes into single-quotes.

preData = StringReplace[csvData, {",\"" -> ",'", "\"," -> "',"}]

"80,14,0,67,3\n65,100,23,97,68\n74,15,'3d456',4,100\n"

Now we do the conversion to Mathematica data.

mmaData = ImportString[preData, "CSV"]

{{80, 14, 0, 67, 3}, {65, 100, 23, 97, 68}, {74, 15, "'3d456'", 4, 100}}

It's not quite right because of the single-quotes. The post-processing gets rid of them.

postData = Map[Switch[#, _String, StringTrim[#, "'"], _, #] &, mmaData, {2}]

{{80, 14, 0, 67, 3}, {65, 100, 23, 97, 68}, {74, 15, "3d456", 4, 100}}

postData == data

True

Update

The OP asked for a function that would implement the procedure described above. I am posting such a function, but without any discussion of file system functions used or why I made the choices that I made. This update would be better if such a discussion were included, but I simply do not have the time to write it.

csv::fail = "import from `1` failed";
csv::ok = "data imported from `1`";
getCSVData[] :=
  Module[{filePath, str, csvData},
    filePath =
      SystemDialogInput["FileOpen", {"*.csv", {"CSV Files" -> {"*.csv", ".CSV"}}}];
    If[filePath === $Canceled, Return[{}]];
    str = ReadString[filePath];
    If[str === $Failed, Message[csv::fail, filePath]; Return[{}]];
    csvData =
      ImportString[StringReplace[str, {",\"" -> ",'", "\"," -> "',"}], "CSV"];
    Map[Switch[#, _String, StringTrim[#, "'"], _, #] &, csvData, {2}]]

Answer 2

You can use Import[..., {"CSV", "RawData"}] to treat all fields as strings. Unfortunately that means all fields whether or not they're in quotes. So then you need to use ToExpression to parse specific fields into numbers.