6
$\begingroup$

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.

$\endgroup$
  • 3
    $\begingroup$ This issue is also described in the documentation of "CSV" in "Possible issues", 3rd example - as suggested there, you could set "Numeric"->False if you don't have any numeric inputs $\endgroup$ – Lukas Lang May 11 '18 at 9:06
  • 1
    $\begingroup$ The d-notation is used by Fortran if I'm not mistaken. $\endgroup$ – Szabolcs May 11 '18 at 10:08
  • 1
    $\begingroup$ ReadList can help if your data file is properly structured (rectangular table with consistent row structure l $\endgroup$ – LLlAMnYP May 11 '18 at 15:57
4
$\begingroup$

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

data

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}]]
$\endgroup$
  • $\begingroup$ Really nice solution! Thank you! If you wrap it up in a self-contained function I'll make it the accepted answer. (Ok, I think I'll make it the accepted answer regardless.) $\endgroup$ – dreeves May 13 '18 at 7:56
4
$\begingroup$

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.

$\endgroup$
  • 4
    $\begingroup$ And ToExpression won't actually parse notations like 123e4. Nor is it secure. And there's no alternative. The situation is completely unsatisfactory. Import is just not very well designed and can cause a lot of damage if you're unlucky: mathematica.stackexchange.com/q/55245/12 $\endgroup$ – Szabolcs May 11 '18 at 10:07

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.