Solved Concatenate multiple csv files

September 27, 2011 at 06:10:37
Specs: Windows XP
I have a number of csv files, and I want to concatenate the data in all the csv files in to one. The copy command is working fine for me but I need to remove the header that comes in the output csv file after each csv file is concatenated.

I am using the below code in a batch file:-

@echo off> output.csv
copy /a 1_*.csv Output.csv

Please help


See More: Concatenate multiple csv files

Report •


#1
September 27, 2011 at 14:52:22
Not too sure what you mean by but I need to remove the header that comes in the output csv file after each csv file is concatenated. headers are usually the first line of a csv file so would appear before the concatenated file.

With that in mind please test this:

@echo off>output.csv
cls
setlocal 

for /f "tokens=*" %%1 in ('dir /b /a-d 1_*.csv') do (
    for /f "skip=1 tokens=*" %%A in (%%1) do (
        echo %%A>>output.csv
  )
)


Please come back & tell us if your problem is resolved.


Report •

#2
September 28, 2011 at 04:19:29
Hey, this did not work. I used the code below:-

@echo off>output.csv
cls
setlocal

for /f "tokens=*" %%1 in ('Data_*.csv') do (
for /f "skip=1 tokens=*" %%A in (%%1) do (
echo %%A>>output.csv
)
)

Here, the ouput file is Output.csv and I have input files as Data_*.csv in the same directory. Also, what I want is to concatenate the data from all Data_*.csv into output.csv.

As per the copy method in my previous post, I am able to concatenate the data in output.csv but the headers from all Data_*.csv is coming as rows in the concatenated output.csv. I want just one header at the beginning of the file and only data from all csv files should be appended in the output.csv.
I hope I am clear this time. Please advise.


Report •

#3
September 28, 2011 at 23:29:20
✔ Best Answer
Okay. You have not posted an example of any input file so it's hit-and-miss as to what will or will not work. Test this, no allowance is made for filenames containing spaces.:

@echo off
cls
setlocal enabledelayedexpansion

if exist output.csv del output.csv

dir /a-d /b Data_*.csv>dirfiles.txt

for /f "tokens=*" %%1 in (dirfiles.txt) do (
    set /p header=<%%1
    echo !header!>output.csv
)

for /f "tokens=*" %%1 in (dirfiles.txt) do (
        more +1 %%1>>output.csv
   )

del dirfiles.txt


Please come back & tell us if your problem is resolved.


Report •

Related Solutions

#4
September 29, 2011 at 03:23:39
hey thanks a lot..dis is wrkng...
can you please explain it briefly.
i am not really sure of how the for loop is working here.

Report •

#5
September 29, 2011 at 13:48:46

for /f "tokens=*" %%1 in (dirfiles.txt) do (
set /p header=<%%1
echo !header!>output.csv
)

This loop simply selects the first line (the header line) from a file, the name of which was extracted to Dirfiles.txt by the Dir command in the command line above it and writes the line to the Output file. On completion of this loop the Output file contains only the header line.

for /f "tokens=*" %%1 in (dirfiles.txt) do (
more +1 %%1>>output.csv
)

This For loop reads the content of each file, the names of which are listed in Dirfiles.txt and uses the More command to append each line to the Output file skipping the first line of each input file (the header). On completion of the loop the Output file contains the header line plus data lines from all files.

For details of the Set and More commands please enter Set /? or More /? at the command prompt.

Good luck and thank you for coming back to report your success.


Please come back & tell us if your problem is resolved.


Report •

#6
September 30, 2011 at 02:20:32
Thanku so much :)

Report •

Ask Question