need windows script to remove a particular column from csv file

Question

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"

At first, clarify what you need -- if you need help with your PowerShell coding attempt, provide a minimal reproducible example (since "it is not working as expected" is not an error description, so please elaborate) and apply the related powershell tag. If you need a batch file for free, well, you are simply wrong here as this site is not a free scripting service; hence take the tour and consult How to Ask! In case you have tried both a batch file and a PowerShell approach, post one specific question with a minimal reproducible example per coding language! — aschipfl
– aschipfl, Commented May 6, 2019 at 11:17

rojo · Accepted Answer · 2019-05-06 12:08:08Z

3

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.

edited May 6, 2019 at 12:08

answered May 6, 2019 at 11:47

rojo

24.5k5 gold badges61 silver badges104 bronze badges

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

Comments

Compo · Accepted Answer · 2019-05-06 12:22:01Z

3

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

From the powershell prompt or script:

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

From cmd or a batch-file:

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

edited May 6, 2019 at 12:22

answered May 6, 2019 at 11:52

Compo

39k5 gold badges32 silver badges46 bronze badges

2 Comments

rojo Over a year ago

Alias for -NoTypeInformation is -NTI according to MSFT docs. Also, +1. I like your -ExcludeProperty solution better than my own answer.

Compo Over a year ago

I'm aware of the alias, @rojo, and whilst my shortening of NoTypeInformation is currently subject to future changes to the parameters, I prefer its use in my typing. I'm more than satisfied, should the OP require it, that they replace NoT with NTI as you have commented.

Magoo · Accepted Answer · 2019-05-06 14:08:08Z

@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.

Collectives™ on Stack Overflow

need windows script to remove a particular column from csv file

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related