import excel to access

[raymond chiu]

Dear all,

I have a strange situation.
First of all, I have access program which export data to excel. In excel,
there is a field named as "Part_No" which like as below.
1123456025
1523645621A

I have formated this field as string in excel.
But when I import this excel to access, it show numeric in importing
procedure. Due to this reason that some are like number and some like
combination of number and character, it cannot imported.

How can I know?
As When I link the excel file to access and open this link, I find that only
string with all number digits show up but error in the others.

Is it a bug in office?
I am using Office XP Professional on windows XP Professional.

 

[John Nurick]

Hi Raymond,

This is a common problem. When the data is being imported, the Jet
database engine used by Access examines the first few cells of each
column in order to choose the data type to use. If these are numbers, it
uses a number field - and hits trouble when it finds a non-numeric value
lower down.

Ways round it include:
-Make sure that the values in the Excel cells are prefixed with an
apostrophe '. This ensures that Excel and Access will treat them as text
(Excel doesn't display the apostrophes). Since you're exporting the data
to excel in the first place, you can include the apostrophes in the
query you export, e.g with a calculated field like this
fPart_No: "'" & [Part_No]

-Instead of importing to a new table (so that the table is automatically
created), create the table yourself with a text field for the Part_No

-Ensure that the first row of Excel data contains a part number that can
only be text, e.g. "123433452A".

For more detail, see
http://www.dicks-blog.com/excel/2004/06/external_data_m.html