By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,128 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Read integer value from Excel file failed

P: n/a
Hi all,
I have following code to read Excel content into a DataSet

string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
OleDbConnection conn = new OleDbConnection(connection);

try {
DataSet ds = new DataSet();
conn.Open();
OleDbDataAdapter oAdapter = new OleDbDataAdapter("select * from
[Main$]", conn);
oAdapter.Fill(ds);
conn.Close();
} catch {
}

One of the column is phone number, and some cells of this column are marked
as "The number in this cell is formatted as text or proceeded by
apostrophe", I can find a small green triangle at top-left corner of the
cell, then values from these cells in DataSet are blank

Is there any trick how to write connection string or how to read Excel file?

Thanks!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
OK, I found the solution.
I have a big Excel file with about 5000 rows. The mix of data type of this
column starts from 26th line. I modified the setting of resistry
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows to
change the value from default 8 to 0, which means to make ADO to scan all
column values before choosing the appropriate data type.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:OB**************@TK2MSFTNGP11.phx.gbl...
Hi all,
I have following code to read Excel content into a DataSet

string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
OleDbConnection conn = new OleDbConnection(connection);

try {
DataSet ds = new DataSet();
conn.Open();
OleDbDataAdapter oAdapter = new OleDbDataAdapter("select * from
[Main$]", conn);
oAdapter.Fill(ds);
conn.Close();
} catch {
}

One of the column is phone number, and some cells of this column are
marked as "The number in this cell is formatted as text or proceeded by
apostrophe", I can find a small green triangle at top-left corner of the
cell, then values from these cells in DataSet are blank

Is there any trick how to write connection string or how to read Excel
file?

Thanks!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy

Nov 23 '05 #2

P: n/a
Put the duct tape away and fix the data. If you have mixed data in the
column then just change the data type to text for all of it.

--

Derek Davis
dd******@gmail.com

"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
OK, I found the solution.
I have a big Excel file with about 5000 rows. The mix of data type of this
column starts from 26th line. I modified the setting of resistry
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
to change the value from default 8 to 0, which means to make ADO to scan
all column values before choosing the appropriate data type.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:OB**************@TK2MSFTNGP11.phx.gbl...
Hi all,
I have following code to read Excel content into a DataSet

string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
OleDbConnection conn = new OleDbConnection(connection);

try {
DataSet ds = new DataSet();
conn.Open();
OleDbDataAdapter oAdapter = new OleDbDataAdapter("select * from
[Main$]", conn);
oAdapter.Fill(ds);
conn.Close();
} catch {
}

One of the column is phone number, and some cells of this column are
marked as "The number in this cell is formatted as text or proceeded by
apostrophe", I can find a small green triangle at top-left corner of the
cell, then values from these cells in DataSet are blank

Is there any trick how to write connection string or how to read Excel
file?

Thanks!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy


Nov 23 '05 #3

P: n/a
Yes, but we have no control over the files submitted by clients over
internet.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"carion1" <dd******@gmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Put the duct tape away and fix the data. If you have mixed data in the
column then just change the data type to text for all of it.

--

Derek Davis
dd******@gmail.com

"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
OK, I found the solution.
I have a big Excel file with about 5000 rows. The mix of data type of
this column starts from 26th line. I modified the setting of resistry
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
to change the value from default 8 to 0, which means to make ADO to scan
all column values before choosing the appropriate data type.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:OB**************@TK2MSFTNGP11.phx.gbl...
Hi all,
I have following code to read Excel content into a DataSet

string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
OleDbConnection conn = new OleDbConnection(connection);

try {
DataSet ds = new DataSet();
conn.Open();
OleDbDataAdapter oAdapter = new OleDbDataAdapter("select * from
[Main$]", conn);
oAdapter.Fill(ds);
conn.Close();
} catch {
}

One of the column is phone number, and some cells of this column are
marked as "The number in this cell is formatted as text or proceeded by
apostrophe", I can find a small green triangle at top-left corner of the
cell, then values from these cells in DataSet are blank

Is there any trick how to write connection string or how to read Excel
file?

Thanks!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy



Nov 23 '05 #4

P: n/a
Jan
Hi Hardy,

If your customers use mixed data types, you should not rely on OleDb to
recognize column data type. Instead, use some approach that allows you to
access worksheet cells directly and without presumed data type. Automation
is one option, another one is using some third-party component like
ExcelLite (free if you need less than 150 rows).

For comparison, see: http://www.gemboxsoftware.com/ExcelLite.htm#Automation

Jan
GemBox Software

"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:uH**************@TK2MSFTNGP10.phx.gbl...
Yes, but we have no control over the files submitted by clients over
internet.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"carion1" <dd******@gmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Put the duct tape away and fix the data. If you have mixed data in the
column then just change the data type to text for all of it.

--

Derek Davis
dd******@gmail.com

"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
OK, I found the solution.
I have a big Excel file with about 5000 rows. The mix of data type of
this column starts from 26th line. I modified the setting of resistry
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
to change the value from default 8 to 0, which means to make ADO to scan
all column values before choosing the appropriate data type.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
"Hardy Wang" <ha*******@newsgroups.nospam> wrote in message
news:OB**************@TK2MSFTNGP11.phx.gbl...
Hi all,
I have following code to read Excel content into a DataSet

string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
OleDbConnection conn = new OleDbConnection(connection);

try {
DataSet ds = new DataSet();
conn.Open();
OleDbDataAdapter oAdapter = new OleDbDataAdapter("select * from
[Main$]", conn);
oAdapter.Fill(ds);
conn.Close();
} catch {
}

One of the column is phone number, and some cells of this column are
marked as "The number in this cell is formatted as text or proceeded by
apostrophe", I can find a small green triangle at top-left corner of
the cell, then values from these cells in DataSet are blank

Is there any trick how to write connection string or how to read Excel
file?

Thanks!

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy



Nov 29 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.