|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Read integer value from Excel file failedI 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. Show quote "Hardy Wang" <hardywang@newsgroups.nospam> wrote in message news:OBxt2E67FHA.4036@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 > 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. -- Show quoteDerek Davis ddavi***@gmail.com "Hardy Wang" <hardywang@newsgroups.nospam> wrote in message news:%23faY4W67FHA.3804@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" <hardywang@newsgroups.nospam> wrote in message > news:OBxt2E67FHA.4036@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 >> > > Yes, but we have no control over the files submitted by clients over
internet. Show quote "carion1" <ddavi***@gmail.com> wrote in message news:%235pHwy67FHA.2576@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 > ddavi***@gmail.com > > "Hardy Wang" <hardywang@newsgroups.nospam> wrote in message > news:%23faY4W67FHA.3804@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" <hardywang@newsgroups.nospam> wrote in message >> news:OBxt2E67FHA.4036@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 >>> >> >> > > 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 Show quote "Hardy Wang" <hardywang@newsgroups.nospam> wrote in message news:uHdi6dD8FHA.3044@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" <ddavi***@gmail.com> wrote in message > news:%235pHwy67FHA.2576@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 >> ddavi***@gmail.com >> >> "Hardy Wang" <hardywang@newsgroups.nospam> wrote in message >> news:%23faY4W67FHA.3804@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" <hardywang@newsgroups.nospam> wrote in message >>> news:OBxt2E67FHA.4036@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 >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics