asked on

merge and rename file

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

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aneilg

ASKER

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

RobSampson

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.

aneilg

ASKER

fantastic

RobSampson

Thanks for the grade.

Rob.