New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Empty cells in xlsx become the non-empty string "None" in the dictionary #6
Comments
Good idea @JoshuaCapel 👍 thanks for pointing this out 🙏 |
@JoshuaCapel thank you for raising this. I dusted off sheet2dict today for a new project and tried the latest version and experienced this. I had a un-merged a lot of cells and was shocked to see them full of "None". It's a real shame that openpxyl doesn't support merged cells... but then again, I have no idea how sheet2dict would handle them. @Pytlicek - I lost your contact information somehow, please ping me. In the meantime, let's talk about a solution. I need to re-set up my IDE/computer to push/pull correctly. Maybe we can add an if just to check to see if the result is None. I did not modify the sheet2dict source, I made allowances in my code.
def item(row, col):
if type(sheet.cell(row=row, column=col).value) is None:
return (sheet.cell(row=1, column=col).value, '')
if type(sheet.cell(row=row, column=col).value) is not None:
return (sheet.cell(row=1, column=col).value, str(sheet.cell(row=row, column=col).value)) Also, whatever we do, we should add some test cases to check for this (You taught me well @Pytlicek!) |
@bai-yi-bai do you have an example file? I want to look at this and better understand what is the case, what exactly we need and so on. Thanks |
I created a PR #9
def item(row, col):
if sheet.cell(row=row, column=col).value is None:
return (sheet.cell(row=1, column=col).value, '')
else:
return (sheet.cell(row=1, column=col).value, str(sheet.cell(row=row, column=col).value))
def test_parse_xlsx_sheet_items(worksheet):
ws = worksheet
ws.xlsx_to_dict(path="inventory.xlsx", select_sheet='SJ3')
ws_items = ws.sheet_items
assert "Taipei" in str(ws_items)
assert "Bratislava" not in str(ws_items)
assert "None:" in str(ws_items)
assert "'None'," not in str(ws_items) # Tests whether empty cells were converted to "" strings and not "None"
assert None in ws_items[0]
assert len(ws_items) > 1
assert len(ws_items) == 6
|
Thank you @bai-yi-bai 🙏 Merged and published on PyPI with version: sheet2dict 0.1.5 |
Thank you for writing this. I have recently moved from using csv files to excel files and this has made the transition easier.
One issue I have encountered is that empty cells are not being returned as a Falsey value like
None
, or the empty string''
.From what I have been able to understand, openpyxl reads the empty cell as
None
, and the following code in sheet2dict usesstr
to convert this into the string'None'
.sheet2dict/sheet2dict/main.py
Lines 30 to 34 in 93bf731
I have filtered my own dictionaries to turn
'None'
into''
but that will cause issues when any of my sheet really do contain the word None.I just did some quick testing, and it seems that the
csv.DictReader()
in the python standard library uses the empty string''
for missing values in the a csv file, so that might be a good thing to do for these cases.The text was updated successfully, but these errors were encountered: