Saaharjit Singh Saaharjit Singh - 6 months ago 26
ASP.NET (C#) Question

How to trim characters of excel column before uploading in database

I am upload an excel file onto my database. i have an INVOICE column in the excel sheet. i want to store only the first 10 digits of that column into the database.

for example

Excel file -

Invoice

123354647478483

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

1233546474

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))
{
oda.Fill(dtExcelData);
}
excel_con.Close();

Answer Source

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

to

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

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

to

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