When Converting excel to csv for empty cells quotes not coming

Question

I'm trying to import excel file to csv format through power shell using this command

import-excel c:\users\xxxx\TestFile.xlsx | export-csv c:\users\xxx\CSVFile.csv -NoTypeInformation  -Encoding UTF8

It is working fine but issue is when the column value is empty for that double quotes are not coming.

Sample out put:-

"98456","Millan, Don K","Millan","Don","K",,"MD","SPECIALISTS","UROLOGY",,,,"0-99",,

Required output:-

"98456","Millan, Don K","Millan","Don","K","","MD","SPECIALISTS","UROLOGY","","","","0-99","",""

My excel input data is like below one

So is there any way to get quotes for all the empty cell when converting to csv in powershell.

Answer 1

Your "missing" values imply the source data is $null for those properties:

PS> [pscustomobject] @{
    "col1"  = "val1"
    "col2" = $null
    #        ^^^^^ null value
    "col3" = "val3"
} | convertto-csv

which outputs

"col1","col2","col3"
"val1",,"val3"
#     ^^ no content

If you want an empty quoted string in the output, you need to put that value in the data:

PS> [pscustomobject] @{
    "col1" = "val1"
    "col2" = ""
    #        ^^ empty string
    "col3" = "val3"
} | convertto-csv

which then outputs:

"col1","col2","col3"
"val1","","val3"
#      ^^ empty string

So, after you've imported your data with import-excel iterate over all the results and change any $null values to "" before you call export-csv:

$data = import-excel c:\users\xxxx\TestFile.xlsx

foreach( $item in $data }
{
   foreach( $property in $item.psobject.properties )
   {
       if( $null -eq $property.Value )
       {
           $property.Value = ""
       }
   }
}

$data | export-csv c:\users\xxx\CSVFile.csv -NoTypeInformation  -Encoding UTF8

The result might not look any different if you display the property values on-screen before and after changing them, because neither $null nor "" display any printable characters, but when you export them you'll see the desired output.