Powershell: Implementing an IdataReader wrapper around streamreader

idatareader powershell sqlbulkcopy streamreader


I am trying to load extremely large CSV files into SQL Server using Powershell. The code also has to apply on the fly regex replacements, allow for various delimiters, EOR, and EOF markers. For maintenance, I would really like all of this logic to exist in Powershell without importing assemblies.

To be efficient, I know I need to use the SQLBulkCopy method. But, all of the Powershell examples I see fill a DataTable and pass it which is not possible for me because of the file size.

I am pretty sure I need to wrap StreamReader in an Idatareader and then pass that to SQLBulkcopy. I found a couple great examples of this implemented in C#:

Is it possible to accomplish this functionality using native PowerShell without importing the C# assembly? I am specifically having a hard time converting the abstract class wrapper.

This is the code I have so far that does not pass an IdataReader and breaks on memory limits.

function Get-CSVDataReader()
param (
    $parsedData = New-Object 'System.Collections.Generic.List[string]'
    #List<string[]> parsedData = new List<string[]>()

    $sr = new-object IO.StreamReader($path)

    while ($line = $sr.ReadLine())
        #regex replace and other logic here

    ,$parsedData #if this was an idatareader, the comma keeps it from exploding

$MyReader = Get-CSVDataReader('This should not fill immediately.  It needs a Read Method.')

Thanks a bunch for the help.

10/3/2012 4:50:34 PM

Popular Answer

I'm importing large CSV's by a datatable and performing batch updates after 1 million rows.

if ($dt.rows.count -eq 1000000) {

Here is the link where I detail my own script on my blog, but the above code outlines the basic concept. My PowerShell script took 4.x minutes to import 9 million rows from a 1.1 GB CSV. The script relied on SqlBulkCopy, [System.IO.File]::OpenText and a datatable.

7/18/2014 2:39:46 PM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow