Consolidate multiple very large medical claim response text files for database load

Question

Each month we receive 30 text files containing various medical claim responses. We sort these into three subfolders and then consolidate and load them into a database table for analysis.

This may not be the wisest way to do this, so feel to include better methods in addition to code review, but I'm learning Powershell and trying to use it to accomplish two things:

• Add a column (either in memory or in the actual file) to text files containing several undelimited columns of known (fixed) lengths
  • The new column's data would simply be the name of the current file
  • The new column would be of unknown (variable) length
  • When the text files are combined into a single CSV and loaded into a database table, the new column tells user the source file of a record so they can refer back to it if needed
  • The source files are located in separate subfolders - each subfolder gets its own consolidated csv/table.
• Combine all of the modified text files into a single csv for load into Teradata via Teradata FastLoad
  • The text files can be up to 5GB in combined size

The script works, but pretty slowly. We were previously doing this via a batch file containing Perl, but the Perl broke and I thought Powershell had numerous possible advantages for this task:

1. Already installed on every user's computer - new task managers do not need to request Perl via the IT software pipeline
2. Future maintainers can learn one language instead of 2 (Batch and Perl)

I've included the previous batch file for comparison:

The Powershell file I wrote:

#Powershell -executionpolicy bypass -file "\\(directory 1)\Create Consolidated CSV.ps1"


function ConsolidateResponseFile($plan, $fileindex) {

    $pathPlanResponseFileCSV = ($_.FullName + ([System.IO.Path]::DirectorySeparatorChar) + "Consolidated_Response_File.csv") 

    if (Test-Path $pathPlanResponseFileCSV) {
        Remove-Item $pathPlanResponseFileCSV
    }

    $originalFiles = Get-ChildItem -Path ($_.FullName + ([System.IO.Path]::DirectorySeparatorChar) + "*.txt") -Attributes !Directory 

    $originalFiles | ForEach-Object {
        $fileindex = $fileindex + 1

        $fileName = $_.Name

        $csv = import-csv $_ -Header Stage_Data , File_Name

        $csv | ForEach-Object {
            $_.File_Name = $fileName            
        }

        $percent = $fileindex/$totalfiles*100
        Write-Progress -Activity "Consolidating text file data..." `
            -PercentComplete $percent `
            -CurrentOperation $fileName `
            -Status "$percent% Complete"        

        $finalcsv = $finalcsv + $csv
    }

    $percent = $fileindex/$totalfiles*100
    Write-Progress -Activity "Writing final csv..." `
        -PercentComplete $percent `
        -CurrentOperation $fileName `
        -Status "$percent% Complete"

    $finalcsv | export-csv -LiteralPath $pathPlanResponseFileCSV -NoTypeInformation

    $fileindex
}


$allResponseFilesForMonth = "\\(directory 2)\"

$files = Get-ChildItem -path "$allResponseFilesForMonth*.txt" -Attributes !Directory -Recurse
$totalfiles = $files.Count

$planFolders = Get-ChildItem -path $allResponseFilesForMonth -Attributes Directory

$fileindex = 0

$planFolders | ForEach-Object {

    $plan = $_
    ConsolidateResponseFile $plan, $fileindex
    $fileindex = ConsolidateResponseFile

}

For comparison, the previous Batch script (which makes individual CSVs first and does only one subfolder):

pushd \\(directory)\

REM copy original files
copy *.TXT *.csv

REM Add filename to end of each record. 
net use b: /delete /y
net use b: "\\(directory)\"
for /f %%a IN ('dir /b "\\(directory)\*.csv"') do (perl -i.bak -p -e "s/\n/|$ARGV\n/" %%a )

REM Copy all csvs to one master csv
copy *.csv Consolidated_Response_File.csv

Here's some test data to use. There will be 30 .txt files, and for the subfolder with 20 of them, they can be up to a combined 5GB in size.

Before data for FileName1.txt

250CharactersOfUndelimitedFixedLengthData1
250CharactersOfUndelimitedFixedLengthData2
250CharactersOfUndelimitedFixedLengthData3
250CharactersOfUndelimitedFixedLengthData4
250CharactersOfUndelimitedFixedLengthData5

After data for FileName1.txt

250CharactersOfUndelimitedFixedLengthData1,FileName1.txt
250CharactersOfUndelimitedFixedLengthData2,FileName1.txt
250CharactersOfUndelimitedFixedLengthData3,FileName1.txt
250CharactersOfUndelimitedFixedLengthData4,FileName1.txt
250CharactersOfUndelimitedFixedLengthData5,FileName1.txt

Answer 1

Personally I think you're over complicating things. There's not much need for a function here that I can see. You're also getting directory listings several times, which is probably trivial in the grand scheme of things.

First, you get the listing of your files right up front, you shouldn't need to do it over and over. What you can do is use the Group-Object command and the Directory property, and then take action against each grouping. I moved up your $fileindex declaration, and made it scoped to the entire script so that it can be used within a loop and not having scoping issues. I also use % which is an alias for ForEach-Object, and I use Group which is short for Group-Object.

$allResponseFilesForMonth = "\\(directory 2)"
$files = Get-ChildItem -path "$allResponseFilesForMonth\*.txt" -File -Recurse
$totalfiles = $files.Count
$script:Fileindex = 0
$files|Group Directory|%{

Now, what $files|group Directory will do is output 3 objects. Each object will have 3 properties: Count, Name, and Group. For this you'll probably see something like:

Count Name                      Group
----- ----                      -----
    8 \\(directory 2)\PlanA        {\\(directory 2)\PlanA\BigTextFile1.txt, \\(directory 2)\PlanA\BigTextFile2.txt, \\(directory 2)\PlanA\BigTextFile3.txt, \\(directory 2)\PlanA\BigTextFile4.txt...}
   13 \\(directory 2)\PlanB        {\\(directory 2)\PlanB\BigTextFile1.txt, \\(directory 2)\PlanB\BigTextFile10.txt, \\(directory 2)\PlanB\BigTextFile11.txt, \\(directory 2)\PlanB\BigTextFile12.txt...}
    9 \\(directory 2)\PlanC        {\\(directory 2)\PlanC\BigTextFile1.txt, \\(directory 2)\PlanC\BigTextFile2.txt, \\(directory 2)\PlanC\BigTextFile3.txt, \\(directory 2)\PlanC\BigTextFile4.txt...}

Now for each of those objects we just need to define the output CSV file name (can be derived from the Name property), specify the header line, and loop through each file in the Group property to read it and append the file name to the end.

Declaring the CSV path is as easy as a Join-Path command:

        $pathPlanResponseFileCSV = Join-Path $_.Name Consolidated_Response_File.csv

Then you can do the bit to make sure that it doesn't exist just like you were before.

Next is looping through the files for that directory. We'll start by outputting the header line once in the Begin block, then loop through files in the Process block:

    $_.Group|% -Begin {'Stage_Data,File_Name'} -Process {

To be honest reading it as a CSV is probably causing you a lot of overhead converting everything from a simple string to an object with properties, so lets just read it as text and do string manipulation to add a comma and the file name to the end of the line. For large files you'll probably see better performance from [file.io.file]::ReadLines() if not a StreamReader, but I'm not real familiar with streamreaders so I'll go with what I know.

First thing, we want to increase $script:FileIndex by 1, and write the progress. That is most easily done with these lines:

        $script:fileindex++
        Write-Progress -Activity "Consolidating text file data..." -PercentComplete ($script:fileindex/$totalfiles*100) -Status "Compiling $pathPlanResponseFileCSV" -CurrentOperation "Migrating data from $($File.name)"

Then we grab the file name, and read the file in and for each line we add a comma, and the filename to the end. I use string formatting for this, but there are a few ways to do it like string concatenation, or using sub-expressions to expand property values in a double quoted string. Then we'll close the ForEach loop, and pipe it to Set-Content.

        $FileName = $_.Name
        [System.IO.File]::ReadLines($_.FullName)|%{"{0},{1}" -f $_,$FileName}
    }|Set-Content $pathPlanResponseFileCSV

After that we close the ForEach-Object loop with a }, and it performs that all on each directory. In the end it all looks like this:

$allResponseFilesForMonth = "\\(directory 2)"
$files = Get-ChildItem -path "$allResponseFilesForMonth\*.txt" -File -Recurse
$totalfiles = $files.Count
$script:Fileindex = 0
$files|Group Directory|%{
    $pathPlanResponseFileCSV = Join-Path $_.Name Consolidated_Response_File.csv
    if (Test-Path $pathPlanResponseFileCSV) {
        Remove-Item $pathPlanResponseFileCSV
    }

    $_.Group|ForEach-Object -Begin {'Stage_Data,File_Name'} -Process {
        $script:fileindex++
        Write-Progress -Activity "Consolidating text file data..." -PercentComplete ($script:fileindex/$totalfiles*100) -Status "Compiling $pathPlanResponseFileCSV" -CurrentOperation "Migrating data from $($_.name)"
        $FileName = $_.Name
        [System.IO.File]::ReadLines($_.FullName)|%{"{0},{1}" -f $_,$FileName}
    }|Set-Content $pathPlanResponseFileCSV
}

I ran it against 3 folders, each folder had 3 123MB text files (500,000 lines of 250 characters). It took just under 4 minutes to run that against the 9 files. I'm pretty sure that should speed up your script.