# SemanticImport fails with CSV when last column is ALL empty

(MMA v11.1.1)

I have many empty fields in my CSV files and I couldn't understand why sometimes SemanticImport just failed to recognize the headers. I just find out that the problem is when the last column is ALL empty

Here is a minimal example:

(for the purpose i use SemanticImportString with a string, instead of SemanticImport with a file, but the results are the same)

SemanticImportString["a,b,c\n1,2,\n11,22,\n111,222,"]


whereas if just one element exists in the last column, all works fine:

SemanticImportString["a,b,c\n1,2,\n11,22,33\n111,222,"]


Trying to trick the process by specifying explicit delimiters like this produce an error:

SemanticImportString["a,b,c\n1,2,\n11,22,\n111,222,", Delimiters -> {",", ",\n"}]


The only workaround i found is adding an additionnal comma at the end of the lines:

SemanticImportString["a,b,c\n1,2,\n11,22,\n111,222," //
StringReplace[#, ",\n" -> ",,\n"] &]


In comparison Import (or ImportString) works fine:

ImportString["a,b,c\n1,2,\n11,22,\n111,222,"]


{{"a", "b", "c"}, {1, 2, ""}, {11, 22, ""}, {111, 222, ""}}

and Export (ExportString) of a table with an empty last column show that rows are just terminated with a comma, and that this format should be handled by mathematica:

ExportString[{{a, b, c}, {1, 2,}, {11, 22,}}, "CSV"] // InputForm


"a,b,c\n1,2,\n11,22,"

So, I would say it is a bug ...

Any other workaround than adding (programmatically or manually) an additional comma to all the problematic files ?

file = StringToStream["a,b,c\n1,2,\n11,22,\n111,222,"];
First@ImportString[#, "CSV"] & /@ ReadList[file, "String"]  // TableForm
Close[file];


edit: using SemanticImport on each line and making a dataset:

file = StringToStream["a,b,c\n1,2,\n11,22,\n111,222,"];
data = Normal@*SemanticImportString /@ ReadList[file, "String"];
Close[file];
Dataset[Association[Rule @@@ Transpose[{head, #}]] & /@ data]


if you don't like zero fillers do this:

{head, data} =
Through@{First, Rest}@(
PadRight[#, Length@data[[1]], "empty"] & /@ data);

• Thanks but at the end of my post I already gave a working example with ImportString, I know it works. Actually I do need the "Semantic" feature even if my minimal example do not show that, sorry if ithat was not clear. – SquareOne Jun 22 '17 at 17:04
• you might take this and run SemanticImportString on each line as well. – george2079 Jun 22 '17 at 17:37
• The problem is that If I run SemanticImportString on each line separately, it does not produce a Dataset of the whole data. – SquareOne Jun 22 '17 at 18:00
• oh i see, see edit – george2079 Jun 22 '17 at 18:49
• I just noticed this with some datasets we have here - the CSV files are written using some mythical python CSV library and each 'row' ends with a ,\n and not just \n. So I have a 'ghost column' without a label even. These Import OK but don't SemanticImport – flip Jul 6 '17 at 23:08

Here are two workarounds:

The first one is the one i gave already in the post and consists in adding an additional comma at the end of each line. This works but it probably should not and won't work when the problem is fixed:

file = StringToStream["a,b,c\n1,9:00,\n11,10:00,\n111,11:00,"];
Import[file, "Text"] // StringReplace[#, ",\n" -> ",,\n"] & // SemanticImportString
Close[file];


The second should be definitely more robust and consists in adding a given arbitrary text tag in the last column which will be explicitly recognized by SemanticImport as an empty field:

file = StringToStream["a,b,c\n1,9:00,\n11,10:00,\n111,11:00,"];
Import[file, "Text"] //
StringReplace[#, ("," ~~ EndOfString | "\n") -> ",missing\n"] & //
SemanticImportString[#,  MissingDataRules -> {"missing" -> Missing["Empty"]}] &
Close[file];


Both return:

where the b column data type was correctly recognized as a time object.