-3


Could you please help find a Windows solution that can solve the below needs?

Currently I do this manually in Notepad++ with regular expression, but this routine will be used by several persons and an automatic way will be better.

I have a huge text file containing insert statements which will be loaded in SQL Server.
What I'm trying to do is:
1 - insert BEGIN TRANSACTION; at the beginning of the file
2 - insert COMMIT TRANSACTION; at the END of the file
3 - insert 3 new lines with text after every 1000 lines:
COMMIT TRANSACTION;
GO
BEGIN TRANSACTION;
4 - Copy the first 3 line from another file to current file

This thing will happen in dynamic paths.

Here is an example for a file with 5000 lines:

Input

Line_No Text
1 insert into...
500 insert into...
1000 insert into...
1001 insert into...
2000 insert into...
2001 insert into...
5000 insert into...

Desired output

Line_No Text
1st line with text from other file in same path
2nd line with text from other file in same path
3rd line with text from other file in same path
BEGIN TRANSACTION;
1 insert into...
500 insert into...
1000 insert into...
1001 COMMIT TRANSACTION;
1002 GO
1003 BEGIN TRANSACTION;
1004 insert into...
2000 insert into...
2001 COMMIT TRANSACTION;
2002 GO
2003 BEGIN TRANSACTION;
2004 insert into...
5000 insert into...
5001 COMMIT TRANSACTION;

Thank you so much! :)

UPDATE: Here is the solution for doing this task using the solution @mjolinor has provided:

$cd = Get-Location
$PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent
$newfile = "$cd\newfile.sql"
$oldfile = "$cd\oldfile.sql"
$string = Get-Content $cd\otherfile.txt

$header = @"
:out $PSScriptRoot\log.txt
USE $string
GO
BEGIN TRANSACTION; 
"@

$insert = @'
COMMIT TRANSACTION; 
GO
BEGIN TRANSACTION; 
'@

$header | Set-Content $newfile
 Get-Content $oldfile -ReadCount 1000 |
   ForEach-Object { $_,$insert | Add-Content $newfile -Force}
 'COMMIT TRANSACTION;' | Add-Content $newfile

Thank you @mjolinor :-) !

Cosmin
  • 15
  • 5
  • 5
    This is not a question but a task request. Please try it on your own, share your efforts and describe precisely where you are stuck, including what your code is supposed to do and what it actually does. – aschipfl Apr 27 '16 at 17:22
  • You say you can already do this with regex... In PowerShell you can use `$text -replace 'regexpattern', 'newvalue'` to replace using regex. Personally I would use some type of loop and a counter. – Frode F. Apr 27 '16 at 17:58
  • It is also not clear what exactly you mean by "dynamic paths" neither what file to pick in step 4 (copy the first 3 line from another file to current file)... – aschipfl Apr 28 '16 at 16:11

1 Answers1

1

Not tested extensively, but I thing this should work:

$newfile = 'c:\somedir\newfilename.txt'
$oldfile = 'c:\somedir\oldfilename.txt'
$otherfile = 'c:\somedir\someotherfilename.txt'

$insert = @'
COMMIT TRANSACTION; 
GO
BEGIN TRANSACTION; 
'@

'BEGIN TRANSACTION' | Set-Content $newfile
 Get-Content $oldfile -ReadCount 1000 |
   ForEach-Object { $_,$insert | Add-Content $newfile }
 Get-Content $otherfile -Head 3 | Add-Content $newfile
mjolinor
  • 66,130
  • 7
  • 114
  • 135