Split text file into multiple files based on header row

Question

I am trying to split a .txt file into separate data files from an SSIS Script Task, using header rows starting FILE| to denote when a new file should be created. The row starting FILE| is pipe delimited and the second delimited value is the file name to use.

In the below code I have been able to obtain the file name. However, I want to be able to write all rows between the FILE| header rows to their corresponding file. E.g., DataFile1 has all files below the header row written to DataFile1.txt until the row header DataFile2 is hit.

Below is my commented code on how far I've got and what I think I'm missing. Any help to get me over the line is appreciated:

    public void Main()
    {
        // The file from my SSIS package containing the unsplit data
        string dataFile = Dts.Variables["User::unsplitDataFile"].Value.ToString();

        // The destination I want to write this to, e.g. \\vSplitDestination\DataFile1.txt:
        string splitDestination = Dts.Variables["User::vSplitDestination"].Value.ToString();

        bool fireAgain = true;

        int counter = 0;
        string line;

        // Read the file and display it line by line.
        System.IO.StreamReader file = new System.IO.StreamReader(dataFile);
        while ((line = file.ReadLine()) != null)
        {
            if (line.StartsWith("FILE"))
            {
                // File Names found
                Dts.Events.FireInformation(0, "Info:", line.Split('|','|')[1].ToString(), String.Empty, 0, ref fireAgain);

                // Create file with file name above ( line.Split('|','|')[1].ToString() ) and fill with all rows until next "FILE" pattern hit.

                // Repeat for all file names

            }

            counter++;
        }

        file.Close();

        Dts.TaskResult = (int)ScriptResults.Success;
    }

For user "observer" in comments below, attempt at powershell script - answers on this also welcomed:

$Path = "\\Transfer"
$InputFile = (Join-Path $Path "unsplit.data")
$Reader = New-Object System.IO.StreamReader($InputFile)

While (($Line = $Reader.ReadLine()) -ne $null) {
    If ($Line -match "FILE|(.+?)") {
        $OutputFile = $matches[0] + ".txt"
    }

    Add-Content (Join-Path $Path $OutputFile) $Line
}

Sample data (so each FILE| becomes its own datafile(n).txt file with the rows below in that file)

FILE|datafile1|25/04/17
25044|0001|37339|10380|TT75
25045|0001|37339|10398|TT75
25046|0001|78711|15940|TT75
FILE|datafile2|25/04/17
25047|0001|98745|11263|TT75
25048|0001|96960|13011|TT84
FILE|datafile3|25/04/17
25074|0001|57585|13639|TT84
25075|0001|59036|10495|TT84
FILE|datafile4|25/04/17
25076|0001|75844|13956|TT84
25077|0001|17430|01111|TT84

you can also use PowerShell using ExecuteProcessTask instead of using ScriptTask. PowerShell will be much better then the ScriptTask. — observer
– observer, Commented May 3, 2017 at 10:07
Thanks - I'm weak on powershell. I've tried a search but not come up with a satisfactory result, I've had an attempt (see addendum to original question) but isn't working, are you able to develop this into an answer here ? — TJB
– TJB, Commented May 3, 2017 at 10:38
Ok, I will create a demo package and after I am done I will post that as an answer. Will try to do it Today. — observer
– observer, Commented May 3, 2017 at 10:54

score 3 · Accepted Answer · 2017-05-04 09:18:09Z

3

I see 2 errors:

-match needs a RegEx, in a RegEx the unescaped vertical bar denotes an or expression, so you have to escape | with a backslash \|
the first matched group in a RegEx is $matches[1]
to match everything between the first two bars I use a negated class

$Path = "\\Transfer"
$InputFile = (Join-Path $Path "unsplit.data")
$Reader = New-Object System.IO.StreamReader($InputFile)

While (($Line = $Reader.ReadLine()) -ne $null) {
    If ($Line -match 'FILE\|([^\|]+)') {
        $OutputFile = "$($matches[1]).txt"
    }
    Add-Content (Join-Path $Path $OutputFile) $Line
}

edited May 4, 2017 at 9:18

answered May 3, 2017 at 13:27

user6811411

Sign up to request clarification or add additional context in comments.

1 Comment

TJB Over a year ago

Spot on. Thanks a lot.

Collectives™ on Stack Overflow

Split text file into multiple files based on header row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related