CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
Kiran Malvi
Kiran Malvi

Reputation: 636

SqlBulkCopy mapping issue with fullstops in column names

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.

Upvotes: 0

Views: 493

Answers (1)

Azar Shaikh
Azar Shaikh

Reputation: 449

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

Upvotes: 3

Related Questions