If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Import from Excel
When I import from an Excel into Access 2003 table, in one field (Text field)
the project numbers appear in a scientific format with exponents. The data in the field are all numeric but I want the field to be defined as a Text. Is there a way to avoid the scientific formats. Surprisingly sometimes it worked fine but I do not know when. Can somebody help ? Thanks a lot dan |
#2
|
|||
|
|||
Import from Excel
Dan,
The first thing I would check is is the column wide enough. Go to the table and expland the column the same as you would in Excel. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Dan" wrote in message ... When I import from an Excel into Access 2003 table, in one field (Text field) the project numbers appear in a scientific format with exponents. The data in the field are all numeric but I want the field to be defined as a Text. Is there a way to avoid the scientific formats. Surprisingly sometimes it worked fine but I do not know when. Can somebody help ? Thanks a lot dan |
#3
|
|||
|
|||
Import from Excel
Excel doesn't know about data types. You can format a cell to display
contents as numeric, text, or date but the contents are not of these types. Change the cell formatting and your value may look completely different. You say the value in the Access table field has scientific format. You are positive the field is setup as text type? How many digits are involved in the original value? Dan wrote: When I import from an Excel into Access 2003 table, in one field (Text field) the project numbers appear in a scientific format with exponents. The data in the field are all numeric but I want the field to be defined as a Text. Is there a way to avoid the scientific formats. Surprisingly sometimes it worked fine but I do not know when. Can somebody help ? Thanks a lot dan -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#4
|
|||
|
|||
Import from Excel
The Excel has the part number as 300270310 and after import into Access it
appears as 300270310.00 in some cases and in other cases as 3.0270e.xxx. The field in Access is defined as Text. The one I could overcome is to change it to Numeric then the data is imported correctly. But in that case I have to change all other queries comparing tables etc.... I would like to keep the Access definition for this field as Text only. Maybe I can change the columns in Excel (Text to data) and then try to import. But in that case it involves manual intervention. The field however contains only 9 digits. Thanks.... "June7 via AccessMonster.com" wrote: Excel doesn't know about data types. You can format a cell to display contents as numeric, text, or date but the contents are not of these types. Change the cell formatting and your value may look completely different. You say the value in the Access table field has scientific format. You are positive the field is setup as text type? How many digits are involved in the original value? Dan wrote: When I import from an Excel into Access 2003 table, in one field (Text field) the project numbers appear in a scientific format with exponents. The data in the field are all numeric but I want the field to be defined as a Text. Is there a way to avoid the scientific formats. Surprisingly sometimes it worked fine but I do not know when. Can somebody help ? Thanks a lot dan -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
#5
|
|||
|
|||
Import from Excel
On Sat, 27 Jun 2009 16:04:01 -0700, Dan wrote:
The Excel has the part number as 300270310 and after import into Access it appears as 300270310.00 in some cases and in other cases as 3.0270e.xxx. The field in Access is defined as Text. The one I could overcome is to change it to Numeric then the data is imported correctly. But in that case I have to change all other queries comparing tables etc.... I would like to keep the Access definition for this field as Text only. Maybe I can change the columns in Excel (Text to data) and then try to import. But in that case it involves manual intervention. The field however contains only 9 digits. Thanks.... Try inserting a blank row at the very top of the spreadsheet with an unambiguosly text value. Somehow Excel is assuming that this field must be converted to a number to import into Access. Also... double and triplecheck that the Access table field IS in fact text (and not a Lookup Field or a Number of some flavor). I'm skeptical. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Import from Excel
The only other thing I could think to try is to explicitely convert the Excel
value to a string with VBA Cstr function or Format function. Dan wrote: The Excel has the part number as 300270310 and after import into Access it appears as 300270310.00 in some cases and in other cases as 3.0270e.xxx. The field in Access is defined as Text. The one I could overcome is to change it to Numeric then the data is imported correctly. But in that case I have to change all other queries comparing tables etc.... I would like to keep the Access definition for this field as Text only. Maybe I can change the columns in Excel (Text to data) and then try to import. But in that case it involves manual intervention. The field however contains only 9 digits. Thanks.... Excel doesn't know about data types. You can format a cell to display contents as numeric, text, or date but the contents are not of these types. [quoted text clipped - 12 lines] Thanks a lot dan -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200906/1 |
Thread Tools | |
Display Modes | |
|
|