Reading data with Excel drops columns with no values.

[vortexkd]

When reading an excel with data like this:

some_col, my_empty_col, other_col
1,,"hi"

"my_empty_col" does not exist on the resulting Table.

For reference the Csv reader seems to work fine.

I think this has something to do with the way columns are added in XlsxReader, the createColumn is inside the cell != null check, so it never gets added?

if (cell != null) {
          if (column == null) {
            column = createColumn(headerNames.get(colNum), cell);  // looks like it runs only if cell != null
            columns.set(colNum, column);
            while (column.size() < rowNum - tableArea.startRow) {
              column.appendMissing();
            }
          }

Steps to reproduce:

Table csvTable = Table.read().usingOptions(
                XlsxReadOptions.builder(
                        "my_file.csv")
        );
csvTable.column("my_empty_column"); // works fine

Table excelTable = Table.read().usingOptions(
                XlsxReadOptions.builder(
                        "my_file.xlsx")
        );
excelTable.column("my_empty_column"); // throws error.

[lwhite1]

Hi vortexkd,

Is a column with no values useful to you?

If we read it, it would have to be as a StringColumn, which would likely lead to a bug report from another user who intended their empty column to be a column of numbers or dates.

See also #815, #812

[vortexkd]

My particular use case:
I'm reading the file, processing the data (adding a bunch of things) and outputting the file again.
And it seems really weird for a column to just magically disappear, especially if it isn't always empty and as a result only disappears sometimes.

I understand that typing the column would then become an issue - but it seems that converting the excel file to Csv and then reading it solves the missing column issue - so if dropping empty columns is the stance you want to take on it as a project, then the CSV reader is working strangely (because it keeps empty columns).

-> Either way, it seems like one could reasonably expect the behaviour to be the same if the file is a CSV or an Excel file.

Maybe this sort of thing should have a setting that can be passed into the XlsxReadOptions / CsvReadOptions?

I guess this is similar to #812 but for excel

Edit:
I understand the bug that this would lead to with a simplistic solution (eg, the first row of a column is empty and as a result a numerical column becomes a string column - and panic ensues)
Maybe in the event that columns are created of the wrong type, or are missing etc using the default strategy, the user could send in a collection of "column_name": type_enum to specify expected behaviour?

That would be an enhancement

[lujop]

I think that should be useful to have that configurable in readOptions.
To be able to solve this and #815 and #812 I think that something like this should work:

preserveColumnWithMissingData(String columnName, ColumnType, defaultValue)

[radiocold]

I have they same problem, this will be fixed??? I need don't remove column with data empty.

[lujop]

I will be happy to provide a PR if something like preserveColumnWithMissingData can be accepted.

[ArslanaWu]

Hi! We are a group of students ( Zhengxin, Jiashu, Zunyao, Wendi and me) who would like to contribute to open source projects. Can we work on this issue? We want to let user specify the type of empty column in advance, otherwise it will not be created as before.

[lwhite1]

Thank you, but I would rather not have to supervise student work. I have very limited time for this project.

…

On Sun, Apr 18, 2021 at 8:44 AM Arslana ***@***.***> wrote: Hi! We are a group of students ( Zhengxin <https://github.com/Carl-Rabbit>, Jiashu <https://github.com/wateryloo>, Zunyao <https://github.com/DanielMao1>, Wendi <https://github.com/LUUUAN> and me) who would like to contribute to open source projects. Can we work on this issue? We want to let user specify the type of empty column in advance, otherwise it will not be created as before. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#822 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2FPARJY3NHM3ZTSP7N333TJLH23ANCNFSM4OZB4E4A> .