Solved merge files in column output

September 20, 2011 at 11:55:20
Specs: Windows 7
Hi all,

I am trying to merge txt files (from which first column is equal in all) into one file:
1.txt 2.txt 3.txt
l1 a1 l1 b1 l1 c1
l2 a2 l2 b2 l2 c2
l3 a3 l3 b3 l3 c3

out.txt
l1 a1 b1 c1
l2 a2 b2 c2
l3 a3 b3 c3

The batch file I have scrambled together does extract the columns I want, but rather than that everything is in separate columns, all data is in one single column. I spend several hours on this forum, but I cannot manage it.

:: hmm.bat
@echo off > hmm.txt & setLocal enableDELAYedeXpansion
echo AAAAAAAAAAaaaaaaaaaa............
pushd %*

for /f "tokens=1* delims= " %%a in (a1.txt) do (
>> hmm.txt echo. %%a hm
)

for %%j in (*.txt) do (
echo. %%j yes? >> hmm.txt
for /f "tokens=2* delims= " %%a in (%%j) do (
>> hmm.txt echo. %%a
)
)
popd
:: End_Of_Batch

Does anyone have suggestions?


See More: merge files in column output

Report •


#1
September 22, 2011 at 02:55:54
✔ Best Answer
Here is an unrefined test script, it uses 2 temp files(x.txt and y.txt) and outputs to "z.txt". These three files are overwritten automatically, without warning, so be careful.

The input file names are also hard coded to "a.txt", "b.txt" and "c.txt".

It is set to mix 3 files only and information will be lost it the file contains any exclamation marks ("!").

@echo off
SetLocal EnableDelayedExpansion
rem temp files
type nul > x.txt
type nul > y.txt
type nul > z.txt



for %%a in (a b c) do find /n /v "" < %%a.txt >> x.txt
sort x.txt /o x.txt

set "regex=^\[[0-9]\]"
:loop
findstr /r "%regex%" x.txt >> y.txt
if not errorlevel 1 (
    set "regex=^\[[0-9]%regex:~3%
    goto loop
)

set cnt=0
set line=
for /f "delims=" %%a in (y.txt) do (
    set input=%%a
    set input=!input:*]=!
    set line=!line! !input!
    if "!cnt!"=="2" (
        >> z.txt echo !line:~1!
        set line=
    )
    set /a cnt=^(cnt + 1^) %% 3

)
pause


Report •

#2
September 22, 2011 at 04:39:36
Many thanks for your reply Judago. I will need some time to go through your suggestion, there is quite some stuff I am not yet so familiar with.
Eventually I need it to work on thousands of .txt files, rather than just three, as I used in my example. Per experiment, thousands of txt files are generated, which I then need to combine. If I get your example to work, I can try make this work for more than 3 files, while leaving the input txt files intact.
I will keep you informed about my progress.

Report •

#3
September 22, 2011 at 11:56:03
I have a lot of trouble to get the suggested code to work for (many) more than 3 input files.

I now have this working for three files, which could be expanded to many files:
::=============================================
@echo off > tral.txt & setLocal enableDELAYedeXpansion

set N=
for /f "tokens=1* delims= " %%a in (a1.txt) do (
set /a N+=1 & call :sub1 %%a & set A!N!=!C!
)

set N=
for /f "tokens=* delims= " %%a in (a1.txt) do (
set /a N+=1 & call :sub1 %%a & set B!N!=!C!
)

set N=
for /f "tokens=* delims= " %%a in (a2.txt) do (
set /a N+=1 & call :sub1 %%a & set C!N!=!C!
)

set N=
for /f "tokens=* delims= " %%a in (a3.txt) do (
set /a N+=1 & call :sub1 %%a & set D!N!=!C!
)

for /L %%a in (1 1 !N!) do (
>> tral.txt echo. !A%%a! !B%%a! !C%%a! !D%%a!
)
goto :eof

:sub1 set C to last token
:loop
if '%2' neq '' (
shift
goto :loop
)
set C=%1
goto :eof
::================================================

To extend this to many (thousands) files, I would need to repeat the bit

set N=
for /f "tokens=* delims= " %%a in (a*.txt) do (
set /a N+=1 & call :sub1 %%a & set x!N!=!C!
)

maaaaaaaany times. I have tried using a loop instead:

::=============================================
@echo off & setLocal enableDELAYedeXpansion
type nul > slow.txt

set N=
for /f "tokens=1* delims= " %%a in (a1.txt) do (
set /a N+=1 & call :sub1 %%a & set A!N!=!C!
)

for %%j in (*.txt) do (
set N=
for /f "tokens=* delims= " %%a in (%%j) do (
set /a N+=1 & call :sub1 %%a & set x!N!=!C!
)
)

for /L %%a in (1 1 !N!) do (
>> slow.txt echo. !A%%a! !x%%a!
)
goto :eof

:sub1 set C to last token
:loop
if '%2' neq '' (
shift
goto :loop
)
set C=%1
goto :eof
::=============================================

I end up with the first column (which all data files have in common), and the second column of the very last data file.
I do not know how to update the variable x in !x%%a! for each file, to get this printed in a separate column.

Alternatively, does anyone know if it is possible to echo data at the end of a chosen line in the output file?
I would then echo it to the end of the first line, which then would result in echoing all data in columns.
Using
set /P line1=< hmm.txt
and then
echo.%line1% %%a>>hmm.txt
does not result in echoing at the end of the first line, but rather at the end of the last line.

Anyone has a solution either way?


Report •

Related Solutions

#4
September 22, 2011 at 12:12:29
Random question: Where did you get that script? It's nothing like what was posted. At all.

How To Ask Questions The Smart Way


Report •

#5
September 22, 2011 at 12:22:58
Hi Razor2.3,

I tried using the suggested script, but I could not figure out, how to make that work for more than three input files.
The other attempts I have scrambled together from scripts elsewhere on this forum. I have searched on terms like "combine files" "append column" "echo at specific line" Trying to combine various solution so far has not been fruitful. What ever I have tried, keeps mostly putting all selected data in one single column.

I have also looked for a nice tutorial / book on batch scripting, do you maybe have suggestions?


Report •

#6
September 22, 2011 at 12:33:38
Replace this line:
for %%a in (a b c) do find /n /v "" < %%a.txt >> x.txt

With this:
for %%a in (<list of files here>) do find /n /v "" < %%a >> x.txt

How To Ask Questions The Smart Way


Report •

#7
September 22, 2011 at 12:59:51
Also:

    if "!cnt!"=="2" (

and

    set /a cnt=^(cnt + 1^) %% 3


Need to be increased to n -1 and n respectively.


Report •

#8
September 22, 2011 at 13:21:03
When using Judago's script on more than 3 files, the data ill all be read in, but the out put is still on 6 columns. And also not only the second column is in the output, but both the first and second. I was looking to get rid of the first column, since all the files have these in common:
1.txt 2.txt 3.txt
l1 a1 l1 b1 l1 c1
l2 a2 l2 b2 l2 c2
l3 a3 l3 b3 l3 c3

out.txt
l1 a1 b1 c1
l2 a2 b2 c2
l3 a3 b3 c3

The output from judago's is (z.txt):
l1 a1 l1 b1 l1 c1
l2 a2 l2 b2 l2 c2
l3 a3 l3 b3 l3 c3

To get around this, I have tried playing with this bit:
for /f "delims=" %%a in (y.txt) do (

for /f "token=* delims=" %%a in (y.txt) do ( [no change in output when using 3 input files]
and
for /f "token=1* delims=" %%a in (y.txt) do ( [no change in output when using 3 input files]
and
for /f "token=2* delims=" %%a in (y.txt) do ([empty output file]

I suppose this would require changes in the bit:
set input = @@a
set input = !input:*]=!
set input = !line! !input!
I suspect this changes with the number of input files, which may vary per experiment.


Report •

#9
September 23, 2011 at 06:37:09
I finally managed to make Judago's suggestion work for me.
I modified it a wee bit. With options found from other pages on this forum :)
It first looks how many files there are, and lists the filenames in a separate file "list.dat". Then using this "list.dat" as an input file, I do not have to worry if I have suddenly differently named files etc, or a different amount.

@echo off 
SetLocal EnableDelayedExpansion
for /f  %%a in ('dir/b *.txt') do (
	set /a count+=1
	set /a count1=count-1
)
echo total is !count!
echo !count1!

rem empty contents of files
type nul > x.txt
type nul > y.txt
type nul > z.txt
type nul > list.dat

setlocal
set first=y
(
	for /f %%g in ('dir/b/a-d *.txt') do (
		if defined first (
			set first=
			set/p=%%~ng <nul
		) else (
			set/p=%%~ng <nul
		)
	)
)>>list.dat

for /f %%j in (list.dat) do (
	echo. %%j	
	for %%a in (%%j) do find /n /v "" < %%a.txt >> x.txt
)
sort x.txt /o x.txt

set "regex=^\[[0-9]\]"
:loop
findstr /r "%regex%" x.txt >> y.txt
if not errorlevel 1 (
	set "regex=^\[[0-9]%regex:~3%
	goto loop
)
rem type x.txt

set cnt=
set line=
for /f "delims=" %%a in (y.txt) do (
	set input=%%a
	set input=!input:*]=!
	set line=!line! !input!
	if "!cnt!"=="!count1!" (
		>> z.txt echo !line:~1!
		set line=
	)
	rem echo %count%
	set /a cnt=^(cnt + 1^) %% !count!
)
rem type z.txt
type nul > zz.dat
for /F "delims=" %%i in (z.txt) do (
	set cnt=1
	set rrow=
	for %%j in (%%i) do (
		set /A cnt-=1
		if !cnt! equ 0 (set cnt=0 & set rrow=!rrow! %%j)
	)
	set cnt=2
	set row=
	for %%j in (%%i) do (
		set /A cnt-=1
		if !cnt! equ 0 (set cnt=2 & set row=!row! %%j)
	)
	set row=!row:~1!
	echo. !rrow! !row!>> zz.dat
)

del x.txt
del y.txt
pause

Many thanks for the help Judago and Razor2.3, slowly I'm learning more and more.

When learning java, I found a nice textbook to study out of, but so far I have been unable to track down something similar for teaching oneself batch scripting. In case anyone has any suggestions for book (or good website), I'm keen to know.


Report •

#10
October 19, 2011 at 02:08:55
Just a little note, this code works nice, but does not keep the columns in the same order as the input files. This happens during the sorting procedure.
So far I have not been able to sort this one out, I am thinking it is better to perhaps use another programming language. If the thread is still open by the time I have managed that, I'll post this solution.

Report •

#11
October 20, 2011 at 11:44:58
Scripting.Dictionary doesn't guarantee the order of returned keys. As such, I technically shouldn't offer this as a solution, but I'm lazy.

VBScript:

Const someDirPath = "."
Const outPath = "out.dat"

Set dic = CreateObject("Scripting.Dictionary")
Set fso = CreateObject("Scripting.FileSystemObject")

'Read data
For Each file In fso.GetFolder(someDirPath).Files
  If LCase(fso.GetExtensionName(file)) = "txt" Then
    With file.OpenAsTextStream
      Do Until .AtEndOfStream
        line = Split(Trim(.ReadLine), " ", 2)
        dic(line(0)) = dic(line(0)) & " " & line(1)
      Loop
    End With
  End If
Next 'file

'Dump results
Set out = fso.OpenTextFile(outPath, 2, True)
For Each d In dic.Keys
  out.WriteLine d & dic(d)
Next 'd

How To Ask Questions The Smart Way


Report •

Ask Question