PowerShell - comparing large fixed width file to large SQL table

Sorry all for not being timely on replying, but I have a solution! There's most likely room for improvement, but the solution is reasonably quick. I didn't have access to my database to run PowerShell directly against it, so I'm making use of the SQL Import and Export Wizard at the end.

Summary of process:

1. Create a CSV file of data points, which will be consumed as an object array in PowerShell.
2. Find the files of interest and store them into a string array (possibly not necessary, but it seemed to be speedier)
3. Cycle through each file found in your string array. For each file, open a .NET StreamReader, and for each line, parse the file against your object array of data points to create substrings that you write to a single, consolidated, delimited, output file. I recommend the pipe character (looks like this: |, typically above Enter) because it typically isn't found in data, whereas a rogue comma or tab character may trip you up.
4. Once the script is complete, use the import wizard in SQL to create a table from your output file

Detail

1. Create a CSV file that lists your data points in column A, the starting position in column B, and the widths in column C. It would look something like this:

2. Import your data points in your script by utilizing an object array.

   $dataPoints = Import-Csv "c:\temp\datapoints.csv"
   $objDataCols = @()
   foreach($objCol in $dataPoints){
       objColumn = New-Object psobject
       $objColumn | Add-Member -Type NoteProperty -Name Name -Value $objCol.Name
       $objColumn | Add-Member -Type NoteProperty -Name Position -Value ([int] $objCol.Position)
       $objColumn | Add-Member -Type NoteProperty -Name ColumnLength -Value ([int] $objCol.ColumnLength)
       $objSourceCols += $objColumn
   }
   

3. Find the files and assemble the names to an array (optional). I used a regular expression to filter for my files.

   $files = @()
   Get-ChildItem -Path $sourceFilePath | Where-Object { $_.FullName -match $regExpression } | ForEach-Object{
       $files += $_.FullName
   }
   

4. Loop through each file and parse them to an output file. In production code, you would want try/catch blocks, but I left them out in the example.

   $writer = New-Object System.IO.StreamWriter "c:\temp\outputFile.txt"
   ForEach($sourceFileName in $files){
       $reader = [System.IO.File]::OpenText($sourceFileName)
       while($reader.Peek() -gt -1){
           $line = $reader.ReadLine()
   
           # Write each data point in the line, pipe delimited
           for($i = 0; $j -le ($objDataCols).Length; $i++){
               # Write to a pipe-delimited file
               $writer.Write("{0}|", $line.Substring($objDataCols[$i].Position, $objDataCols[$i].ColumnLength))
           }
   
           # Write a new line, along with any additional reference columns not defined in the source file, such as adding in the source file name and line number
           $writer.WriteLine($sourceFileName)
       }    
       $reader.Close()         
       $reader.Dispose()
   }
   $writer.Close()
   $writer.Dispose()
   

5. Import the pipe-delimited output file into SQL.