Combine Medical Response Files Prior To Table Load This is a script I wrote to:


Combine multiple text files into one CSV
Append the source file's name to each line of data


This script currently takes about 3 seconds per 10,000 lines read - this is so long in comparison to a working batch/Perl script I have that I'm wondering what I'm doing wrong.

Comments about whether this is even the best approach is welcome - anything that gets me one combined file with the source file name appended to each line is welcome. I chose to use StreamReader because the source files are very large.

Powershell

# Powershell -executionpolicy bypass -file (.ps1 file path)

[System.IO.DirectoryInfo]$ResponsesFolder = (folder path)
[System.IO.DirectoryInfo]$pathCombinedResponsesCSV = ($ResponsesFolder.Parent.FullName + [IO.Path]::DirectorySeparatorChar + $ResponsesFolder.BaseName + "_Combined_Responses.csv")

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

$streamWriter = New-Object System.IO.StreamWriter $pathCombinedResponsesCSV
Write-Host $pathCombinedResponsesCSV

$files = Get-ChildItem -Path ($ResponsesFolder.ToString() + "*.*") -File
$totalFiles = $files.Count

$fileindex = 0

$files | ForEach-Object {
    $streamReader = New-Object System.IO.StreamReader $_.FullName

    [int]$fileindex = $fileindex + 1
    Write-Host ("File " + $fileindex + " of " + $totalFiles)

    [string]$fileName = $_.Name

    [int]$lineIndex = 0

    while (([string]$line = $streamReader.ReadLine()) -ne $null) {
        $streamWriter.WriteLine($line + "|" + $fileName)
        $lineIndex = $lineIndex + 1

        if ($lineindex % 10000 -eq 0) {
            Write-Host $lineindex "lines of" $fileName "loaded"         
        }
    }

    $streamReader.Close(); $streamReader.Dispose()

}

$streamWriter.Close(); $streamWriter.Dispose()


Batch/Perl

pushd (UNC folder path)

copy *.TXT *.csv

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

copy *.csv Consolidated_Magic_File.csv
The Perl approach can be shorter and faster than it already is:

perl -ple"BEGIN { @ARGV=glob shift } s,$,|$ARGV," *.csv &gt; Consolidated_Magic_File.not_an_input
ren *.not_an_input *.csv


The best PowerShell approach is not so different from the fast Perl approach.  Feed a list of filenames, use Get-Content (or gc) to read them, let PS figure out where the line breaks are, loop through those results and collect all the outputs in one place.

ls \\unc\folder -Filter *.txt | %{ $fn=$_.Basename; gc $_.PSPath | %{ $_ + "|$fn" } } | sc out.csv


$_.Basename is the name without path or extension.  You can use $_.Name to get the original name with extension, or $_.Basename + ".csv" to label the output with CSV-filenames even though your input was TXT files.