Solved Read a file into another file with a prefix

October 11, 2011 at 20:04:35
Specs: Windows XP
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');

Note that the last two lines from my input text file are 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.

See More: Read a file into another file with a prefix

Report •

October 12, 2011 at 20:29:48
✔ Best Answer
This is doable. We will have to manipulate each line. It looks like your input file is the result of a dir, so we should start with that to generate a fresh file that doesn't contain anything except file names. THIS HASN'T BEEN TESTED

@echo off
REM getting clean list of files for input
dir /b c:\directory\*.psa > list.txt
for /f "tokens=*" %%A in (list.txt) do (
REM setting datestring variable equal to line so we can modify it
set datestring=%%A
REM extracting ten character date from start of line
set datestring=%datestring:~0,10%
REM extracting nineteen character file name from end of line
set filestring=%%A
set filestring=%filestring:~-19%
echo insert into xyz.abcd values('%datestring%', '%filestring%'); >> output.sql
echo go >> output.sql

Report •

October 13, 2011 at 09:05:02
Yeah, if your scripting environment has access to both the database and the directory in question, it'd probably be easier to use a VBScript and automate every step.

Doing so, however, would require knowing every step.

How To Ask Questions The Smart Way

Report •

October 17, 2011 at 11:06:34
Yes, your solution works like a charm!! Thank you so much for this. I was working on doing this on powershell but then I just saw your reply this morning. Appreciate your time and help on this.


Report •

Related Solutions

October 17, 2011 at 11:11:35
No problem Sanders_2503. Glad I could help. Thanks for the best answer mark.

Report •

Ask Question