Question Bulk Insert Problem - inserting excel data

[raysefo]

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.

Here is the table;

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:

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;
        }
    }

 

[Skydiver]

What are the return the values of the col.ToString() on line 67?

 

[Skydiver]

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".

 

[raysefo]

What are the return the values of the col.ToString() on line 67?

They are column numbers 1,2,3

There are no spaces before and after PID by the way.

 

[raysefo]

I changed my code as follows:

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.

 

[cjard]

What is the type of the column in the datatable ?

 

[raysefo]

uniqueidentifier

 

[raysefo]

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;
        }

 

[raysefo]

But I need to set Cells not manually, but how?
dt = worksheet.Cells["A1:E37"].ToDataTable(c =>

 

[cjard]

uniqueidentifier

There is no such data type in C#; your System.Data.DataTable cannot possibly have a DataColumn whose .DataType is "uniqueidentifier"

 

[raysefo]

This works for me 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;
        }

 

[cjard]

Comment on line 20 seems to disagree with code on line 21