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
– Paxz, Commented Feb 12, 2019 at 10:26

user6811411 · Accepted Answer · 2019-02-12 10:48:18Z

3

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

answered Feb 12, 2019 at 10:48

user6811411

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

6 Comments

Ansgar Wiechers Over a year ago

Alternatively, if they wanted to remove specific columns and keep everything else they'd use Select-Object * -Exclude col1, col4.

ryall579 Over a year ago

Thanks for this. I have just tried to add the export on the end:

Import-Csv c:\temp\*.txt -Header (1..5|%{"Column$_"}) |Select-Object Column2,Column3,Column5 -SkipLast 1| Export-Csv -Path C:\Temp\test.txt -Delimiter "," -Encoding UTF8 -notypeinfo

But get the error: Cannot perform operation because the path resolved to more than one file. This command cannot operate on multiple files. It also create the test.txt as blank

user6811411 Over a year ago

Import-Csv won't accept multiple files as the error msg clearly states. You'll need to process files with a gci first. Do you really want to combine several input files in one output?

ryall579 Over a year ago

Im only importing one file and then trying to export the columns I need which is the odd thing

user6811411 Over a year ago

If there is only one file and you know the name, use that name. If the name could be changing but there is only one use gci .\*.txt |%{ import-csv $_.FullName -Header (1..5|%{"Column$_"})} |Select-Object Column2,Column3,Column5 -SkipLast 1

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related