Saaharjit Singh Saaharjit Singh - 2 years ago 74
ASP.NET (C#) Question

How to trim characters of excel column before uploading to database

I am uploading an excel file to my database. I have an

column in the excel sheet. I want to store only the first 10 digits of that column into the database.

For example:

Excel file



I want to trim this number and store only the first 10 digits in the database like

string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();

dtExcelData.Columns.AddRange(new DataColumn[10] {
new DataColumn("Invoice#", typeof(string)),
new DataColumn("P#O##", typeof(string)),
new DataColumn("Line#", typeof(int)),
new DataColumn("Invoice Date",typeof(DateTime)),
new DataColumn("Gross", typeof(string)),
new DataColumn("Disc", typeof(string)),
new DataColumn("NET", typeof(string)),
new DataColumn("Date Pd", typeof(string)),
new DataColumn("Check#/Doc#", typeof(string)),
new DataColumn("Additional Info", typeof(string))

using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

Answer Source

Change your query "SELECT * FROM [" + sheet1 + "]"


"SELECT LEFT(Invoice#, 10) as NewInvoice, * FROM [" + sheet1 + "]"

and change new DataColumn("Invoice#", typeof(string)),


new DataColumn("NewInvoice", typeof(string)),

Edit: maybe you would need to mention all the required columns in the Select statement when doing this.

While using column names in Select statement use [ & ] for column names with spaces and special characters. Thus query will look like:

SELECT Left([Invoice#], 10) as NewInvoice, [P#O##],[Line#],[Invoice Date],Gross,Disc,NET,[Date Pd],[Check#/Doc#],[Additional Info] FROM [" + sheet1 + "]"

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download