SemanticImport fails with CSV when last column is ALL empty

Question

(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 ?

Answer 1

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"];
{head, data} = Through@{First, Rest}@PadRight@data;
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);

Answer 2

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.