Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mangelo
Starting Member
2 Posts |
 Posted - 2010-12-01 : 12:10:25
|
I need a good suggestion on the best technic to use to insert a new row into an existing
flat file with 2 or more rows, anywhere in the file above the trailer record of all 9's.
For example, the existing flat file may contain the following data by column alignment:
Column Header names not included in the flat file,
Column Header names =
Customer_Name, Customer#, Balance, Payment
John Doe 000123 2500.00 500.00
Jerry Jones 000201 3500.00 650.00
Troy Aikman 000550 1550.50 275.00
999999999999999999999999999999999999999999999999999
I want to insert the following row anywhere above the trailing row of 9's:
Emmit Smith 000355 4250.00 785.00
Any suggestions? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-01 : 12:19:22
|
appending is easy - you can just use an echo statement and append to the file.
To do this you need something that either searches for a crlf or is aware of rows in a flat file.
I would be tempted to just bulk insert into a table, add the row then bcp it out.
A script taks in an ssis package or a .net script could do it easily.
So could perl or python.
depends really what you have available or are willing to use.
hmm - wonder if you could do it with a dos edit command?
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-01 : 13:02:17
|
You could maybe create a .bat file and call it with parameters using xp_cmdshell
This would copy a file a.txt to b.txt and insert a row xxxxx before the row containing 999999
Took me a lot fiddling to get this - it's been a long time since I've had to do it, probably errors in it.
@echo off
echo. > b.txt
for /F "tokens=1*" %%a in (a.txt) do call :procline %%a
goto :EOF
:procline
if %1 = 999999 goto :found
echo %1 >> b.txt
goto :EOF
:found
echo xxxxx >> b.txt
echo %1 >> b.txt
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Insert Rows in a Flat File