Parsing Data with quotes in CSV

[hantsy]

In our project, we need to parse a csv file, but the provided sample is a little wired, all header items and values are with a double quotes.

The following is a simple example to explain this problem I encountered.

"Date","Name",...
"25 Mar","Test",...

I created a @DataSchema data class and annotated the Date and Name with @ColumnName(...).

@DataSchema
data class MyData(
@ColumnName("Date") date:MonthDay? = null
//......
)

I have tried to use the following approaches to read the CSV file.

DataFrame
  .readDelim(
      reader = BufferedReader(InputStreamReader(ByteArrayInputStream(data))),
      format = DEFAULT.format.withQuote('"')
  )

or

DataFrame
    .readCSV(ByteArrayInputStream(data))

And got the exception like this.

Can not find column `Date` in DataFrame

[hantsy]

I found it is very wired, in fact the parsing is ok.

I have tried to use the following fragment to convert to a data class.

DataFrame
    .readCSV(ByteArrayInputStream(data))
    .convertTo<MyData>{}

The date field(annotated with a @ColumnName("Date")) in the converted data class are null.

I have tried add custom conversion like the following two approaches. But the column name Date can not be referred by name Date, I am sure the column name is Date in the original CSV, and I have explored the KSP generated classes, I can find something like this["Date"] code fragment in the generated files.

DataFrame
    .convert{ "Date"<String>()}.with{ MonthDay.parse(....)} // 1. this still caused exception `Can not find column `Date` in DataFrame`.
    .readCSV(ByteArrayInputStream(data))
    .convertTo<MyData>{
        parser{MonthDay.parse(....) } // 2. this parser is no effects, all the date column in the Data classes are null
     }

[koperagen]

It seems that either convertTo doesn't take this annotation into account (it should), or the exception message is misleading.
It should be fixed, but in the meantime i'd expect your workaround with .convert{ "Date"<String>()}.with{ MonthDay.parse(....)} to work...
To better understand the problem, can you print the schema after readCSV?
DataFrame.readCSV("...").schema().print()
I think it might become clearer why .convert{ "Date"<String>()}.with{ MonthDay.parse(....)} doesn't work.

[hantsy]

oh, print the schema like this... only the Date field looks like this ?"Date"(???), others display the name defined in @ColumnName.

?"Date": String
Room: Int
...

[koperagen]

Could it be that your original csv file has some invisible characters in the header? readCSV read everything as is, so it must come either from csv or a bug in csv parsing.. You can workaround by providing list of column names: DataFrame.readCSV(..., header = listOf("name", "date"))

[hantsy]

Could it be that your original csv file has some invisible characters in the header?

In the text editor, this csv file encoding is shown as UTF8 BOM, that means there is a \uFEFF specific char at the beginning of the content.

But I tried to set the charset attribute in readCSV, there is no UTF8 BOM option.

[hantsy]

Get it work by removing the heading BOM char, https://mkyong.com/java/java-how-to-add-and-remove-bom-from-utf-8-file/#remove-bom-from-a-utf-8-file
Thanks @koperagen