Bulk copy CSV files using NPGSQL 3.0.5 BinaryImporter

Question

I have upgraded to NPGSQL 3.0.5 and realized the NpgsqlCopyIn is no more available. With older version I could process CSV file with NpgsqlCopyIn which is really fast and efficient for bulk copying huge data. I used

var copystr = "COPY tablename (col1,col2,etc) FROM 'csv file' STDIN WITH DELIMITER ',' CSV HEADER" ; 
NpgsqlCommand dbCommand = new NpgsqlCommand(copyStr, _DataStoreConnection); 
NpgsqlCopyIn copyIn = new NpgsqlCopyIn(dbCommand, _DataStoreConnection, stream); 
copyIn.Start(); 

But with 3.0 version, I couldn't find a way for bulk copying by just letting the binary importer that data is CSV. Instead I use the below code

StreamReader streamReader = null;
try {
    streamReader = new StreamReader(fileStream);
    {
        var copyStr = string.Format("COPY {0} ({1}) FROM STDIN (FORMAT BINARY)", _DataStoreName, string.Join(",", _DataStoreColumns.Select(a => a.ToLower())));
        if (_DataStoreConnection.State == ConnectionState.Closed)
        _DataStoreConnection.Open();
        string csvLine = string.Empty;
        while ((csvLine = streamReader.ReadLine()) != null)
        {
            if (lineCount > 0)
            {
                using (var importWriter = _DataStoreConnection.BeginBinaryImport(copyStr))
                {
                    importWriter.WriteRow(csvLine.Split(','));
                }
            }
            else
            {
                lineCount++; //This is to skip the first line from the CSV file. First line will be header, so skip it.
            }
        }
    }
}

Is there a way where I can specify the BinaryImporter that the input data is CSV, so that it takes care of delimiter and inserting the data to datastore as in NpgSqlCopyIn?

Answer 1

You can't use the binary importer to import CSV because, well, CSV isn't binary :)

The binary importer is a more efficient way to import your data, see an example in the Npgsql docs. If you absolutely have to import CSV, you can still do that via BeginTextImport but you have to format the text yourself, i.e. put the delimiter and so forth. See the docs for that as well.