Remove empty lines in a CSV file with PowerShell

Question

I am trying to use powershell to export rest API to CSV with the following code

$DesiredProperties | Export-CSV -Path ".\Testrestapi.csv" -Encoding ASCII -NoTypeInformation

It outputs the following to CSV:

,
"105283","TickerID"
"2365512290","TickerID"
,
,
,
,
"US594918AH79","ISIN"
,
,
,
,
"US594918AH79","ISIN"
"2121687888","TickerID"
"US594918AH79","ISIN"
,

Desired result:

"105283","TickerID"
"2365512290","TickerID"
"US594918AH79","ISIN"
"US594918AH79","ISIN"
"2121687888","TickerID"
"US594918AH79","ISIN"

I tried using

(gc file.txt) | ? {$_.Trim() -ne ""} | Set-Content file.txt

but it seems it is not working for CSV files. Could someone please show me which command I should be using in this script to remove empty lines ", , , ,"

What is $DesiredProperties ? Is it content from a file ?

Fourat
– Fourat

2019-04-29 08:18:09 +00:00
Commented Apr 29, 2019 at 8:18 — Fourat
– Fourat, Commented Apr 29, 2019 at 8:18
? {$_.Trim() -ne ""} -> ? {$_.Trim(',')}

Ansgar Wiechers
– Ansgar Wiechers

2019-04-29 08:19:19 +00:00
Commented Apr 29, 2019 at 8:19 — Ansgar Wiechers
– Ansgar Wiechers, Commented Apr 29, 2019 at 8:19
Yes. In this case tickercode, ticker_code_type

eckomax87
– eckomax87

2019-04-29 08:19:21 +00:00
Commented Apr 29, 2019 at 8:19 — eckomax87
– eckomax87, Commented Apr 29, 2019 at 8:19

Tim van Lint · Accepted Answer · 2019-04-29 09:32:46Z

3

You could use Where-Object { $_.PSObject.Properties.Value -ne '' } to clean up the $DesiredProperties object directly.

Example:

Input data: "C:\Temp\Testrestapi.csv" (I use your CSV here, but you should take your export object from the REST API directly:

,
"105283","TickerID"
"2365512290","TickerID"
,
,
,
,
"US594918AH79","ISIN"
,
,
,
,
"US594918AH79","ISIN"
"2121687888","TickerID"
"US594918AH79","ISIN"
,

Sample code:

Import-Csv -Path "C:\Temp\Testrestapi.csv" -Header 'col1','col2' | Where-Object { $_.PSObject.Properties.Value -ne '' } | Export-Csv -Path "C:\Temp\Testrestapi_clean.csv" -NoTypeInformation

Output data: "C:\Temp\Testrestapi_clean.csv":

"col1","col2"
"105283","TickerID"
"2365512290","TickerID"
"US594918AH79","ISIN"
"US594918AH79","ISIN"
"2121687888","TickerID"
"US594918AH79","ISIN"

answered Apr 29, 2019 at 9:32

Tim van Lint

1546 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove empty lines in a CSV file with PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related