Click here to Skip to main content
12,446,296 members (54,457 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#2.0 C#
When getting data from excelsheet to C# dataset :
I am using

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO;\"";

and

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO;\"";

I have to make general formatting for column(s), so that able to update Database as integer column.

Ex

12,220 --> 12220
15,445 --> 15445
55,881 --> 55881

Number(-1,234) --> Number(General)

I don't want fix(s) by row (by) row operation

it will degrade performance
Posted 11-Oct-12 0:18am
vino20121.4K
Updated 11-Oct-12 0:19am
v2
Comments
JJ Raj 11-Oct-12 20:53pm
   
Have you tried string.Format ?
vino2012 12-Oct-12 4:03am
   
If I am doing string.format, I have to go [row][column]
Expecting performance, Excel sheet contains around 6 Lakhs records

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Formatting has nothing to do with importing data from MS Excel sheet.

Read this: Import Excel File to DataSet[^]

If you use: Provider=Microsoft.ACE.OLEDB.12.0 driver, the Extended Properties can NOT use Excel 8.0 value.

More about it, you'll find at: http://www.connectionstrings.com/excel[^] and http://www.connectionstrings.com/excel-2007[^]
Read carefuly about IMEX option Wink | ;)

But, if you got trouble with separator for thousends and decimals, try to manually change it[^] or use CultureInfo (System.Globalization)[^] class to change it programmatically (to imitate other settings).
  Permalink  
Comments
vino2012 12-Oct-12 4:06am
   
Thanks, I fixed by removing IMEX=1. Default its IMEX=0 checking first 8 rows of data and it deciding the column type and also removing Excel format.
Maciej Los 12-Oct-12 10:59am
   
Thank you for accepting solution ;)
If my solution was helpful, please, rate it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160811.3 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100