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, PaymentJohn Doe 000123 2500.00 500.00Jerry Jones 000201 3500.00 650.00Troy Aikman 000550 1550.50 275.00999999999999999999999999999999999999999999999999999I want to insert the following row anywhere above the trailing row of 9's:Emmit Smith 000355 4250.00 785.00Any 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_cmdshellThis would copy a file a.txt to b.txt and insert a row xxxxx before the row containing 999999Took 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 offecho. > b.txtfor /F "tokens=1*" %%a in (a.txt) do call :procline %%agoto :EOF:proclineif %1 = 999999 goto :foundecho %1 >> b.txtgoto :EOF:foundecho xxxxx >> b.txtecho %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