?
Solved

merge and rename file

Posted on 2011-10-20
5
Medium Priority
?
295 Views
Last Modified: 2012-05-12
i am using ssis and a script task manager. i want to be able to merge 3 txt files then output the files to one csv file with the date, and a prefix of PE. PEDDMMYY
Dim objFSO
    Dim objFile
    Dim objOutputFile
    Dim objTextFile
    Dim strText
    Dim sFolderHeader '= "\\folderb-001\Data\folderc\folder\Extracts\Header.txt"
    Dim sFolderDetail '= "\\folderb-001\Data\folderc\folder\Extracts\Detail.txt"
    Dim sFolderFooter '= "\\folderb-001\Data\folderc\folder\Extracts\Footer.txt"
    Dim my_File_Name
    Dim strLine
    Dim strNewContents
    Dim strFileName

    Const ForReading = 1

    Public Sub Main()

        objFSO = CreateObject("Scripting.FileSystemObject")
        objOutputFile = objFSO.CreateTextFile("PE.txt")

        'Outputs the header record
        Header_Extract()
        'Outputs the detail record
        Detail_Extract()
        'Outputs the footer record
        Footer_Extract()

        'Removes the blank lines from the file
        RemoveBlankLines()

        objOutputFile.Close()

        Dts.TaskResult = Dts.Results.Success

    End Sub

    Public Sub Header_Extract()

        'Reads the header file
        objTextFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\Header.txt", ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub Detail_Extract()

        'Reads the detail file
        objTextFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\Detail.txt", ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub Footer_Extract()

        'Reads the footer file
        objTextFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\Footer.txt", ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub RemoveBlankLines()

        'Remove blank lines from file
        objFile = objFSO.OpenTextFile("\\folderb-001\Data\folderc\folder\Extracts\PE.txt", ForReading)

        Do Until objFile.AtEndOfStream
            strLine = objFile.Readline
            strLine = Trim(strLine)
            If Len(strLine) > 0 Then
                strNewContents = strNewContents & strLine & vbCrLf
            End If
        Loop

    End Sub

Open in new window

0
Comment
Question by:aneilg
  • 3
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 37003583
Hi, try this.

I suggest you try it outside of the DTS package first, by uncommenting these two lines:
'Call Main()
'WScript.Echo "Finished."

and see whether it produces any errors.

Regards,

Rob.
Dim objFSO
Dim objFile
Dim objOutputFile
Dim objTextFile
Dim strText
Dim sFolderHeader '= "\\folderb-001\Data\folderc\folder\Extracts\Header.txt"
Dim sFolderDetail '= "\\folderb-001\Data\folderc\folder\Extracts\Detail.txt"
Dim sFolderFooter '= "\\folderb-001\Data\folderc\folder\Extracts\Footer.txt"
Dim my_File_Name
Dim strLine
Dim strNewContents
Dim strFileName
Dim strFileA
Dim strFileB
Dim strFileC
Dim strOutputFile

Const ForReading = 1
'Call Main()
'WScript.Echo "Finished."


    Public Sub Main()

		strFileA = "\\folderb-001\Data\folderc\folder\Extracts\Header.txt"
		strFileB = "\\folderb-001\Data\folderc\folder\Extracts\Detail.txt"
		strFileC = "\\folderb-001\Data\folderc\folder\Extracts\Footer.txt"
		strOutputFile = "\\folderb-001\Data\folderc\folder\Extracts\PE" & Right("0" & Day(Date), 2) & Right("0" & Month(Date), 2) & Right(Year(Date), 2) & ".txt"

        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objOutputFile = objFSO.CreateTextFile(strOutputFile, True)
		
        'Outputs the header record
        Header_Extract()
        'Outputs the detail record
        Detail_Extract()
        'Outputs the footer record
        Footer_Extract()
		
		objOutputFile.Close()
		
        'Removes the blank lines from the file
        RemoveBlankLines()

        Dts.TaskResult = Dts.Results.Success

    End Sub

    Public Sub Header_Extract()

        'Reads the header file
        Set objTextFile = objFSO.OpenTextFile(strFileA, ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub Detail_Extract()

        'Reads the detail file
        Set objTextFile = objFSO.OpenTextFile(strFileB, ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub Footer_Extract()

        'Reads the footer file
        Set objTextFile = objFSO.OpenTextFile(strFileC, ForReading)

        strText = objTextFile.ReadAll
        objTextFile.Close()
        objOutputFile.WriteLine(strText)
        'RemoveBlankLines()

    End Sub

    Public Sub RemoveBlankLines()

        'Remove blank lines from file
        Set objFile = objFSO.OpenTextFile(strOutputFile, ForReading, False)

        Do Until objFile.AtEndOfStream
            strLine = objFile.Readline
            strLine = Trim(strLine)
            If Len(strLine) > 0 Then
                strNewContents = strNewContents & strLine & vbCrLf
            End If
        Loop
        objFile.Close
		
		Set objFile = objFSO.CreateTextFile(strOutputFile, True)
		objFile.Write strNewContents
		objFile.Close
		
    End Sub

Open in new window

0
 

Author Comment

by:aneilg
ID: 37005204
thanks for that, when i add strOutputFile = "\\folderb-001\Data\folderc\folder\Extracts\PE" & Right("0" & Day(Of Date)(), 2) & Right("0" & Month(Of Date)(), 2) & Right(Year(Of Date), 2) & ".txt"

i get an error on (Of Date) public function day(day value as date) has integer as no type parameters and so cannot have type arguments.

thanks
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 37013436
Why are you using "Of Date"?  In VBScript, you use just "Date".

This function:
Day(Date)

should give you the Day value of the current date. Day is a function, and Date is another function.

Rob.
0
 

Author Closing Comment

by:aneilg
ID: 37030624
fantastic
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 37034506
Thanks for the grade.

Rob.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question