JSON missing columns where the first value for a labelled column is empty #864

New Issue

2017-10-30T09:00:23Z

disleyland commented

2017-10-30 09:00:23 +00:00

(Migrated from github.com)

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 commented

2017-11-30 15:18:12 +00:00

(Migrated from github.com)

I want the same problem

vjau commented

2017-12-14 14:16:13 +00:00

(Migrated from github.com)

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

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

paishin commented

2018-01-16 00:50:00 +00:00

(Migrated from github.com)

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

reviewher commented

2018-01-16 01:20:16 +00:00

(Migrated from github.com)

@paishin can you share a file?

paishin commented

2018-01-17 09:51:19 +00:00

(Migrated from github.com)

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': '+22334455', 'Phone 2': 'NULL', 'Mobile 1': '+35799887766', 'Mobile 2': 'NULL', 'Fax 1': 'NULL', 'Fax 2': 'NULL', Email: 'yianni@kostis.com', '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': '+22334455', 'Phone 2': 'NULL', 'Mobile 1': '+35799887766', 'Mobile 2': 'NULL', 'Fax 1': 'NULL', 'Fax 2': 'NULL', Email: 'yianni@kostis.com', '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' } ]

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](https://github.com/SheetJS/js-xlsx/files/1638404/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': '+22334455', 'Phone 2': 'NULL', 'Mobile 1': '+35799887766', 'Mobile 2': 'NULL', 'Fax 1': 'NULL', 'Fax 2': 'NULL', Email: 'yianni@kostis.com', '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](https://github.com/SheetJS/js-xlsx/files/1638408/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': '+22334455', 'Phone 2': 'NULL', 'Mobile 1': '+35799887766', 'Mobile 2': 'NULL', 'Fax 1': 'NULL', 'Fax 2': 'NULL', Email: 'yianni@kostis.com', '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 commented

2018-01-17 12:23:53 +00:00

(Migrated from github.com)

@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.

@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 commented

2018-01-17 16:14:34 +00:00

(Migrated from github.com)

@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;

@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`](https://docs.sheetjs.com/#json). Here's a small example: ```js 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`](https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L77): ```diff out.length = outi; + out.headers = hdr; return out; ```

👍 4

Sign in to join this conversation.