Export an Excel document to a PostgreSQL database in C#

Question

I am developing a functionality that is responsible for reading an Excel document, and exporting its data to PostgreSQL. So far I have the following process. In an ASP.NET MVC application with C#, I generate a page where it is requested to enter a file.

This file is read and saved inside a stream object. Then, with the NPOI library, I convert this file into a list of entities that is created according to the data from the Excel document in read. Finally, after the list is done, it is saved via Entity Framework to the database. Here is the code that works today.

public async Task Index([FromForm] IFormFile archivoExcel)
{
    try
    {
        if (archivoExcel.Length > 0)
        {
            Stream excelStream = archivoExcel.OpenReadStream();
            IWorkbook miExcel = null;
            if (Path.GetExtension(archivoExcel.FileName) == ".xlsx")
            {
                miExcel = new XSSFWorkbook(excelStream);
            }
            else
            {
                miExcel = new HSSFWorkbook(excelStream);
            }

            List lstManifiestoExcel = new List();
            var sheet = miExcel.GetSheetAt(0);
            for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
            {
                var sheetRow = sheet.GetRow(i);
                ManifiestoDetalle md = new ManifiestoDetalle();
                md.CodigoEntrega        = sheetRow.Cells[0].ToString();
                md.Pais                 = sheetRow.Cells[1].ToString();
                md.NombreCompleto       = sheetRow.Cells[2].ToString();
                md.CodArea              = Convert.ToInt32(sheetRow.Cells[3].ToString());
                md.Telefono             = Convert.ToInt32(sheetRow.Cells[4].ToString());
                md.Direccion1           = sheetRow.Cells[5].ToString();
                md.Direccion2           = sheetRow.Cells[6].ToString();
                md.Direccion3           = sheetRow.Cells[7].ToString();
                md.Region               = sheetRow.Cells[8].ToString();
                md.Comuna               = sheetRow.Cells[9].ToString();
                md.CodigoPostal         = Convert.ToInt32(sheetRow.Cells[10].ToString());
                md.RutDni               = sheetRow.Cells[11].ToString();
                md.DescripcionEnvio     = sheetRow.Cells[12].ToString();
                md.Precio               = Convert.ToInt32(sheetRow.Cells[13].ToString());
                lstManifiestoExcel.Add(md);
            }
            await _context.SaveChangesAsync();
        }
        else
        {
            ViewBag.Message = "Empty File Upload Failed";
        }
    }
    catch (Exception ex)
    {
        ViewBag.Message = "File Upload Failed";
    }
    return View(await _context.Manifiestos.ToListAsync());
}

This code works fine when there are few records. The problem is generated, when already in production, there are many records, since it gets stuck...

In Python, I have done tests to record in a database in Heroku, 30,000 records of this type, and the load takes less than 10 seconds. I tried to run the Python script from a .NET Core application, but I didn't have very good results, because with this method you can't use libraries like NumPy or Pandas.

Is there a way to do a bulk insert in PostgreSQL from .NET Core? I have looked for examples, but they only appear for SQL Server.

Hambone · Answer

For what it's worth, if you have a lot of records and speed is your primary concern, I have found there is no faster way to execute this than using an export/copy, meaning export the file to CSV using Native Excel's capability (lightning fast compared to iterating rows) and Postgres' copy to command (very fast compared to row-by-row inserts). I even go one step further and gzip the file before sending it to the server to minimize network impact.

This is interop... rarely associated with speed, but I'm telling you Excel can turn a worksheet into a CSV faster than any third party package.

The code below is a simplified version of what I do, can you can declare any range. This is helpful because if your sheet has data outside of what you want to upload, then you can't use the native export to CSV. The code copies the range to a blank sheet (and later deletes it) to enable that.

Save file to CSV and compress:

Excel.Range range = excel.Selection;
Excel.Workbook wb = excel.Workbooks.Add();
Excel.Worksheet ws = wb.Worksheets[1];

range.Copy();
ws.get_Range("A1").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats);
excel.DisplayAlerts = false;
wb.SaveAs(Path.Combine(_Outputdir, string.Format("{0}.csv", TableName)),
    Excel.XlFileFormat.xlCSV);
wb.Close();
excel.DisplayAlerts = true;

// Pick your favorite compress method -- this is optional
string newFile = Commons.Compress(_Outputdir, string.Format("{0}.csv", TableName));

Send compressed CSV file to the Pg server and run the copy:

// Send this to the server however you normally would
Commons.FtpPut(newFile, _Outputdir);

NpgsqlTransaction trans = PgConnection.BeginTransaction(IsolationLevel.RepeatableRead);

if (TruncateTable)
{
    cmd = new NpgsqlCommand(string.Format("truncate table {0}", TableName),
        PgConnection, trans);
    cmd.ExecuteNonQuery();
}

try
{
    cmd.CommandText = string.Format(
        "copy {0} from program 'gzip -dc /apps/external_data/inbound/{0}.csv.gz' " +
        "with null as '' csv header encoding 'WIN1250'", TableName);

    cmd.ExecuteNonQuery();
    trans.Commit();
}
catch (Exception ex)
{
    // If the copy fails, roll back the truncate
    trans.Rollback();
}

PgConnection.Close();

// Clean up after yourself
Commons.FtpDelete(newFile, _Outputdir);

This does presuppose you have the ability to access the server and run copy, which is a superuser function. If you can't do those, then you can replace this with a local copy (well supported on Npgsql), but that approach will be much different.

This is my compress method, in case you want to use it:

public static string Compress(String Directory, String FileName)
{
    string newFileName = string.Format("{0}.gz", FileName);

    using (FileStream originalFileStream = File.Open(Path.Combine(Directory, FileName), FileMode.Open))
        using (FileStream compressedFileStream = File.Create(Path.Combine(Directory, newFileName)))
            using (GZipStream compressionStream = new GZipStream(compressedFileStream, CompressionMode.Compress))
                originalFileStream.CopyTo(compressionStream);

    return newFileName;
}

Export an Excel document to a PostgreSQL database in C#

1 Answers