 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2004importing Excel worksheet problem
I'm importing an Excel 2002 worksheet into an Access 2002 table using the menus. The Excel spreadsheet has 4 columns all formatted as text, and one text column is all numbers. Access SHOULD import this as text and it does, but for some reason it converts the text numbers to E- notation first, then converts back to text so I now have a bunch of E-notation numbers in a text field in the Access table. Anybody know how to fix this without having to pad the text number column with alphabetic characters before importing? Jeff Insert a quotation mark ( ' ) at the beginning of each cell's value in the number column. That will tell ACCESS to import the value as text.  SignatureKen Snell <MS ACCESS MVP> > I'm importing an Excel 2002 worksheet into an Access 2002 > table using the menus. The Excel spreadsheet has 4 [quoted text clipped - 8 lines] > > Jeff Add a dummy row of data to row 1. For the column that is all numbers use alphabetioc data for the dummy row. e.g. "ABC" When Access looks at the first 8 rows and "guesses" the datatype it will guess Text for that column. SignatureJoe Fallon Access MVP > I'm importing an Excel 2002 worksheet into an Access 2002 > table using the menus. The Excel spreadsheet has 4 [quoted text clipped - 8 lines] > > Jeff > When Access looks at the first 8 rows and "guesses" the datatype it will > guess Text for that column. Note it is Jet, rather than MS Access, that scans the rows. Making the correct distinction in this case would help the OP locate the relevant settings. For more details see: http://www.dicks-blog.com/excel/2004/06/external_data_m.html Jamie. -- I am aware of the difference. IMO it does not make a whit of difference to the OPs problem. SignatureJoe Fallon Access MVP > > When Access looks at the first 8 rows and "guesses" the datatype it will > > guess Text for that column. [quoted text clipped - 8 lines] > > -- > I am aware of the difference. > IMO it does not make a whit of difference to the OPs problem. This is why I think it matters: 1) Excel is a Jet data source. This is all about how the Excel data presents itself via Jet. The fact that MS Access also uses Jet is merely a coincidence in this case. 2) Knowing it is a Jet process would give the OP a chance of finding the relevant registry settings. Look in the registry under MS Access and they'll never be found. 3) By saying 'Access looks at the first 8 rows', it makes it look like you actually think it is MS Access doing the scanning. People in these ngs put a lot of faith in MVPs; it's important you get the correct technical information across, rather than not caring a whit when you get it wrong. If you truly are aware of the distinction, then make it clear in your posts. Jamie. -- I just confused as to why Access converts the numbers when the cells' data types are defined as text. Why does is the text data type ignored during the import? Jeff > Add a dummy row of data to row 1. > For the column that is all numbers use alphabetioc data for the dummy row. [quoted text clipped - 13 lines] > > > > Jeff "Yours is not to question why. Yours is but to do or die." I already explained why it ignores it. Becasue they are two differnet programs and one has to "guess" what the other means. The guess is based on the data in the first 8 rows or so of data in Excel. (can be changed in the registry though). So if you had simply tried my suggestion you would have seen that it works. SignatureJoe Fallon Access MVP > I just confused as to why Access converts the numbers when the cells' data > types are defined as text. Why does is the text data type ignored during [quoted text clipped - 19 lines] > > > > > > Jeff > I just confused as to why Access converts the numbers when the cells' data > types are defined as text. Why does is the text data type ignored during > the import? Did you read: http://www.dicks-blog.com/excel/2004/06/external_data_m.html "Data type is determined column by column. 'Majority Type' means a certain number of rows ... in each column are scanned and the data types are counted. Both a cell's value and format are used to determine data type. The majority data type (i.e. the one with the most rows) decides the overall data type for the entire column. There's a bias in favor [of] numeric in the event of a tie." Jamie. -- Ok, so if all the cells are in the text format, why would ANY of the cells be converted to E-notation? No one single cell in the Excel spreadsheet is defined as a number. > > I just confused as to why Access converts the numbers when the cells' data > > types are defined as text. Why does is the text data type ignored during [quoted text clipped - 14 lines] > > -- > Ok, so if all the cells are in the text format, why would ANY of the cells > be converted to E-notation? No one single cell in the Excel spreadsheet is > defined as a number. I think there must be something else going on e.g. your columns are not formatted Text. Here's some steps to reproduce (execute individually from any blank query): CREATE TABLE [Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation ( MyDataCol VARCHAR(35) ) ; INSERT INTO [Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation (MyDataCol) VALUES ('12345678901234567890') ; INSERT INTO [Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation (MyDataCol) VALUES ('0.1234567890123456789') ; SELECT MIN(LEN(MyDataCol)) AS shortest_value FROM [Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation ; I get shortest_value = 20, proving the value was not converted to e-notation and has not been curtailed. Jamie. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.