Remove header while merging multiple .csv files using batch

Question

I have written the code to concatenate sample files into a single file minus the headers each file.

Input files:

File1:

[ Row : Header ],,,,,,,,,
ContractNum,ProgramNum,CustomerNum,TierNum,StartDate,EndDate,DateCreated,CreatedBy,DateUpdated,UpdatedBy
00032116,21238,60304PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14
00032116,21238,81790PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14

File 2:

[ Row : Header ],,,,,,,,,
ContractNum,ProgramNum,CustomerNum,TierNum,StartDate,EndDate,DateCreated,CreatedBy,DateUpdated,UpdatedBy
00024067,15562,9942PRMI,1,2014-09-16,2016-12-31,gintgUser,gintgUser,2016-02-21 05:59:43,2016-02-21 05:59:43

Expected Output:

[ Row : Header ],,,,,,,,,
ContractNum,ProgramNum,CustomerNum,TierNum,StartDate,EndDate,DateCreated,CreatedBy,DateUpdated,UpdatedBy
00024067,15562,9942PRMI,1,2014-09-16,2016-12-31,gintgUser,gintgUser,2016-02-21 05:59:43,2016-02-21 05:59:43
00032116,21238,60304PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14
00032116,21238,81790PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14

Actual Output:

[ Row : Header ],,,,,,,,,
ContractNum,ProgramNum,CustomerNum,TierNum,StartDate,EndDate,DateCreated,CreatedBy,DateUpdated,UpdatedBy
00024067,15562,9942PRMI,1,2014-09-16,2016-12-31,gintgUser,gintgUser,2016-02-21 05:59:43,2016-02-21 05:59:43
00032116,21238,60304PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14
[ Row : Header ],,,,,,,,,
ContractNum,ProgramNum,CustomerNum,TierNum,StartDate,EndDate,DateCreated,CreatedBy,DateUpdated,UpdatedBy
00032116,21238,81790PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14

Please find below code used for this operation:

@echo off
break>Combined.csv
cls
setlocal enabledelayedexpansion

if exist C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\Combined.csv del C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\Combined.csv

dir /a-d /b C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\ContractEligibility_*.csv>C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\dirfiles.txt

cd C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\

for /f "tokens=*" %%A in (C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\dirfiles.txt) do (
    set /p header=<%%A
    if "!header!" neq "" (
        (echo(!header!)>Combined.csv
        goto :break_for
    )

)
:break_for

for /f "tokens=*" %%A in (C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\dirfiles.txt) do (
        more +1 %%A>>Combined.csv
   )

del dirfiles.txt
}

Can someone please help me resolve this issue. I am a neophyte to batch scripting and unable to debug this issue.

Answer 1

A couple points about this question:

• This question is an exact duplicate of Windows Batch file execution error
• At that question there are 4 answers, one of which is mine.
• In my answer I asked you to post a small section of your data files, but you never replied.
• This is a copy of my answer at that question after I slightly modified it in order to insert the key point of your problem: the headers contain TWO lines:

EDIT: I modified the code accordingly to the new specifications posted in a comment: there are three lines of headers in each file, but just the 3rd must be included in the output.

@echo off
setlocal enabledelayedexpansion

cls

REM cd C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\

set "header3="
(for %%A in (*.csv) do (

   if not defined header3 (
      (set /p "header1=" & set /p "header2=" & set /p "header3=") <%%A
      echo !header3!
   )

   more +3 %%A

)) > Combined.txt

• And this is the generated Combined.txt file when this program run with your data above:

.

[ Row : Header ],,,,,,,,,
ContractNum,ProgramNum,CustomerNum,TierNum,StartDate,EndDate,DateCreated,CreatedBy,DateUpdated,UpdatedBy
00032116,21238,60304PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14
00032116,21238,81790PRMI,3,2014-05-02,2017-09-30,Administrator,Administrator,2016-02-29 10:46:14,2016-02-29 10:46:14
00024067,15562,9942PRMI,1,2014-09-16,2016-12-31,gintgUser,gintgUser,2016-02-21 05:59:43,2016-02-21 05:59:43

As you can see, the output is the same you want.

EDIT: I can't test the modification because the posted input files does not contain the same data as the real files...

• You should follow up the questions you post and not post new questions with the exact same problem of a previous one.
• You should be clearer in the description of your problem and post an example data.

Answer 2

There is no need for an interim file that contains a list of CSV files, you can read and combine them by a standard for loop and a nested for /F loop, using its skip option to get rid of the headers (assuming the header is always a single line). The initial header can be taken from another for/for /F loop construct that is broken upon its first iteration:

> "C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\Combined.csv" (
    for %%F in ("C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\ContractEligibility_*.csv") do (
        for /F "usebackq eol=| delims=" %%L in ("%%~F") do (
            echo(%%L
            goto :LEAVE
        )
    )
)
:LEAVE
>> "C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\Combined.csv" (
    for %%F in ("C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\ContractEligibility_*.csv") do (
        for /F "usebackq skip=1 eol=| delims=" %%L in ("%%~F") do (
            echo(%%L
        )
    )
)

---

If you need a specific sort order of the CSV files, you need another for /F loop instead of the standard for loop that parses the output of a dir /B command to do that job. The following example takes a two-line header, then it sorts the files from oldest to newest modification dates:

> "C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\Combined.csv" (
    set "FLAG="
    for %%F in ("C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\ContractEligibility_*.csv") do (
        for /F "usebackq eol=| delims=" %%L in ("%%~F") do (
            echo(%%L
            if defined FLAG goto :LEAVE
            set "FLAG=#"
        )
    )
)
:LEAVE
>> "C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\Combined.csv" (
    for /F "eol=| delims=" %%F in ('
        dir /B /A:-D /O:D /T:W "C:\Users\kartikeya.avasthi\Desktop\Batch_Scripts\ContractEligibility_*.csv"
    ') do (
        for /F "usebackq skip=2 eol=| delims=" %%L in ("%%F") do (
            echo(%%L
        )
    )
)

Answer 3

If you felt like installing awk - one of the handiest programs around from Unix/Linux - your task would become very simple. It is available for Windows from here.

Then you could just use:

awk  'NR<3 || FNR>2'  *.csv

To explain the command, you need to know that NR is the Number of the Record (i.e. the line number) and it starts at one for the first record/line of the first file and then increments with each record, so it will be less than 3 for just the first two records of just the very first file. FNR on the other hand, is the File Number of Record which is the same, but it resets to one as each new file is opened, so it will be less than 2 for the first two records of every file.

So, in summary, the command says... "Print any line if it is one of the very first two lines of all the input files, or if it is past line 2 of any of the files."

Note that you may need to replace the single quotes with double quotes on Windows.

Note that if you were to download gawk, it will work just the same as awk for this example.