Remove columns and last row of a CSV file using a PowerShell script

Question

I have a CSV file as below but id like column 1 and 4 removed and the last row removed and and the speech marks removed then to export a new file to a UNC path. I also want the script to run from a UNC path too if at all possible. Can the new filename be the same as the original with the addition of _new?

Current File (file.txt):

"col1","col2","col3","col4","col5"
"col1","col2","col3","col4","col5"
"col1","col2","col3","col4","col5"
"col1","col2","col3","col4","col5"
"","","","","","","","","","","","","1111.00","11",""

Expected end result (file_new.csv):

col2,col3,col5
col2,col3,col5
col2,col3,col5
col2,col3,col5

The PowerShell script I tried to use is:

Import-Csv C:\temp\*.txt -Delimiter "," |
    Select Column2,Column3,Column5 |
    Export-Csv C:\Temp\New.txt -Delimiter "," -Encoding UTF8 -NoTypeInfo

This sounds more like an order than a question... Please show us what you tried so far. Edit your question and offer a minimal reproducible example. We are not a coding service ;) Also your description of the script doesn't match the "expected end result".. (column 2 still exists and column 4 is delete?) — Paxz

Unknown Unknown · Accepted Answer · 2019-02-12T10:48:18

As your file has no headers (or they are named col1..5) you'll have to either supply them

> Import-Csv file.txt -Header (1..5|%{"Column$_"}) |Select-Object Column2,Column3,Column5 -SkipLast 1

Column2 Column3 Column5
------- ------- -------
col2    col3    col5
col2    col3    col5
col2    col3    col5
col2    col3    col5

Or use the proper names:

> Import-Csv file.txt |Select-Object Col2,Col3,Col5 -SkipLast 1

col2 col3 col5
---- ---- ----
col2 col3 col5
col2 col3 col5
col2 col3 col5

Remove columns and last row of a CSV file using a PowerShell script

1 Answers