CODEWITHSUNDEEP
powershellcsv
ryall579
ryall579

Reputation: 83

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

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

Upvotes: 0

Views: 614

Answers (1)

user6811411
user6811411

Reputation:

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

Upvotes: 3

Related Questions