Sheetjs: JSON missing columns where the first value for a labelled column is empty

[disleyland]

Unlike the other output formats, when outputting to JSON and the excel column does not have a value in the first row after the row header, that column is not included in the JSON.

[devrodriguez]

I want the same problem

[vjau]

I have proposed a fix in this PR:
https://github.com/SheetJS/js-xlsx/pull/918

[paishin]

I'm on version 0.11.17 and still have this problem, has it been resolved?

[reviewher]

@paishin can you share a file?

[paishin]

Hey there @reviewher, I am sending you two files the first one works fine, the second one has one field 'ID Issue Country' empty and it does not work because its value is missing from the results.

This one works:
persons.single.xlsx

[ { 'Customer No.': '84613', 'First name': 'Yiannis', 'Last name': 'Kostis', Sex: 'M', 'Type of ID': 'PN', 'ID Number': '123456-789', 'ID Is Certified': 'FALSE', 'ID Expiration Date': '12/10/99', 'ID Issue Country': 'NULL', 'Birth Date': '4/5/46', 'Creation Date': '2/5/97', 'Life Cycle': 'AC', 'Phone 1': '[phone]', 'Phone 2': 'NULL', 'Mobile 1': '[phone]', 'Mobile 2': 'NULL', 'Fax 1': 'NULL', 'Fax 2': 'NULL', Email: '[email protected]', 'Address Line 1': 'Koukouroukou 32, Flat 1', 'Address Line 2': 'NULL', 'Zip Code': '2222', City: 'Nicosia', Country: 'Cyprus', 'Country ISO': 'CY', 'Last Balance': '1234.56', 'Questionnaire Submitted': 'FALSE', 'LRS Risk': 'NULL', 'Address has proof': 'TRUE', 'Nationality Country': 'NULL', 'Country of Birth': 'NULL', Income: '15000', 'Income Source': 'Salary', 'Income Status': 'Verified' } ]

This one fails:
persons.single (1).xlsx

[ { 'Customer No.': '84613', 'First name': 'Yiannis', 'Last name': 'Kostis', Sex: 'M', 'Type of ID': 'PN', 'ID Number': '123456-789', 'ID Is Certified': 'FALSE', 'ID Expiration Date': '12/10/99', 'Birth Date': '4/5/46', 'Creation Date': '2/5/97', 'Life Cycle': 'AC', 'Phone 1': '[phone]', 'Phone 2': 'NULL', 'Mobile 1': '[phone]', 'Mobile 2': 'NULL', 'Fax 1': 'NULL', 'Fax 2': 'NULL', Email: '[email protected]', 'Address Line 1': 'Koukouroukou 32, Flat 1', 'Address Line 2': 'NULL', 'Zip Code': '2222', City: 'Nicosia', Country: 'Cyprus', 'Country ISO': 'CY', 'Last Balance': '1234.56', 'Questionnaire Submitted': 'FALSE', 'LRS Risk': 'NULL', 'Address has proof': 'TRUE', 'Nationality Country': 'NULL', 'Country of Birth': 'NULL', Income: '15000', 'Income Source': 'Salary', 'Income Status': 'Verified' } ]

[vjau]

@paishin , sorry, my fix does not address data missing in first data row (second row of the file), but the whole column missing when first header row had no name (first row of the file).
I so inappropriately linked my fix to this issue. Feel free to re-open it.

[SheetJSDev]

@paishin you are describing a different situation. The second worksheet has no assigned value in cell I2, so by default the converter does not add a field for that column. If a subsequent row does have a value in that column, the value will be included. If you really want a default value, the option in sheet_to_json is defval.

Here's a small example:

var ws = XLSX.utils.aoa_to_sheet([
  ["a","b","c"],
  [1,,3], // <-- cell B2 is missing
  [2,4] // <-- cell C3 is missing, but B3 is present
]);
XLSX.utils.sheet_to_json(ws);
// [ { a: '1', c: '3' }, { a: '2', b: '4' } ]
XLSX.utils.sheet_to_json(ws, {defval:""});
// [ { a: '1', b: '', c: '3' }, { a: '2', b: '4', c: '' } ]

If you need to merely recover the original headers, we could theoretically attach it as a named property of the result. It would be a 1-line change near the end of sheet_to_json:

    out.length = outi;
+   out.headers = hdr;
    return out;