SqlBulkCopy mapping issue with fullstops in column names

c# sqlbulkcopy sql-server


I'm trying to import Excel sheet to SQL Server database. The issue happening is with the column mapping. If the Column Name in Excel Sheet ends with fullstop (eg: 'No.', 'Name.'), C# is throwing an exception

Message=The given ColumnName 'No.' does not match up with any column in data source.

But if I remove fullstop, it is working absolutely fine.

The source code for mapping in C# is as follows

     private void InsertExcelRecords()
    string FilePath = "C:\\Upload\\" + FileUpload.FileName;
    string fileExtension = Path.GetExtension(FileUpload.PostedFile.FileName);
    FileUpload.SaveAs("C:\\Upload\\" + FileUpload.FileName);

    ExcelConn(FilePath, fileExtension);
    DataTable dtExcelSheetName = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();

    Query = string.Format("Select * FROM [{0}]", getExcelSheetName + "A7:I");

    OleDbCommand Ecom = new OleDbCommand(Query, Econ);

    DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
    DataTable Exceldt = ds.Tables[0];
    SqlBulkCopy objbulk = new SqlBulkCopy(con);   
    objbulk.DestinationTableName = "BankTransaction";
    objbulk.ColumnMappings.Add("No", "Number");  

Please let me know if you need any more information.

Accepted Answer

You will not be able to retrieve column names with . from an excel sheet using OLEDB or ODBC. Because it is not a valid or recognizable syntax.

'.' typically we use it to distinguish between two [schema].[table].[column] like that.

OLEDB,ODBC Replace column name '.' char with '#'

So you need to replace your code

objbulk.ColumnMappings.Add("No.", "Number")


objbulk.ColumnMappings.Add("No#", "Number")

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow