SqlBulkCopy mapping issue with fullstops in column names

c# sqlbulkcopy sql-server

Question

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);
    Econ.Open();
    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);
    Econ.Close();
    oda.Fill(ds);
    DataTable Exceldt = ds.Tables[0];
    connection();  
    SqlBulkCopy objbulk = new SqlBulkCopy(con);   
    objbulk.DestinationTableName = "BankTransaction";
    objbulk.ColumnMappings.Add("No", "Number");  
    con.Open();
    objbulk.WriteToServer(Exceldt);
    con.Close();
}

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

with

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why