Question Bulk Insert Problem - inserting excel data

raysefo

Well-known member
Joined
Feb 22, 2019
Messages
362
Programming Experience
10+
Hi there,
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.

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.
Ekran görüntüsü 2023-01-18 144031.png


Here is the table;
SQL:
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

Here is the code:
C#:
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;
        }
    }
 
What are the return the values of the col.ToString() on line 67?
 
It might be my astigmatism, but to me it looks "PID" is not centered under the "A". That might suggest that there is a trailing space or two after "PID".
1674050031464.png
 
I changed my code as follows:
C#:
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
            var startCell = worksheet.Dimension.Start;
            var endCell = worksheet.Dimension.End;

            var options = ToDataTableOptions.Create();
            options.FirstRowIsColumnNames = true;
            dt = worksheet.Cells["A1:E37"].ToDataTable(options);

            return dt;
        }

getting this error:
The given value '7e1bf43c-d521-40e4-be9d-951d29f90af5' of type String from the data source cannot be converted to type uniqueidentifier for Column 3 [ReferenceNo] Row 1.

I need to convert it to guid.
 
I think this works;
C#:
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;
        }
 
This works for me hope helps someone else

C#:
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;
        }
 

Latest posts

Back
Top Bottom