How to convert empty space cell's to null values

[kgrvamsi]

How can i keep the empty spaces with cells to match the header data.(is there a way to define empty space as null or none value when converted to json)

Example: header cells :14
Cells: 11(3 cells are empty space cells)

[DiegoZoracKy]

Can you post here the full config (header, columnToKey, etc.) you are passing in to the lib?

[DiegoZoracKy]

Hi @kgrvamsi,

Did you solve your issue? Can we close this?

[kgrvamsi]

@DiegoZoracKy i'm really sorry i didn't looked into this issue ...my bad but here is the config i use as part of the conversion.

return excelToJson({
        sourceFile: data.file,
        outputJSON: false,
      });

I'm expecting something like this

return excelToJson({
        sourceFile: data.file,
        outputJSON: false,
        blankSpaceToNull: true, \\ kind of add the empty value to a Null String value
      });

[DiegoZoracKy]

No problem @kgrvamsi. Would mind explaining your use case? Why do you need the property with a null value?

[kgrvamsi]

the excel sheet's i convert at times i see that even if there is a space it would result me with a undefined value and if there is no value in the cell that that value won't populate in the json array which is good and simplifies my work more on to defect for the right key(A,B,C,D....)

[miekademarco]

Thanks for the awesome library @DiegoZoracKy . I believe I have a similar use case to @kgrvamsi

Given the following spreadsheet

Current result:

{
  "11111": {
    "col1": "x",
    "col2": "x"
  },
  "22222": {
    "col2": "x",
    "col3": "x"
  }
}

Desired result:

{
  "11111": {
    "col1": "x",
    "col2": "x",
    "col3": null
  },
  "22222": {
    "col1": null,
    "col2": "x",
    "col3": "x"
  }
}

It would be nice to have an option to choose whether to return "property: null" or "no property" for empty cells.

The use case is for creating objects that need to have values or nulls for all the properties in order to be complete.

[DiegoZoracKy]

Hi @micdemarco and @kgrvamsi (again),
I get the idea and I believe that it will be good for us to move forward with it. We will need:

• New config option named sheetStubs to tell when those null properties should appear on the result data.

• A new condition capable of preserving those properties when sheetStubs config is set to true

The following line is where a new choice has to be made. The ".v" can be undefined however ".z" will have a value on these cases:

convert-excel-to-json/lib/convert-excel-to-json.js

Line 46 in a716b60

if (cell == '!ref' || sheet[cell].v === undefined) {

Tell me if some of you would like to work on this issue, so I can provide further guidance on it.

[miekademarco]

@DiegoZoracKy sure, I'll be happy to make the changes

[DiegoZoracKy]

Great! If you need more info on this specific case or just about the internals of this lib you can keep posting here and I'll help you.

[harsh07bharvada]

Hi @DiegoZoracKy !
Thanks for the awesome lib. Is this issue closed ? What new key do we have to add for enabling this ?
Had another issue though. Dummy rows are getting converted with all fields as blank string ? How to tackle this issue?

[miekademarco]

@harsh07bharvada some work was done in this pr #39
awaiting feedback from @DiegoZoracKy

[DiegoZoracKy]

Hi @micdemarco and @harsh07bharvada, I'm sorry for the delay. I'm really facing a huge lack of time currently. @micdemarco the PR is merged now and thanks for working on it!

@harsh07bharvada can you try to use this new version, see if that works well for your case and return here with a feedback?

[intermx-krishnan]

Hi @DiegoZoracKy i also facing same issue. My case is i want to insert excel data into database. in this case,
excelToJson({
source: fs.readFileSync(filePath),
header:{
rows: 1
},
columnToKey: {
A: 'type',
B: 'name',
C: 'company',
D: 'orgID',
E: 'isParent',
F: 'parentCompany',
G: 'primaryContact',
H: 'taxIdNumber',
I: 'phoneNumber',
J: 'tollFree'
}
})

Above configuration does not returns null values. But i need that null columns also. suggest me how to handle this.

[DiegoZoracKy]

Hi @intermx-krishnan, the README.md hasn't been updated yet with the new feature brought by @micdemarco and released on version 1.7.0. You just have to define sheetStubs on the config object passed in to excelToJson (right @micdemarco?) . You can check the example on the test file pushed on this PR:

https://github.com/DiegoZoracKy/convert-excel-to-json/pull/39/files

[miekademarco]

@intermx-krishnan @DiegoZoracKy

Yes, correct, i've added a pr to update the README