merge many csv files into one csv file

Ame7755 0
Viewed 52

I have 15 CSV files that I must merge into one, I was trying to copy from the windows command but this joins it in a single column, what I am looking for is that each CSV is a column within the merged file.

D1.csv   D2.csv   D3.csv
11       21       31
12       22       32
13       23       33

then

Copy *.csv combined.csv

Result

11
12
13
21
22
23
31
32
33

desired result

11 21 31
12 22 32
13 23 33
batch-file
3 Answers

I am not 100% sure what your full requirements are, with header or without, but here are some ideas.

type *.csv>combined.tmp && ren combined.tmp combined.csv

Which will merge all the .csv files in the root of the folder to combined.csv

or to remove the headers from all other files in the medged file:

for %%i in (*.csv) do if not "%%~i" == "combined.csv more +1 "%%~i">>combined.csv
usert2Ow0 
@ECHO OFF
SETLOCAL enabledelayedexpansion
SET "sourcedir=c:\testso\sourcedir"
SET "destdir=c:\testso\destdir"
SET "outfile=%destdir%\outfile.txt"

:: remove variables starting #
FOR  /F "delims==" %%a In ('set # 2^>Nul') DO SET "%%a="

FOR /f "delims=" %%e IN (
 'dir /b /a-d "%sourcedir%\*.csv" '
 ) DO (
 rem one more file found
 set /a #line=10000
 for /f "usebackqdelims=" %%y in ("%sourcedir%\%%e") do (
  set /a #line+=1
  for %%o in (!#line!) do set "#data%%o=!#data%%o! %%y"
 )
)
(
 for /f "tokens=1*delims== " %%b in ('set #data') do echo %%c
)>"%outfile%"

GOTO :EOF

The first part of the code sets the directories to be used and the output filename. The values I've used suit my test system; change to suit yourself

Next, remove all variables that start # from the environment.

Then find all the .csv files in the source directory. Naturally, you could change the filemask * to suit.

With each filename found, start a counter. I used a high number so that the data will arrive in variables #data10001, etc.

Read each line of the current file in %%e. The entire line contents because of the delims= and the usebackq is required because the filename is quoted.

for each line, bump the line number and append the line contents to #datathelinenumber by first placing the current line number in %%o to take advantage of cmd's parsing methodology.

Finally, all of the data read from the files will be in #data10001, #data10002, so use set to list the data in order, and select the set output after the = and space.

Note that this requires that the files all have the same number of lines, and the lines all contain some data.

The sequence of the columns would be the same as the sequence of the files listed by the dir command.

usertRV80

The correct solution to this problem largely depends on both the total size and the size of the longest line of the result file. If the total file size is small, then Magoo's solution should work well. However, if the file is large, then such a solution would be slow...

If the result file is large, then this solution should be faster:

@echo off
setlocal EnableDelayedExpansion

del combined.out

for %%f in (*.csv) do (

   if not exist combined.out (
      copy %%f combined.out
   ) else (
      < %%f (
         for /F "delims=" %%a in (combined.out) do (
            set /P "line="
            echo %%a !line!
         )
      ) > combined.new
      del combined.out
      ren combined.new combined.out
   )

)
ren combined.out combined.csv

However, this solution can not be used if the size of the longest line is greater than 1023

Aacini0
Related