SqlBulkCopy mapping issue with fullstops in column names

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.

The error is clear and has nothing to do with Excel. Your mapping says that there is a column named No. in your Exceldt which doesn't actually exists. Did you check the DataTable's column names? If it works without the fullstops, it means that the column names do not have fullstops — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 11, 2017 at 7:24
@PanagiotisKanavos, thanks for the quick response, My question mean, the column name in Excel sheet also ends with fullstop then this error is coming. If I rename column name manually & remove fullstop from both (excel sheet & column mapping) error goes. — Kiran Malvi
– Kiran Malvi, Commented May 11, 2017 at 7:28
You are working with a DataTable, not a sheet. What are the DataTable's column names? How did you create it anyway? If you want to ask why the DataColumn doesn't have a fullstop, post the code you used to load the DataTable — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 11, 2017 at 7:30
BTW, SqlBulkCopy also works with data readers. You could read the sheet data and post them to SQL Server directly without loading everything in memory first. How are you loading the Excel sheet? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 11, 2017 at 7:34

Azar Shaikh · Accepted Answer · 2017-05-11 09:03:05Z

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

Collectives™ on Stack Overflow

SqlBulkCopy mapping issue with fullstops in column names

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related