DOS script to read one file into another

Question

I discovered that I couldnt be granted BULKADMIN or SYSADMIN role on my hosted sql server db and trying to bypass the bulk insert operation by creating a .sql file containing insert statements. The insert statements are created using a xls macro, so theres some bit of manual bodyshopping work that I am doing now. So let me draw the problem here.

I have a text file with the following contents -

10/05/2011 01:21 PM 1-16332-1008261.psa 
10/05/2011 01:21 PM 1-16332-1011698.psa 
10/05/2011 01:21 PM 1-16332-1023151.psa 
10/05/2011 01:21 PM 1-16332-1035695.psa 
10/07/2011 03:36 PM 1-16332-1023193.psa 
10/07/2011 03:36 PM 1-16332-1035694.psa 
6 File(s) 8,933,754 
2 Dir(s) 1,675,268,096 free

What I want to achieve in my final output file is this -

insert into xyz.abcd values('10/05/2011', '1-16332-1008261.psa');
insert into xyz.abcd values('10/05/2011', '1-16332-1011698.psa');
insert into xyz.abcd values('10/05/2011', '1-16332-1023151.psa');
insert into xyz.abcd values('10/05/2011', '1-16332-1035695.psa');
insert into xyz.abcd values('10/07/2011', '1-16332-1023193.psa');
insert into xyz.abcd values('10/07/2011', '1-16332-1035694.psa');
go

Please take a note that the last two lines from my input text file are to be dinged in my final output sql file. Its kind of automating the xls macro I am manually doing now by dos scripting where the 'insert into xyz.abcd values' is considered to be a constant string which is written to each new line before the actual date and filename from the file been read. I will name my final output file as a .sql and execute it remotely from my computer. Can somebody please help me if this can be doable in DOS programming or not.

I have tried to present a similar problem in another post which I am trying to delete, but am unable to do so. Guess I didnt explain the other one properly.

I know I can redirect the string as a literal to a file by the echo command but the challenge is how do I read fraction of a line from one file and put them into a new file.

Thanks!

You could start with reading [SO DOS batch files: How to read a file](http://stackoverflow.com/questions/206114/dos-batch-files-how-to-read-a-file/4531090#4531090) and `for /?` especially the tokens part — jeb, Oct 12 '11 at 13:19

score 2 · Accepted Answer · answered Oct 12 '11 at 14:20

please put following code to notepad and save it as CMD batch file (e.g. Convert2SQLfile.cmd). Then run it from command line with 3 parameters, while:

1st parameter contains path to source file from which data will be read
2nd parameter contains path to destination file to which data will be saved
3rd parameter contains table name which should be stored in SQL command instead of "xyz.abcd" you have in your request

Hope this helps.

Regards,
Standa

    @echo off
    SetLocal

    SET App.SourceFile=%1
    SET App.DestinationFile=%2
    SET App.Table=%3


    FOR /f "usebackq tokens=1,4" %%i IN (`type %%App.SourceFile%%`) DO (
        IF #%%j# NEQ ## (
            IF #%%j# NEQ #free# (
                ECHO insert into %App.Table% values ^('%%i', '%%j'^); >> %App.DestinationFile%
            )
        )
    )

    ECHO go >> %App.DestinationFile%

Evianton, Thank you sounds like an understatement!! This works like a charm. This thread may help others who doesnt have BULKADMIN access but want to upload a sql file to the sql server db hosted on a remote server. — user982201, Oct 12 '11 at 16:03
@user982201 What is the purpose of `IF #%%j# NEQ #free#`? I get it, it's part of the original file, see question. — Roland, Oct 17 '17 at 12:36

DOS script to read one file into another

1 Answers1

Linked