# 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 > 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.

• Thank you for writing this up. I won't accept this answer because it recommends Get-Content for large source files, but I appreciate the perl advice. – puzzlepiece87 May 6 at 12:24
• If they really are that large, just remove the parens around gc; see edit. – Oh My Goodness May 6 at 13:27
• I'm sure you know more about this than I do, but can you please explain in detail why you recommend Get-Content when tests consistently show StreamReader is much faster? This is why I previously said that I would not accept this answer. – puzzlepiece87 May 6 at 15:18