-1

I have a process in SSIS where I create three files.

Header.txt work.txt Trailer.txt

Then I use an Execute Process Task to call my Powershell script. I basically need to take the work.txt file and prepend the header record to it (while maintaining integrity of original values in work.txt) and then append the trailer record (which is generated with total row counts, etc.).

Currently I have:

Set-Location "H:\Documentation\Projects\CVS\StageCVS"
Clear-Content "H:\Documentation\Projects\CVS\StageCVS\CVSMemberEligibility"
Get-Content Header.txt, work.txt, Trailer.txt|out-file "H:\Documentation\Projects\CVS\StageCVS\CVSMemberEligibility" -Confirm

This is fine in testing where I only had 1000 rows, but now that I have 67,000 rows the process takes forever.

I was looking at the Add-Content cmdlet but I can't find an example where it adds the header. Can someone assist with the syntax on going to the first line in the file and then adding the content before that first line?

many thanks in advance!

Just to clarify: I would like to build off the work.txt file. This si where the majority of the data is already, so instead of rewriting it all to a new file, I think a copy would make more sense. So in theory I would create all three files. copy the work file to say workfile.txt . Prepend header to workfile, append trailer to workfile, rename workfile.

UPDATE This seems to work for the trailer.

Set-Location "H:\Documentation\Projects\CVS\StageCVS"
#Clear-Content "H:\Documentation\Projects\CVS\StageCVS\CVSMemberEligibility"
Copy-Item work.txt workfile.txt
#Get-Content Header.txt, work.txt, Trailer.txt|out-file "H:\Documentation\Projects\CVS\StageCVS\CVSMemberEligibility"
Add-Content workfile.txt -value (get-content Trailer.txt)

UPDATE

Also tried:

Set-Location "H:\Documentation\Projects\CVS\StageCVS"
$header = "H:\Documentation\Projects\CVS\StageCVS\Header.txt"
#Clear-Content "H:\Documentation\Projects\CVS\StageCVS\CVSMemberEligibility.txt"
Copy-Item work.txt workfile.txt
#(Get-Content Header.txt, work.txt, Trailer.txt -readcount 1000)|Set-Content "H:\Documentation\Projects\CVS\StageCVS\CVSMemberEligibility"
Add-Content workfile.txt -value (get-content Trailer.txt)
.\workfile.txt = $header + (gc workfile.txt)
Sam
  • 7,252
  • 16
  • 46
  • 65
Hituptony
  • 2,740
  • 3
  • 22
  • 44
  • have you tried doing your Get-Content with the -Raw switch? That reads the entire file as one string instead of line, by line, by line. Or do a -ReadCount 1000, so it processes 1000 lines at a time instead of one at a time. – TheMadTechnician Jun 24 '14 at 17:03
  • readcount 1000 might work. readcount 0 was destructive, as someone else mentioned...the raw switch didn't work, not sure why – Hituptony Jun 24 '14 at 17:27
  • the file isabout a 1GB – Hituptony Jun 24 '14 at 17:29
  • it is loading those thousand rows into memory, which is bogging down my process pretty badly. I think it would be better to append to the copy then to recreate all the file contents. How can I put the header record in when data is already there? @TheMadTechnician – Hituptony Jun 24 '14 at 17:43

2 Answers2

1

This is something that seems so easy but the reality is that it is not due to the underlying filesystem. You are going to need a file buffer or a temp file or if you are really brave you can look at extending the file and transposing the characters. As this guy did in C#.

Insert Text into Existing Files in C#, Without Temp Files or Memory Buffers http://www.codeproject.com/Articles/17716/Insert-Text-into-Existing-Files-in-C-Without-Temp

Bin
  • 211
  • 1
  • 7
1

So as it turns out out-file and get-content are not very performance enhanced. I found that it was taking over 5 minutes to run 5000 record result set and write/read the data.

When i researched some different performance options for Powershell I found the streamwriter .NET method. For the same process this ran in under 15 seconds.

Being that my result set in production environment would be 70-90000 records this was the approach I took.

Here is what i did:

[IO.Directory]::SetCurrentDirectory("H:\Documentation\Projects\CVS\StageCVS")
Set-Location "H:\Documentation\Projects\CVS\StageCVS"
Copy-Item ".\ELIGFINAL.txt" H:\Documentation\Projects\CVS\StageCVS\archive\ELIGFINAL(Get-Date -f yyyyMMdd).txt
Clear-Content "H:\Documentation\Projects\CVS\StageCVS\ELIGFINAL.txt"
Copy-Item work.txt workfile.txt
Add-Content workfile.txt -value (get-content Trailer.txt)


$work = ".\workfile.txt"
$output = "H:\Documentation\Projects\CVS\StageCVS\ELIGFINAL.txt"
$readerwork = [IO.File]::OpenText("H:\Documentation\Projects\CVS\StageCVS\workfile.txt")
$readerheader = [IO.File]::OpenText("H:\Documentation\Projects\CVS\StageCVS\Header.txt")
try
{
    $wStream = New-Object IO.FileStream $output ,'Append','Write','Read'

    $writer = New-Object System.IO.StreamWriter $wStream
    #$write-host "OK"
}
finally
{

}
$writer.WriteLine($readerheader.ReadToEnd())
$writer.flush()

$writer.WriteLine($readerwork.ReadToEnd())

$readerheader.close()
$readerwork.close()
$writer.flush()
$writer.close()
Hituptony
  • 2,740
  • 3
  • 22
  • 44