Append multiple text files as columns

Microsoft Windows vista home premium w/s...
March 11, 2010 at 16:37:16
Specs: Windows Vista
I would really appreciate your help on trying to solve a problem i am currently facing. I am currently generating lots of data as tab limited text files. The text files have two columns: the first column is time and the second column is a varibable that is changing (as an example):
Time Mean
1 15
2 4
3 6

All these text files have have the same data arrangement format. All the text files within each group of subject data have a common start for the filename (as an example): abc.bmc50.txt ; abc.bmc100.txt and abc.bmc150.txt.

I want to try and merge all these text files into one tab limited text file. However I would like the final text file to have a format such that I have only one time column and the headers of the other columns to be changed to the part(or all) of the original filename (i.e. the bmc50, bmc100 etc) (so something like):

Time bmc50 bmc100 bmc150
1 15 19 20
2 4 10 2
3 4 11 6

I really dont know anything about programming..but this would really help me and I really appreciate anyones help or even partial solutions if possible. Please let me know if any of this is possible.
Thanking you kindly

See More: Append multiple text files as columns

Report •

March 11, 2010 at 16:45:28
My tab de-limted columns have not come out well. But I think it will hopefully make sense. Sorry.

Report •

March 11, 2010 at 17:26:16
Does each txt file have the same number of rows?

Helping others achieve escape felicity


Report •

March 11, 2010 at 23:39:12
Yes each text file has 501 rows, the first row is just a heading = time (1st column) and mean (2nd column). Thank you

Report •

Related Solutions

March 12, 2010 at 07:37:52
> My tab de-limted columns have not come out well.

Don't use TAB's, if you have the option to not use them in a file's format, get rid of them and replace them with whitespace, or another smartly thought of character.

Report •

March 12, 2010 at 11:25:55
Sorry if I have confused you. While trying to demonstrate the structure of my txt file on this forum I created a rough tab delimited format...but once I posted it it turned out a bit weird (as you can see above). The actual text files are fine and they are tab delimited. hope it makes sense now.

Report •

March 12, 2010 at 12:47:31
Well, half the problem may be on this forum, where double and/or leading spaces are replaced by nothing, but it will be no problem ...

M2 will come up with a solution (I am sure) and this will involve the FOR /F loop, where it is easier to use whitespaces, than to use tab's. So, if you have the option now ...

Report •

March 12, 2010 at 20:44:34
this might take a year to run, it iterates through every file in the directory 501 (or max) times.
the deal is, to do it the other way trades space for time: you would need to build a table of
501 "lines", then read each file and append each line to each of the table-lines. That might
be the way to go, though (also, simpler to write). i don't know how many files we're talking...
if it's too slow, let me know and i'll do the other way (array of 501 variables)

@echo off & setlocal enabledelayedexpansion
:: this picks up a TAB char. from any .txt file
for %%a in (*.txt) do (
for /f "tokens=*" %%b in (%%a) do (
set xx=%%b
if /i !xx:~0^,4! equ time goto :aa
:: note that the tab HAS to be 5th char. in string!
set tab=%xx:~4,1%
:set tab=
set /a max=501
:call :yy
set oline=Time
for /L %%i in (0 1 !max!) do (
set /a sk=%%i
for %%a in (*.txt) do (
set fil=%%a
:: this change should fix the col. heading problem
if %%i equ 0 (set oline=!oline!!tab!%%a) else (call :xx)
>> mainfile.out echo.!oline!
set oline=
goto :eof

set /a sk2=0
for /f "tokens=1-2" %%b in (!fil!) do (
if !sk2! equ !sk! (
set test=!oline:%tab%=!
if "!oline!" equ "" set oline=%%b
if "!test!" equ "" set oline=%%b
set oline=!oline!!tab!%%c
goto :eof
set /a sk2+=1
:: shortfile
set oline=!oline!!tab!
goto :eof

:: optional: this finds the length of longest file. set /a max=0
if they're all always
:: the same, you can just make this as a constant (ie: set max=501)
for %%a in (*.txt) do (
set fn=%%a
for /f %%b in ('find /v /c "" ^< %%a') do (
if %%b gtr !max! set max=%%b

Report •

March 13, 2010 at 01:35:35
Hello, Thank you for the quick response. As you have suggested it seems to take a little while to process - it could be that my computer is also a bit slow. Also the mainfile output seems to have all the characters bunched up together (example of the first few rows):


I was going through all my txt files (i.e. the original text files) earlier and noticed that not all of them have the word "time" at the top of the first column. Some of them are just blank on the first column. Which still means they have 501 rows for both column one and two and everything else is the same. But is it easy enough to write into the first row of the first column the word "Time" if its not present. Sorry if this has complicated things further.

Report •

March 13, 2010 at 13:29:42
hello: I had already coded the col.head as a constant:
set oline=Time
but i had a glitch in the code that didn't use it right. fixed in
script in response #7.
the other problem is, as Tvc pointed out, the delivery of TAB char. over the forum (ie: NOT).
i tried to express this in line 2:
@echo off>mainfile.out & setlocal enabledelayedexpansion
:: that's a tab (char 9) in the next line,
set tab=

i started to write code to pick up the tab out of one of the files, but went the other route which requires you to put it into the batch (using debug or:
EDIT /70 xxx.bat
then move cursor to: "set tab= ", the second space
ctrl-P then hit TAB key,then save the file.)
i edited the script in response #7 to pick up the tab from any file that has "time" at beg. of col.heading.

Report •

March 14, 2010 at 08:10:11
Hi I had a go at trying to enter the special character "TAB" i entered this using edit... so now that line looks:
set tab=%xx: ~4,1%

the output file gives a tab separated file which is awesome but now there are some additional characters - see below:

1xx: 4,15xx: 4,114xx: 4,113
2xx: 4,110xx: 4,18xx: 4,19
3xx: 4,18xx: 4,111xx: 4,120

after I changed it to set tab=% %

The characters all become bunched up again like before and
I loose the tab format.

Am I entering the tab at the worng place? Do I also need to delete some characters? Is there a way to speed up the process of merging as you have suggested.

The other question I have is - would it be easier/faster to do something in excel? any ideas?

Thank you

Report •

March 14, 2010 at 08:11:34
Also even if I use set tab=%: %
characters are all bunched up in the output

Report •

March 14, 2010 at 11:39:47
sorry, things have gotten a bit confused. You were able to get the tab into the batch, but you put it at the wrong location (my fault, i didn't explain clearly enough.)
Try just cut and paste from response 7, don't make any changes, run it and see if it works.
Then, here's the other variation (you WILL need to set the tab char. in this one)
@echo off & setlocal enabledelayedexpansion
:: set the TAB in the following line
(set tab= )
set test=0
set out0=Time
for %%a in (*.txt) do (
set out0=!out0!!tab!%%a
set /a c=0
for /f "tokens=1-2" %%b in (%%a) do (
set /a c+=1
if !test! equ 0 set out!c!=%%b
set out!c!=out!c!!tab!%%c
set test=1
for /L %%i in (0 1 500) do >> mainfile.out echo out%%i
:: ---------- end script

it makes assumption of all files always 501 lines.

Report •

March 19, 2010 at 02:59:57
This uses txt files in a directory immediately below, containing these 3 files:


All that in the interest of simplicity. If this script works OK, the rest is pretty much plain vanilla housekeeping.


@echo off & setLocal EnableDELAYedeXpansion

pushd data

set head=Time
for /f "tokens=* delims= " %%a in ('dir/b/a-d/od *.txt') do (
for /f "tokens=2 delims=." %%i in ("%%a") do (
set head=!head!	%%i
> newfile echo.!head!

for /f "tokens=* delims= " %%a in ('dir/b/a-d/od *.txt') do (
for /f "skip=1 tokens=1-2" %%i in (%%a) do (
set R%%i=!R%%i!	%%j

for /L %%x in (1 1 500) do (
>> newfile echo.%%x !R%%x!

Helping others achieve escape felicity


Report •

Ask Question