1

how to write a batch script which will take a file as an input, then it will perform a sql query on that file and give a file as an output.

input will be a textfile which has 4 query in it. Now a batch file is to be written which will take 1 query at a time and execute it and output will be stored in a file. So there will be 4 seperate output file for 4 query

wimh
  • 15,072
  • 6
  • 47
  • 98
sudeep
  • 93
  • 2
  • 6
  • 17
  • be specific, is this file in CSV format? Also please rephrase your question title - it's way too generic. – SpliFF Feb 04 '11 at 07:26
  • you have asked another question which has far more detail and is to the point. I suggest you simply close this one as too vague and concentrate on your other one which is a lot better: http://stackoverflow.com/questions/4895725/batch-file-which-will-take-a-text-file-as-an-input – paxdiablo Feb 04 '11 at 08:28
  • I added your reply to the answers also to the question above, and rephrased the title a bit. – wimh Feb 05 '11 at 10:06

3 Answers3

2

You don't specify which sql server you are using, in this example I will use firebird. If you use a different sql server, you have to use the correct sql commandline tool and syntax. firebird uses isql.exe.

Asuming I have the following text file "input.sql" containing 4 sql commands:

select * from CUSTOMER;
select * from DEPARTMENT;
select * from EMPLOYEE;
select * from SALES;

Then this batchfile will execute each command using isql.exe, and creates a seperate output file for each command:

@echo off

set sql_exe="C:\Program Files\Firebird\Firebird_2_5\bin\isql.exe"
set sql_options=-u sysdba -p masterkey
set sql_db="C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB"
set count=1

for /f "delims=" %%a in (input.sql) do (
  echo %%a > temp.sql
  call :processtemp_sql
)
goto :eof


:processtemp_sql
%sql_exe% %sql_options% -i temp.sql -o output%count%.txt %sql_db%
set /A count=%count%+1
goto :eof


:eof

at the end output1.txt..output4.txt are created. Each file contains the output of one sql command.

wimh
  • 15,072
  • 6
  • 47
  • 98
0

You don't often perform SQL queries on a normal file, it generally requires a DBMS (database management system) at the other end for interpreting the SQL and extracting the relevant data.

Writing a batch file is relatively easy, be it a UNIX shell like bash or the Windows cmd.exe.

But, if you're thinking about implementing a full blown SQL query language that operates on text files (or any non-database file, really), that's probably going to take more then one question on Stack Overflow :-)

Perhaps you could flesh out your question with a little more detail, given that we may have misunderstood your requirements.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • The task is to automate the process. So have to write a batch script to parse this file , The input will be the attached file and What I all finally need is only the sql query in a single line and output should be in file. The each input file will have an 4 Sql query. So each sql query need to be in new line. – sudeep Feb 04 '11 at 07:32
  • @sudeep: so what you're asking for is an SQL interpreter and extraction engine to be written, which will handle the file you _haven't_ attached and write the extracted data to a file? If that's the case, I don't think you fully understand the complexity of what you're proposing. – paxdiablo Feb 04 '11 at 07:35
  • input will be a textfile which has 4 query in it. Now a batch file is to be written which will take 1 query at a time and execute it and output will be stored in a file. So there will be 4 seperate output file for 4 query – sudeep Feb 04 '11 at 07:41
  • so what to write in the batch file so that it will take a text file as an input? – sudeep Feb 04 '11 at 07:47
  • @sudeep, see my answer to your other question: http://stackoverflow.com/questions/4895725/batch-file-which-will-take-a-text-file-as-an-input – paxdiablo Feb 04 '11 at 08:29
0

I think what you're looking for is Microsoft Log Parser 2.2. It allows you to execute SQL queries on a a number of file types, including logs, CSV and XML files.

Victor Welling
  • 1,867
  • 12
  • 14
  • input will be a textfile which has 4 query in it. Now a batch file is to be written which will take 1 query at a time and execute it and output will be stored in a file. So there will be 4 seperate output file for 4 query – sudeep Feb 04 '11 at 07:44