CODEWITHSUNDEEP
csvbatch-filecmd
ryall579
ryall579

Reputation: 83

Remove columns from a CSV using CMD/Batch

I currently have a CSV I download from a vendor which has data in it I do not require before importing it into our system.

I need to removed some columns from the CSV using a batch file as we are on citrix we have not got powershell as an option.

There are no column headers and below is what the CSV looks like:

"9826XXXXXXXXXX217","60007834        ","    10.00","D","22/11/2018"
"9826XXXXXXXXXX324","60008504        ","    12.00","D","22/11/2018"
"9826XXXXXXXXXX414","60010605        ","    12.00","D","22/11/2018"
"9826XXXXXXXXXX013","60011385        ","    12.00","D","22/11/2018"

I need the output to be 2nd, 3rd, 5th column.

"60007834        ","    10.00","22/11/2018"
"60008504        ","    12.00","22/11/2018"
"60010605        ","    12.00","22/11/2018"
"60011385        ","    12.00","22/11/2018"

Upvotes: 0

Views: 6072

Answers (2)

user6811411
user6811411

Reputation:

In case there are possibly commas inside the fields,
use a call to a sub passing the quoted arguments:

:: Q:\Test\2018\11\23\SO_53444017.cmd
@Echo off
(for /f "delims=" %%A in (test.csv) do Call :Split %%A
) > test_New.csv
goto :Eof
:Split
Echo(%2,%3,%5

> type test_New.csv
"60007834        ","    10.00","22/11/2018"
"60008504        ","    12.00","22/11/2018"
"60010605        ","    12.00","22/11/2018"
"60011385        ","    12.00","22/11/2018"

Edit: Variant processing all *.csv files (appending _New to the name)

:: Q:\Test\2018\11\23\SO_53444017.cmd
@Echo off
For %%F in (*.csv) do ( 
    (for /f "delims=" %%A in (%%F) do Call :Split %%A
    ) > "X:\Path\%%~nF_New.csv"
)
goto :Eof
:Split
Echo(%2,%3,%5

Upvotes: 1

user7818749
user7818749

Reputation:

Not too difficult really.

from batch file, assuming file name is test.csv

(for /f "tokens=2,3,5 delims=," %%i in (test.csv) do echo %%i,%%j,%%k) > newcsv.csv

This will simply write values 2,3,5 to the new csv file..

To run it from cmdline instead, simply remove one of each %

(for /f "tokens=2,3,5 delims=," %i in (test.csv) do echo %i,%j,%k) > newcsv.csv

Please note, this assumes the data within your values do not contain , if they do, we need to make some changes.

Upvotes: 1

Related Questions