Excel data types

[Charlie]

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

 

[Nick Hodge]

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Charlie]

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