How can I export null value to excel and later read the null back into memory? #1196

New Issue

2018-07-27T09:16:29Z

flight9 commented

2018-07-27 09:16:29 +00:00

(Migrated from github.com)

Some fields in our db are like this:
{
"asset_id" : null,
}
We want to do a roundtrip to export->modify->import fields, but when export to excel, it becomes blank, like:

asset_id	other fields
(blank)	some value

Later when importing from this excel, the asset_id is totally missing. When comparing, the new imported value of asset_id(actually missing) is not equal to the old value(null) and it'll generate an update. We think this is an unconsistency and an unnecessary modification.

Does anyone know a proper way to export and import a null value to avoid the above situation?

- Some fields in our db are like this: { "asset_id" : null, } - We want to do a roundtrip to export->modify->import fields, but when export to excel, it becomes blank, like: asset_id | other fields ------- | ------- (blank) | some value - Later when importing from this excel, the `asset_id` is totally missing. When comparing, the new imported value of `asset_id`(actually missing) is not equal to the old value(null) and it'll generate an update. We think this is an unconsistency and an unnecessary modification. Does anyone know a proper way to export and import a null value to avoid the above situation?

SheetJSDev commented

2018-07-27 09:39:56 +00:00

(Migrated from github.com)

Yes, there is currently some loss of information around null/undefined.

In theory, the lossless way to do this is to write the error #NULL! for values that are explicitly marked as null. In the cell model, it is represented as {t:"e", v:0x00}. The json_to_sheet function would have to write the null value and the sheet_to_json function would have to interpret the error as a null value. Would that be a better approach?

Yes, there is currently some loss of information around null/undefined. In theory, the lossless way to do this is to write the error `#NULL!` for values that are explicitly marked as null. In the cell model, it is represented as `{t:"e", v:0x00}`. The `json_to_sheet` function would have to write the null value and the `sheet_to_json` function would have to interpret the error as a null value. Would that be a better approach?

flight9 commented

2018-07-30 04:07:28 +00:00

(Migrated from github.com)

@SheetJSDev thanks for quick response.

How can I export {t:"e", v:0x00} in json_to_sheet and correctly recognize them in sheet_to_json ?
And for array type fields in db, like:
{
photos: / not-set / [ null ]
}
How to do that?

@SheetJSDev thanks for quick response. - How can I export `{t:"e", v:0x00}` in `json_to_sheet` and correctly recognize them in `sheet_to_json `? - And for array type fields in db, like: { photos: [ ] / not-set / [ null ] } How to do that?

EvanCarroll commented

2019-03-01 17:52:24 +00:00

(Migrated from github.com)

If the question here is how to get sheet_to_json to not use the string "NULL" but instead the null value, I have the same question.

If the question here is how to get sheet_to_json to not use the string `"NULL"` but instead the `null` value, I have the same question.

SheetJSDev commented

2021-09-30 07:25:34 +00:00

(Migrated from github.com)

An option nullError will be added to aoa_to_sheet / json_to_sheet / sheet_add_aoa / sheet_add_json to control interpretation of null values when reading data. sheet_to_json will always write #NULL! cells as null

An option `nullError` will be added to `aoa_to_sheet` / `json_to_sheet` / `sheet_add_aoa` / `sheet_add_json` to control interpretation of `null` values when reading data. `sheet_to_json` will always write `#NULL!` cells as `null`

Sign in to join this conversation.