Read values from comma delimited file and generate sql

[victorlui]

I have a comma delimited input file param.txt:
A,1
B,2
C,3
......

I would like to create a batch file that:
1. read every lines of param.txt
2. and generate the sql like below for each line

line1.sql:
select * from table1
where col1 = 'A'
and col2 = '1'
;

line2.sql:
select * from table1
where col1 = 'B'
and col2 = '2'
;

line3.sql:
select * from table1
where col1 = 'C'
and col2 = '3'
;

......

[foxidrive]

Do you want a separate file for each line?

[victorlui]

yes

[foxidrive]

Code: Select all

@echo off
setlocal enabledelayedexpansion
set "c=0"
for /f "tokens=1,2 delims=," %%a in (params.txt) do (
set /a c=c+1
>>"line-!c!.sql" echo select * from table1
>>"line-!c!.sql" echo where col1 = '%%a'
>>"line-!c!.sql" echo and col2 = '%%b'
>>"line-!c!.sql" echo ;
)

[victorlui]

The result is line-c+1.sql having the following content:

select * from table1
where col1 = 'A'
and col2 = '1'
;
select * from table1
where col1 = 'B'
and col2 = '2'
;
select * from table1
where col1 = 'C'
and col2 = '3'
;

can I have the 3 files instead?

# line1.sql
select * from table1
where col1 = 'A'
and col2 = '1'
;

# line2.sql
select * from table1
where col1 = 'B'
and col2 = '2'
;

# line3.sql
select * from table1
where col1 = 'C'
and col2 = '3'
;

[ShadowThief]

Code: Select all

@echo off
setlocal enabledelayedexpansion

set counter=0
for /f "tokens=1,2 delims=," %%A in (param.txt) do (
   set /a counter=!counter!+1
   echo select * from table1>>line!counter!.sql
   echo where col1 = '%%A'>>line!counter!.sql
   echo and col2 = '%%B'>>line!counter!.sql
   echo ;>>line!counter!.sql
)

EDIT: Oh look at me, putting extra spaces in output where they don't belong.

[foxidrive]

The result is line-c+1.sql having the following content:

can I have the 3 files instead?

I forgot /a in the set command. It's fixed - try the code in that post again.