1
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – puzzlepiece87 May 6 at 12:24
  • 1
    \$\begingroup\$ If they really are that large, just remove the parens around gc; see edit. \$\endgroup\$ – Oh My Goodness May 6 at 13:27
  • \$\begingroup\$ 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. \$\endgroup\$ – puzzlepiece87 May 6 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.