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

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

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

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

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]

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