search

dfinke / ImportExcel

PowerShell module to import/export Excel spreadsheets, without Excel
https://www.powershellgallery.com/packages/ImportExcel/
Apache License 2.0
2.42k stars 390 forks source link

Issue with empty values in generated Excel. #1544

Open bjornsegrell opened 7 months ago

bjornsegrell commented 7 months ago

I'm importing tab separated data via Import-Excel and piping it to Export-Excel. The created Excel file is then imported to Sql Server via OpenRowset. The problem is that the "empty" cells don't import in as NULL values, but rather as some sort of empty string.

If I open the same tab separated file with Excel and save as .xlsx it will import as NULL.

I guess it's technically not wrong for it to be "empty" strings. But kind of unexpected.

I have the same issue if I do "Invoke-Sqlcmd -OutputAs DataRows | Export-Excel" and try to import the thusly generated Excel

Any suggestion to on how to get around this?

scriptingstudio commented 7 months ago

Empty values will be converted to NULL after rendering by Excel app because Export-Excel generates just a declaration of xlsx.