Search code examples
powershellcsvexport-to-csv

How to make "Export-CSV" export quoting only texts?

I have an input file with the following data:

cod_uf;uf;nome_uf;pop_estimada
11;RO;Rondônia;1796460
12;AC;Acre;894470
13;AM;Amazonas;4207714

issuing the command Import-Csv ".\in.csv" -Delimiter ";" | Export-Csv ".\out.csv" -NoTypeInformation -Encoding utf8 yields:

"cod_uf","uf","nome_uf","pop_estimada"
"11","RO","Rondônia","1796460"
"12","AC","Acre","894470"
"13","AM","Amazonas","4207714"

which is great, but it would be better if it quoted only non-numeric fields, like so:

"cod_uf","uf","nome_uf","pop_estimada"
11,"RO","Rondônia",1796460
12,"AC","Acre",894470
13,"AM","Amazonas",4207714

I couldn't find any mention or example to produce the desired output in the documentation.

To quote non-numeric field is useful when using python's csv library, where one could set the reader to convert all non-quoted fields to type float.

Solution

  • You can try something like this:

    $csv = @'
cod_uf;uf;nome_uf;pop_estimada
11;RO;Rondônia;1796460
12;AC;Acre;894470
13;AM;Amazonas;4207714
'@ | ConvertFrom-Csv -Delimiter ';'

($csv | ConvertTo-Csv) -replace '"([0-9]+)"','$1' | Out-File .\out.csv