csv - Deleting/replacing characters delimited by commas

Question

I'm trying to delete by batch or vbs text delimited by commas (CSV) that are always in the same position. It would not affect the first line, only lines 2 onwards.

Example text from file:

Code,Batch,File #,Reg Hours,O/T,Cost Number,Rate,Earnings,Earnings,Memo Code,Memo Amount,Earnings Code,Earnings Amount,Hours Code,Hours Amount,Earnings Code,Earnings Amount,Adjust Code,Adjust Amount
ABC,123,3980    ,78.52,,12331,10.00,,,,,,,, 
ABC,123,4026    ,29.38,,12331,10.00,,,,,,,, 
ABC,123,5065    ,64.46,,12331,10.00,,,,,,,, 
ABC,123,5125    ,80.00, 0.54,12331,11.00,,,,,,,, 

I would like to end up with text:

Code,Batch,File #,Reg Hours,O/T,Cost Number,Rate,Earnings,Earnings,Memo Code,Memo Amount,Earnings Code,Earnings Amount,Hours Code,Hours Amount,Earnings Code,Earnings Amount,Adjust Code,Adjust Amount
ABC,123,3980    ,78.52,,12331,,,,,,,,, 
ABC,123,4026    ,29.38,,12331,,,,,,,,, 
ABC,123,5065    ,64.46,,12331,,,,,,,,, 
ABC,123,5125    ,80.00, 0.54,12331,,,,,,,,, 

The only difference is the Rate area. It is the 7th separated value from the left, or 9th from the right. The first line remains intact.

Is there a way for the batch/vbs to determine the comma separated value position, delete the value or replace it with 'nothing', and ignore the first line?

For this example, we can assume the file will always be named file.csv, and located in D:location - 'D:locationfile.csv'

Thank you!

Answer 1

REM <!-- language: lang-dos -->
@ECHO Off
SETLOCAL ENABLEDELAYEDEXPANSION
SET "sourcedir=U:sourcedir"
SET "destdir=U:destdir"
SET "filename1=%sourcedir%q46534752.txt"
SET "outfile=%destdir%outfile.txt"

:: Remove the output file

DEL "%outfile%" >NUL 2>nul

:: To reproduce the first line intact

FOR /f "usebackqdelims=" %%a IN ("%filename1%") DO >"%outfile%" ECHO %%a&GOTO hdrdone

:hdrdone

(
REM to process the header line, remove the "skip=1" from the "for...%%a" command
FOR /f "usebackqskip=1delims=" %%a IN ("%filename1%") DO (
 REM step 1 - replace all commas with "|," to separate separators
 SET "line=%%a"
 SET "line=!line:,=|,!"
 FOR /f "tokens=1-7*delims=|" %%A IN ("!line!") DO (
  SET "line=%%A%%B%%C%%D%%E%%F%%H"
  ECHO !line:^|=!
 )
)
)>>"%outfile%"

GOTO :EOF

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

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

Produces the file defined as %outfile%

Processing of the header line is an issue. The code as presented should do as you ask, but it seems illogical to retain the column name in the resultant file when the process is intended to remove that column. To process the header line also, delete the first for line and remove the skip=1 (which skips the first line) from the second.

The fundamental issue is that batch treats a string of delimiters as a single delimiter, so it's necessary to separate those delimiters. This is not possible against a metavariable, but can be done within a loop by transferring the metavariable into an ordinary environment variable (line) and performing the string-replace ceremony on that ordinary variable in delayed expansion mode.

So - replace each , with |,, then process the resultant string using | as a delimiter. Note that the metavariable is in a different case for the second for - one of the few occasions where cmd is case-sensitive. Reconstruct the string, omitting column 7 (%%G) and using the * token meaning the eighth token (%%H) receives the remainder-of-line after the highest explicitly-mentioned token number (7) and echo it after removing remaining | characters.

Note that it is normal policy to refuse code-requests on SO, and only respond in a manner to fix faulty code. In this case however, succeeding browsers may find this response to be the key to doing a similar task and hence refrain from posting unnecessarily. Also, I'm bored witless.