Insert a Column into a Batch File

August 6, 2009 at 05:27:37
Specs: Windows
Hi All,

I have a little problem...

I have the following two csv files:



...and would like to achieve the following result with a batch file:


I have been searching and testing for about a day now, but am having a problem achieving this. I know how to add it to the end of the file, not in the 4th column space as explained in the above example.



See More: Insert a Column into a Batch File

Report •

August 6, 2009 at 16:34:54
So, to simplify...

Basically, I need to take the value on each line in File2 and insert it in the empty column in the fourth position in File1 to create Output.csv.

The best I have been able to do is this...
@Echo Off > output.csv
SET %%c=%%a File2.csv
For /F "tokens=1-5 delims=," %%a in (File1.csv File2.csv) Do (
Echo %%a,%%b,%%c,,%%e>> Output.csv)
Which yields this resulting output.csv file:

As you can see, I am very new to this, so any assistance would be greatly appreciated.



Report •

August 7, 2009 at 12:04:53
OK, I can give you the batch you ned, but you have to be patient as now I am busy. If you can wait, in my next post you will find the script.

Report •

August 7, 2009 at 14:09:09
Hi Mick

@echo off
setlocal EnableDelayedExpansion
set Count1=0

for /f "tokens=1" %%a in (Data2.txt) do (
set /a Count1+=1
set Count2=0
for /f "tokens=1-4 delims=," %%b in (Data1.txt) do (
set /a Count2+=1
if !Count1! EQU !Count2! (
echo %%b,%%c,%%d,%%a,%%e

Report •

Related Solutions

August 7, 2009 at 14:44:42

Good but higly inefficient, as you read the whole second file for each line of the first one, i.e. assumed the files have 100 records 10000 reads.

The job can be done by reading the records four times i.e. 400 reads vs 1000 for files with 100 lines (with a little increment in write operations 300 vs. 100).

Anyway you give a solution while mine is in my mind only.

Report •

August 7, 2009 at 15:06:37

I agree it's inefficient, why not post your code.
I'd be very interested to see learn from your coding.


Report •

August 8, 2009 at 09:39:35
Hi dtech10,

first of all I hope you did not get my comment as offensive as I repute you one of the most skilled scripters in this Forum. I appreciate your solution as here the value is an effective answer while theory comes back.

About the efficiency, let me post a slightly modified version of your script:

@echo off & setlocal > output.csv

echo.DUMMY > Data1.tmp
type Data1.txt >> Data1.tmp

set cnt=1
for /F %%a in (Data2.txt) do call :READ1 %%a
del Data1.tmp
goto :EOF

  for /F "skip=%cnt% tokens=1-4 delims=," %%b in (Data1.tmp) do (
    echo %%b,%%c,%%d,%1,%%e
    set /A cnt+=1
    goto :EOF
  ) >> output.csv

:: End_Of_Batch

While yor original script performs N*N reads and N writes, this results in N*(N+1)/2 according to Gauss' law (i.e. assumed 100 lines 5050 vs. 10000 with just 100 writes)

Here what I worked out by myself

@echo off & setlocal EnableDelayedExpansion

type nul > output.tmp
type nul > output.csv

set row=A
set cnt=0
for /F %%a in (Data1.txt) do call :WRITE %%a
set row=B
set cnt=0
for /F %%a in (Data2.txt) do call :WRITE %%a

set cnt=0
for /F "tokens=2 delims=~" %%a in ('sort ^< output.tmp') do (
  set /A cnt^^=1
  if !cnt! equ 1 (
    set row=%%a
  ) else (
    set row=!row:,,=,%%a,!
) >> output.csv
del output.tmp
goto :EOF

  set /A cnt+=1
  set fcnt=%cnt%
  if %cnt% lss   10 set fcnt=0%fcnt%
  if %cnt% lss  100 set fcnt=0%fcnt%
  if %cnt% lss 1000 set fcnt=0%fcnt%
  echo.%fcnt%%row%~%*>> output.tmp
  goto :EOF

:: End_Of_Batch

The above code executes 4*N reads and 3*N writes handling however huger character streams and relaying on a sort phase whose impact is not easily valuable.

Both scripts performed fine but on short files your solution is indeed the best: complexity must always be avoided when unnecessary.

Last but not least I notice more and more posts fire up interest among the regulars but are ignored by the OP; this is quite frustrating.

Report •

August 8, 2009 at 13:57:18

Excellent piece of coding as usual , I was not offened by your constructive criticism because what you said was perfectly true.
I hold you and many many other on this site in high regard as excellent coders whatever computer language they use.
Long may it continue.


Report •

August 9, 2009 at 00:47:53
You guys sure nailed it. IVO, I'm sorry but I couldn't get yours to work for me. I'm sure it's great, it was just a little too complex for me to follow and adapt.

No matter though, as dtech10's script did the trick. Average file size only about 2000 line which completes in around 3 seconds, which is fine by me.

Thanks to both of you for all your help...



Report •

Ask Question