Read integer value from Excel file failed

  • Thread starter Thread starter Hardy Wang
  • Start date Start date
H

Hardy Wang

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!
 
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.
 
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
(e-mail address removed)

Hardy Wang said:
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 said:
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!
Click to expand...
Click to expand...
 
Yes, but we have no control over the files submitted by clients over
internet.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
carion1 said:
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
(e-mail address removed)

Hardy Wang said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Yes, but we have no control over the files submitted by clients over
internet.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
carion1 said:
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
(e-mail address removed)

Hardy Wang said:
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
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top