How do I merge two csv files side by side with batch script

electr0n2 · 28 May 2015

Hi,

how can I merge two csv files (A and B) side by side with batch script ?:

Code:

A:

a b c 
d e f

Code:

B:

h i j k 
l m n o
p q r s

and the desired output should be C:

Code:

C:

a b c h i j k
d e f l m n o
(blank)p q r s

Pyprohly · 29 May 2015

Broke a sweat writing this one.

Here's your batch script, Electr0n2. Simply pass both CSV files as parameters.

Merge_two_csv_files_side_by_side.bat

Code:

:: Takes two CSV files and combines them "side-by-side". 

@echo off
if "%~2"=="" exit /b 1& if not "%~3"=="" exit /b 1

::
set outfile=".\%~n1_%~n2.csv"
set outfile_csv_delimiter=","
::

goto :main

:file_line_counter FileName VariableName
setlocal
	for /f "delims=" %%I in (' find /v /c "" ^< "%~1" ') do (
		set VAR=%%I
	)
endlocal & set %2=%VAR%
goto :eof

:SUB Counter CsvFileName
setlocal
	if "%~1" GTR "0" ( set skip="skip=%1") else ( set skip="")
	for /f "tokens=1,* %SKIP:"=% delims=]" %%I in (' find /v /n "" ^< "%~2" ') do (
		for %%K in (%%J) do (
			set/p=%%K <NUL >>%TEMPFILE%
		)
	goto :break
	)
	:break
endlocal
goto :eof

:r_trim_once Char
setlocal EnableDelayedExpansion
	call :get_string_length "%~1"
	for /f "delims=" %%I in ('type "%OUTFILE:"=%"') do (
		set line=%%I
		if "!LINE:~-%STRING_LENGTH%!"=="%~1" set line=!LINE:~0,-%STRING_LENGTH%!
		echo.!LINE!>>%TEMPFILE%
	)
	copy %TEMPFILE% "%OUTFILE:"=%" >NUL
	del %TEMPFILE%
endlocal
goto :eof

:get_string_length String
setlocal
	set VAR=%~1
	set "len="
	:loop_1133
	if defined VAR (
		set VAR=%VAR:~1%
		set /a len+=1
		goto :loop_1133
	)
endlocal & set string_length=%LEN%
goto :eof

:remove_numeric_chars VariableName String
setlocal EnableDelayedExpansion
	set VAR=%2
	for /l %%I in (0,1,9) do ( set VAR=!VAR:%%I=!)
endlocal & set "%1=%VAR%"
goto :eof

:main

if exist "%OUTFILE:"=%" del "%OUTFILE:"=%"

set tempfile="%TEMP%\tmp%RANDOM%_%~nx0.txt"

call :file_line_counter "%~1" LEN_1
call :file_line_counter "%~2" LEN_2
if "%LEN_1%" GTR "%LEN_2%" ( set /a len=LEN_1) else ( set /a len=LEN_2)
set "len_1="& set "len_2="

call :remove_numeric_chars outfile_csv_delimiter "%OUTFILE_CSV_DELIMITER:"=%"

for /l %%C in (0,1,%LEN%) do (
	call :SUB %%C "%~1"
	call :SUB %%C "%~2"
	if exist %TEMPFILE% (
		for /f "usebackq delims=" %%F in (%TEMPFILE%) do (
			for %%I in (%%F) do (
				set/p=%%I%OUTFILE_CSV_DELIMITER:"=%<NUL >>"%OUTFILE:"=%"
			)
		echo.>>"%OUTFILE:"=%"
		del %TEMPFILE%
		)
	)
)

call :r_trim_once %OUTFILE_CSV_DELIMITER%

electr0n2 · 10 Jun 2015

Pyprohly thanks I also came up with a script which can also bind two csv files side by side but I want to adjust it in order to combine incremented files such as file1.csv with ff1.csv , file2.csv with ff2.csv and so on therefore:

Code:

@echo off

for /L %%i in (1,1,2) do (

 set f1=File%i%.csv
 set f2=ff%i%.csv
 set "sep=;"  % tab %

 <%f1% (
   for /f "delims=" %%a in (%f2%) do (
      setlocal enabledelayedexpansion
       set /p line=
       echo(%%a!sep!!line!
      endlocal
   )
 ) > combmerged%i%.csv


pause
goto :eof)

the thing is that I get a message that the syntax of the command is not right but I cannot figure out what is the error, any suggestion please?

Pyprohly · 11 Jun 2015

electr0n2 said:

I also came up with a script which can also bind two csv files side by side

I’m sorry to say, but your script does not match what you communicate to the point of which I cannot fix or make suggestions for it.

The error message you receive occurs because you are attempting to make a redirection into a code block.

Code:

<%f1% ( […] )

And that's just one entry of the long list of problems with the batch script you've posted, Electr0n2.

electr0n2 · 11 Jun 2015

actually I have to apply this merge procedure to multiple couples of csv files this is the reason I put a for loop at the beggining which contains the 2nd loop which merges two csv files side by side (I tried that part and indeed works), if this is not a good scipt how can your script be adjusted to merge multiple couples of csv files , for example 100 pairs of file[i].csv and file[j].csv where i,j = [0,100]???

Pyprohly · 12 Jun 2015

electr0n2 said:

I want to [...] combine incremented files such as file1.csv with ff1.csv , file2.csv with ff2.csv and so on

electr0n2 said:

I have to apply this merge procedure to multiple couples of csv files [...] for example 100 pairs of file[i].csv and file[j].csv where i,j = [0,100]

I'm unsure of what exactly it is you are trying to do, and I can't quite comprehend the notation you've written in your example. But it sounds like you are trying to merge CSVs based on the names of files in some folder. And I can give a tip on doing just that...

electr0n2 said:

how can your script be adjusted to [...]

My script shouldn't be adjusted. It does what it needs to do.

The purpose of my script is to do the actual merging, nothing more. You must pass each pair of CSV files you want merged to this script. But I understand that you want to do this in an automated way.

Electr0n2, you should realise that all batch files are commands themselves.

With this in mind, another batch file can be made that simply passes each of the pairs of CSV files that need to be merged to the merge script for you.

Here's an example, I need to merge the files fooN.csv and barN.csv, where N represents some number of 1 to 3, so there are 6 files in total in my directory: foo1.csv, foo2.csv, foo3.csv, bar1.csv, bar2.csv and bar3.csv.

Using Merge_two_csv_files_side_by_side.bat as a command, the solution for to this problem would look something like,

Code:

@echo off
for /l %%A in (1,1,3) do (
	if exist "foo%%A.csv" if exist "bar%%A.csv" (
		Merge_two_csv_files_side_by_side.bat "foo%%A.csv" "bar%%A.csv"
	)
)

which will generate the files, foo1_bar1.csv, foo2_bar2.csv, etc., with its according merged content.

I believe your problem, Electr0n2, is similar to this example. I encourage you to create your own solution for your specific need.

margrave · 12 Jun 2015

Use the power of Unix/Linux:

Code:

paste file1.csv file2.csv | tr "\t" "," > newfile.csv

On Windows, you can have the Unix/Linux shell by installing Cygwin, a Unix/Linux environment that runs on Windows. Owned by Red Hat. Available for free. It does a SUPERB job of emulating Unix/Linux. It does not require a emulation package running in the background, so it operates with no system overhead. And it's free.

BTW, if you install Cygwin with the CR/LF preference (Windows mode), rather than the LF preference (Unix/Linux mode, which is my preference), then the code gets "complicated": :)

Code:

d2u file1.csv
d2u file2.csv
paste file1.csv file2.csv | u2d | tr "\t" "," > newfile.csv

Throw away .bat.
Instead, use the force!