File Format Importing from Excel

[Guest]

Hi,

I am importing a field of numbers (123456) into Access from Excel that I
have saved as a text field in Excel - I wish to link the files.

No matter what I try, Access converts the text items to numbers causing
Mismatch errors in my query reports.

Where do I set the import parameters to stop this happening?

I have a reference book - Master Access 2000, by Simpson and Robinson, that
indicates there is an "Advanced" button on the Import Wizard. My version of
2002 does not have such an option.

Regards
Peter

 

[Ken Snell [MVP]]

You can't set import parameters as part of importing EXCEL data. Easiest fix
for you in this case may be to insert a ' character at the beginning of each
cell in that column. This will tell ACCESS to treat it as text (ACCESS and
Jet database engine do not get data type info from EXCEL during the import;
Jet makes up its own mind.)

 

[Guest]

Actually, it doesn't have to be in every cell in the
column, if you just put a character (') in the first
couple of cells in the column, that would do the trick.
Or put a 'dummy' row in the start of the spreadsheet
containing text (a word, anything) in that column (and any
other column you want to import as text). Other columns
can remain blank. Then simply import and delete that row
from the Access table.

 

[Guest]

Excuse me butting in, but I have a similar problem. I am trying to use an
excel spreadsheet as a linked table, so that I can just replace the excel
spreadsheet to get the latest data. If I import it as described below with a
' in the text field that wants to turn into a number, this works fine.
However if I then try to link the sheet, the field is a number and I get #NUM
in the records with text in the field, including the ones with ' in them.

Any further ideas anyone please.

Trevor

 

[Ken Snell [MVP]]

I just tested this in a worksheet (EXCEL 2002 and ACCESS 2002), where I
inserted an apostrophe character in front of the value in each cell in the
column, regardless of whether the value was numeric or nonnumeric. Linking
to the worksheet didn't show an #Num! errors. I got the same result if I
just put the ' character in front of the numeric values.

Be sure that you delete the link to the spreadsheet before you add the
apostrophe character, then relink to the sheet.