Issue with empty values in generated Excel.

[bjornsegrell]

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]

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