SQL Bulk Insert Problem while inserting excel data

Question

Hi guys,

In my Blazor Server application, I am trying to insert excel data into a SQL database table. I am getting this error, but couldn't figure out why. Hope you can help me.

Error:

The given ColumnName 'PID' does not match up with any column in data source.

Here is the sample excel, I am trying to bulk insert.

CREATE TABLE [dbo].[RazerReconciliation](
	[PID] [nvarchar](50) NULL,
	[ProductName] [nvarchar](200) NULL,
	[ProductAmount] [float] NULL,
	[ReferenceNo] [uniqueidentifier] NOT NULL,
	[Quantity] [int] NOT NULL
) ON [PRIMARY]
GO

public async Task UploadReconciliationFile(IFormFile file)
    {
        var untrustedFileName = file.FileName;
        
       
        try
        {
            var path = Path.Combine(env.ContentRootPath,
                env.EnvironmentName, "unsafe_uploads",
                untrustedFileName);

            await using FileStream fs = new(path, FileMode.Create);
            await file.CopyToAsync(fs);

            logger.LogInformation("{untrustedFileName} saved at {Path}",
                untrustedFileName, path);
            
            var sqlConnectionString = configuration["ConnectionStrings:DefaultConnection"];

            // Get the datatable from procedure on Utility.cs page
            var datapush = Utility.Utility.ImportToDataTable(path, "Sheet1");

            // open connection to sql and use bulk copy to write excelData to my table
            await using var destinationConnection = new SqlConnection(sqlConnectionString);
            destinationConnection.Open();
            using var bulkCopy = new SqlBulkCopy(destinationConnection);
            bulkCopy.DestinationTableName = "RazerReconciliation";
            bulkCopy.ColumnMappings.Add("PID", "PID");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");
            bulkCopy.ColumnMappings.Add("ProductAmount", "ProductAmount");
            bulkCopy.ColumnMappings.Add("ReferenceNo", "ReferenceNo");
            bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
            await bulkCopy.WriteToServerAsync(datapush);
        }
        catch (Exception ex)
        {
            logger.LogError("{untrustedFileName} error on upload (Err: 3): {Message}",
                untrustedFileName, ex.Message);

        }
    }


public class Utility
    {
        public static DataTable ImportToDataTable(string filePath, string sheetName)
        {
            var dt = new DataTable();
            var fi = new FileInfo(filePath);
            // Check if the file exists
            if (!fi.Exists)
                throw new Exception("File " + filePath + " Does Not Exists");

            // If you use EPPlus in a noncommercial context
            // according to the Polyform Noncommercial license:
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var xlPackage = new ExcelPackage(fi);
            // get the first worksheet in the workbook
            var worksheet = xlPackage.Workbook.Worksheets[sheetName];

            // Fetch the WorkSheet size
            ExcelCellAddress startCell = worksheet.Dimension.Start;
            ExcelCellAddress endCell = worksheet.Dimension.End;

            // create all the needed DataColumn
            for (var col = startCell.Column; col <= endCell.Column; col++)
                dt.Columns.Add(col.ToString());

            // place all the data into DataTable
            for (var row = startCell.Row; row <= endCell.Row; row++)
            {
                DataRow dr = dt.NewRow();
                int x = 0;
                for (int col = startCell.Column; col <= endCell.Column; col++)
                {
                    dr[x++] = worksheet.Cells[row, col].Value;
                }

                dt.Rows.Add(dr);
            }

            return dt;
        }
    }

Answer 1

Hello @cenk

Your ImportToDataTable method return wrong Column name in Datatable. EX: 1,2,3,4,5,

and PID, ProductName and other column is consider as Row data. Which is wrong and create the issue while ColumnMappings.

Replace your Utility class to

public class Utility
{
    public static DataTable ImportToDataTable(string filePath, string sheetName, bool hasHeader = true)
    {
        var dt = new DataTable();
        var fi = new FileInfo(filePath);
        // Check if the file exists
        if (!fi.Exists)
            throw new Exception("File " + filePath + " Does Not Exists");

        // If you use EPPlus in a noncommercial context
        // according to the Polyform Noncommercial license:
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using var xlPackage = new ExcelPackage(fi);
        // get the first worksheet in the workbook
        var worksheet = xlPackage.Workbook.Worksheets[sheetName];

        // create all the needed DataColumn
        foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
        {
            
 if (firstRowCell.Text == "ReferenceNo")
                {
                    dt.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column), typeof(Guid));
                }
                else
                {
                    dt.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
                }
        }

        var startRow = hasHeader ? 2 : 1;

        // place all the data into DataTable
        for (int rowNum = startRow; rowNum <= worksheet.Dimension.End.Row; rowNum++)
        {
            var wsRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column];
            DataRow row = dt.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
        }

        return dt;
    }

It will give you the correct column name in Datatable.

Hope this helps

---

If it does, please accept the answer.

Thanks

Naimish Makwana

Answer 2

I think this works;

public static DataTable ImportToDataTable(string filePath, string sheetName)
        {
            var dt = new DataTable();
            var fi = new FileInfo(filePath);
            // Check if the file exists
            if (!fi.Exists)
                throw new Exception("File " + filePath + " Does Not Exists");

            // If you use EPPlus in a noncommercial context
            // according to the Polyform Noncommercial license:
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var xlPackage = new ExcelPackage(fi);
            // get the first worksheet in the workbook
            var worksheet = xlPackage.Workbook.Worksheets[sheetName];

            dt = worksheet.Cells["A1:E37"].ToDataTable(c =>
            {
                
                c.FirstRowIsColumnNames = true;
                c.Mappings.Add(3, "ReferenceNo", typeof(Guid), false, cellVal => new Guid(cellVal.ToString()));
                

            });

           return dt;
        }

Answer 3

But I need to set Cells not manually, but how?

dt = worksheet.Cells["A1:E37"].ToDataTable(c =>

Answer 4

This is what I need, hope helps someone else.

public static DataTable ImportToDataTable(string filePath, string sheetName)
        {
            var dt = new DataTable();
            var fi = new FileInfo(filePath);
            // Check if the file exists
            if (!fi.Exists)
                throw new Exception("File " + filePath + " Does Not Exists");

            // If you use EPPlus in a noncommercial context
            // according to the Polyform Noncommercial license:
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var xlPackage = new ExcelPackage(fi);
            // get the first worksheet in the workbook
            var worksheet = xlPackage.Workbook.Worksheets[sheetName];

            dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
            {
                
                c.FirstRowIsColumnNames = true;
                // Ensure that the OrderDate column is casted to DateTime (in Excel it can sometimes be stored as a double/OADate)
                c.Mappings.Add(3, "ReferenceNo", typeof(Guid), false, cellVal => new Guid(cellVal.ToString()));
                

            });

            return dt;
        }

Answer 5

@Cenk, Welcome to Microsoft Q&A, I am glad to hear that you problem has been solved, you could also set the data type for the GUID column individually.

Like the following:

 foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
            {
                dt.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }
            dt.Columns["ReferenceNo"].DataType = typeof(Guid);

Based on my test, it also works well.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.