Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data types
Hi folks,
First, I'm not completly sure this is the correct group for posting this, but I figure if anyone would know an answer, they'd probably be looking in this group. Anyway, I'm trying to import Excel data into a .NET dataset using c#. I've created the connection string using the IMEX=1 extended property to treat mixed columns as text. So, now the numbers in the mixed columns that have leading zeros come in correctly (e.g. '0210' comes in as '0210' instead of '210'), but the numbers without leading zeros don't return anything (e.g. '2101' returns '' or a null value). Does anyone know of a way to get columns of data such as this in correctly? Everything I read (even the MS KB article related to this issue) state that it will treat the mixed-type columns as text, but it doesn't seem to mention this little quirk. Thanks for your help, Charlie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data types
Charlie
Strange. the only sure way of retaining zeroes in excel is to pre-format the destination cells as text or pre-fix with a leading apostrophe. (Not sure if you can use either of these routes. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "Charlie" wrote in message ... Hi folks, First, I'm not completly sure this is the correct group for posting this, but I figure if anyone would know an answer, they'd probably be looking in this group. Anyway, I'm trying to import Excel data into a .NET dataset using c#. I've created the connection string using the IMEX=1 extended property to treat mixed columns as text. So, now the numbers in the mixed columns that have leading zeros come in correctly (e.g. '0210' comes in as '0210' instead of '210'), but the numbers without leading zeros don't return anything (e.g. '2101' returns '' or a null value). Does anyone know of a way to get columns of data such as this in correctly? Everything I read (even the MS KB article related to this issue) state that it will treat the mixed-type columns as text, but it doesn't seem to mention this little quirk. Thanks for your help, Charlie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel data types
Well, that's the easy part. I used the IMEX=1 in my connection string to
treat the column as text. Apparently, the oledb driver scans the first 8 rows to determine the data type of a column. For my spreadsheet, the first 8 rows contain text, so it interprets the column as text and the leading zeros remain. My problem is that numbers that do not have the leading zeros are ommitted in the import operation and I get nothing (most likely null values, but I haven't tested it yet), I loaded the values into a grid and the cells are empty. Thanks, Charlie "Nick Hodge" wrote: Charlie Strange. the only sure way of retaining zeroes in excel is to pre-format the destination cells as text or pre-fix with a leading apostrophe. (Not sure if you can use either of these routes. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.excelusergroup.org web: www.nickhodge.co.uk "Charlie" wrote in message ... Hi folks, First, I'm not completly sure this is the correct group for posting this, but I figure if anyone would know an answer, they'd probably be looking in this group. Anyway, I'm trying to import Excel data into a .NET dataset using c#. I've created the connection string using the IMEX=1 extended property to treat mixed columns as text. So, now the numbers in the mixed columns that have leading zeros come in correctly (e.g. '0210' comes in as '0210' instead of '210'), but the numbers without leading zeros don't return anything (e.g. '2101' returns '' or a null value). Does anyone know of a way to get columns of data such as this in correctly? Everything I read (even the MS KB article related to this issue) state that it will treat the mixed-type columns as text, but it doesn't seem to mention this little quirk. Thanks for your help, Charlie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Data Types | Excel Programming | |||
Data types Cells | Excel Programming | |||
Data types when importing Excel data to SQLServer | Excel Discussion (Misc queries) |