CODEWITHSUNDEEP
powershellcsvbatch-filedelimiterwindows-scripting
Vicky
Vicky

Reputation: 349

need windows script to remove a particular column from csv file

I have a CSV file (format provided below) with delimiter set to , . As an output, we would like to remove column 4 ( Values ) from below CSV.

We need it in WINDOWS batch script only as there is no other scripting setup allowed on server. Server has powershell as well but we are not sure how to utilize powershell commands to serve below purpose.

CSV is as below:

ID, NAME, SR, Values, Status  
"1", "Raj", "123", "A,B,C,D,E", "False"    
"2", "Rahul", "456", "C", "False"

We tried this but, it is not working as expected :

(Get-Content .\testCSV.csv) | Foreach-Object { $_.split()[4] -join ' ' } | Out-File .\file.txt

Expected Outcome ( removed the Values column ) 

ID, NAME, SR, Status
"1", "Raj", "123", "False"
"2", "Rahul", "456" , "False"

Upvotes: 0

Views: 1316

Answers (3)

Magoo
Magoo

Reputation: 80203

@ECHO OFF
SETLOCAL
SET "sourcedir=U:\sourcedir"
SET "destdir=U:\destdir"
SET "filename1=%sourcedir%\q56003792.txt"
SET "outfile=%destdir%\outfile.txt"
(
FOR /f "usebackqdelims=" %%a IN ("%filename1%") DO CALL :drop4 %%a
)>"%outfile%"

GOTO :EOF

:drop4
:: all data to 'line'
SET "line=%*"
CALL ECHO %%line:*%1, %2, %3, %4, =%1, %2, %3, %%
GOTO :eof

You would need to change the settings of sourcedir and destdir to suit your circumstances.

I used a file named q56003792.txt containing your data for my testing.

Produces the file defined as %outfile%

The usebackq option is only required because I chose to add quotes around the source filename.

Since all of your data lines contain columns separated by , I'll suggest this pure-batch solution.

The preamble establishes directory and filenames to be used. These are customised for my system.

The main meat of the routine is the for/f which calls the subroutine drop4 supplying each entire text line verbatim. The for is enclosed in parentheses to redirect the entire routine output to the specified file.

The subroutine simply assigns the parameters to a standard environment variable (as batch-substringing can only be applied to normal variables).

The call echo line executes echo having substituted the supplied parameters %1..%4 so the string echoed will be the original line, with all characters up to [the first 4 parameters, separated by ,] replaced by [the first 3 parameters, separated by ,] - dropping column4.

Upvotes: 1

Compo
Compo

Reputation: 38718

If you use the built in CSV functionality of Import-CSV, you can exclude the property/column, using Select-Object's -ExcludeProperty.

From the prompt or script:

Import-CSV .\testCSV.csv | Select-Object * -ExcludeProperty Values | Export-CSV .\Modified.csv -NoTypeInformation

From or a :

PowerShell -NoP "IpCSV .\testCSV.csv|Select * -Exc Values|EpCSV .\Modified.csv -NoT"

Upvotes: 3

rojo
rojo

Reputation: 24476

Rather than attempting to hack and scrape your CSV as flat text, it's much easier to objectify it, then select the properties you want to keep.

test.csv:

ID, NAME, SR, Values, Status  
"1", "Raj", "123", "A,B,C,D,E", "False"    
"2", "Rahul", "456", "C", "False"

test.ps1:

ipcsv test.csv | select ID,NAME,SR,Values | export-csv test2.csv -NTI

result:

"ID","NAME","SR","Values"
"1","Raj","123","A,B,C,D,E"
"2","Rahul","456","C"

If your CSV source comes from the pipeline rather than from a file, use ConvertFrom-CSV instead.

Upvotes: 3

Related Questions