CODEWITHSUNDEEP
windowsbatch-filecmd
Ted Gatiss
Ted Gatiss

Reputation: 35

Windows batch code to merge file and edit

I have a lot of csv file into a folder. The files are named like OPERATORS_*.csv where * is a variable.

I want, using a batch file, to merge all files into one, delete the first row of each file and add at the end of each row the *.

I have tried this code:

copy /b OPERATORS_*.csv OPERATORS_FULL.csv

This way is fine, but the first row of each file is printed and i lost the attribute in the filename.

Example:

OPERATORS_ACTIVITY1.csv

OPT;SALES;REDEMPTION
OPT1;12;75

OPERATORS_ACTIVITY2.csv

OPT;SALES;REDEMPTION
OPT2;22;64

and i want this:

OPERATORS_FULL.csv

OPT1;12;75;ACTIVITY1
OPT2;22;64;ACTIVITY2

Any suggestions?

Upvotes: 1

Views: 90

Answers (3)

Magoo
Magoo

Reputation: 79982

@echo off
setlocal
del operators_full.csv 2>nul >nul 
FOR %%f IN (operators_*.csv) DO for /f "usebackqdelims=" %%a in ("%%f") do echo %%a>operators_full.txt&goto body
:body
(
FOR %%f IN (operators_*.csv) DO FOR /f "tokens=1*delims=_" %%s IN ("%%~nf") DO for /f "skip=1usebackqdelims=" %%a in ("%%f") do echo %%a;%%t
)>>operators_full.txt

move operators_full.txt operators_full.csv

First, delete the output file if it exists, then start copying the file(s) to a .txt file but deliberately abort after the very first line.

then, for each file, tokenise on the _ in the name part of the file %%f copy every line,appending the post-_ part of the filename in %%t, skipping the first and append to the .txt file (note the position of the outer pair of parentheses - this syntax allows the output of the entire code block to be redirected)

Finally, move or rename the file.

Oh -- you don't want the header line? Omit the first for line.

Upvotes: 0

aschipfl
aschipfl

Reputation: 34899

Here is a different approach using redirection -- see all the explanatory rem remarks in the script:

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set "_INPUT=OPERATORS_*.csv"     & rem // (input files)
set "_OUTPUT=OPERATORS_FULL.csv" & rem // (output file)
set /A "_SKIP=1" & rem // (number of lines to skip for each input file)

rem // Redirect the whole output at once:
> "%_OUTPUT%" (
    rem // Iterate over all the input files:
    for %%F in ("%_INPUT%") do (
        rem // Store the current file name to get the attribute name later:
        set "NAME=%%~nF"
        rem // Exclude the output file from being processed:
        if /I not "%%~nxF"=="%_OUTPUT%" (
            rem // Determine the number of lines of the current input file:
            for /F %%E in ('^< "%%~F" find /C /V ""') do set /A "CNT=%%E"
            rem // Read current input file:
            < "%%~F" (
                setlocal EnableDelayedExpansion
                rem // Loop over every line:
                for /L %%E in (1,1,!CNT!) do (
                    rem // Read current line:
                    set "LINE=" & set /P LINE=""
                    rem // Return current line if it is not to be skipped:
                    if %%E GTR %_SKIP% echo(!LINE!;!NAME:*_=!
                )
                endlocal
            )
        )
    )
)

endlocal
exit /B

Upvotes: 0

zb226
zb226

Reputation: 10500

Try this (Update #2):

@ECHO OFF
SETLOCAL EnableDelayedExpansion

IF EXIST OPERATORS_FULL.csv DEL OPERATORS_FULL.csv
IF EXIST OPERATORS_FULL.tmp DEL OPERATORS_FULL.tmp

FOR %%A IN ( OPERATORS_*.csv ) DO (
    :: get attribute from filename
    SET "attr=%%A"
    SET "attr=!attr:OPERATORS_=!"
    SET "attr=!attr:.csv=!"
    :: get date suffix
    SET tmp=!attr:_= !
    FOR %%G IN ( !tmp! ) DO (
        SET date_=%%G
    )
    :: if we have a date (i.e. a numeric value)
    IF !date_! EQU +!date_! (
        :: ...remove date from attr with leading underscore
        CALL SET attr=%%attr:_!date_!=%%
    ) ELSE (
        :: ...else clear date variable
        SET date_=
    )
    :: dump CSVs, skipping each header line, adding the attribute from the filename
    FOR /F "skip=1 tokens=*" %%G IN ( %%A ) DO ECHO %%G;!attr!;!date_! >> OPERATORS_FULL.tmp
)

REN OPERATORS_FULL.tmp OPERATORS_FULL.csv

Upvotes: 1

Related Questions