Reading Excel into a dataset

[Bill]

I've been reading excel worksheets into a dataset using the following
prose

OleDbDataAdapter myCommand =
new OleDbDataAdapter(
"SELECT * FROM [MySheet$]",
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + input_file +
@";Extended Properties=""Excel 8.0;IMEX=1""");

The problem of course is the types for the columns are getting hosed
because of the mixed values. I know about the eight rows, etc, but I
have to wonder if there is anyway to force the issue regarding column
types so that the OLEDB reader won't load nulls. Seems kind of silly
to have it guess. Also the doc alludes to MAXSCANROWS=0 to scan all
the rows but that didn't seem to help.

Any ideas?

Bill

 

[Paul Clement]

On 15 Apr 2004 21:31:00 -0700, (e-mail address removed) (Bill) wrote:

¤ I've been reading excel worksheets into a dataset using the following
¤ prose
¤
¤ OleDbDataAdapter myCommand =
¤ new OleDbDataAdapter(
¤ "SELECT * FROM [MySheet$]",
¤ @"Provider=Microsoft.Jet.OLEDB.4.0;" +
¤ @"Data Source=" + input_file +
¤ @";Extended Properties=""Excel 8.0;IMEX=1""");
¤
¤ The problem of course is the types for the columns are getting hosed
¤ because of the mixed values. I know about the eight rows, etc, but I
¤ have to wonder if there is anyway to force the issue regarding column
¤ types so that the OLEDB reader won't load nulls. Seems kind of silly
¤ to have it guess. Also the doc alludes to MAXSCANROWS=0 to scan all
¤ the rows but that didn't seem to help.
¤

The IMEX=1 argument should resolve this problem so I'm surprised that it isn't working. Have you
tried using an OleDbConnection object instead of placing the connection string in the SQL statement.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Kevin Yu [MSFT]]

Thanks for Paul's quick response.

Hi Bill,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when reading from an Excel worksheet,
some of the values were null while the actual values are not null. If there
is any misunderstanding, please feel free to let me know.

Based on the document, generally, there are two workarounds for this
behavior:

1. Insure that the data in Excel is entered as text. Just reformatting the
Excel column to Text will not accomplish this. You must re-enter the
existing values after reformatting the Excel column. In Excel, you can use
F5 to re-enter existing values in the selected cell.

2. You can add the option IMEX=1; to the Excel connect string in the
OpenDatabase method.

I'm quite surprised that IMEX=1 didn't resolve the problem. Could you
please try another workaround to see if it works? For more information,
please check the following KB article.

http://support.microsoft.com/default.aspx?scid=kb;en-us;194124

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Bill,

I'd like to know if this issue has been resolved yet. Is there anything
that I can help. I'm still monitoring on it. If you have any questions,
please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Serhiy Snisarenko]

Hi Kevin,

Could you please explain how can I re-enter the existing values using
F5 in Excel? When I use this key I get the 'Go To ' option. I can
re-enter data cell by cell but I have several thousand records in my
spreadsheet. Is it possible to select all numeric cells in particular
column and re-enter them in one step?

Thank you,

Serhiy