Importing from Excel

[Dthmtlgod]

I am having a little problem importing data into Access from Excel. I am
using the Wizard

My Excel spreadsheet cell format is "General" and I have tried "Text". My
Access Database has all text fields and I tried memo.
Here is an example

Excel
3456
2748
UNK
2345

Access imports rows 1,2, 4 but I get a type mismatch on row 3. How can it
be a type mismatch when it is going into a text field?

Thanks for your assistance

DV

 

[Guest]

Try resaving your Excel file as a .csv file. Then try importing the .csv file
into Access as a delimited text file. The import text wizzard will give you
additional options on how to import each field.

Rosco

 

[John Vinson]

I am having a little problem importing data into Access from Excel. I am
using the Wizard

My Excel spreadsheet cell format is "General" and I have tried "Text". My
Access Database has all text fields and I tried memo.
Here is an example

Excel
3456
2748
UNK
2345

Access imports rows 1,2, 4 but I get a type mismatch on row 3. How can it
be a type mismatch when it is going into a text field?

Access has "strong" datatypes; Excel doesn't. Setting the format of
the spreadsheet does not affect this - it just controls how the value
(text or numeric) will be *displayed*. Each cell in a spreadsheet is
independent; when Access imports from a spreadsheet it must guess on
the basis of the first few rows in the sheet what is the appropriate
datatype, and (as in this case) it often guesses wrong.

Two suggestions:

' put a dummy row first in the spreadsheet with an explicit text
value, e.g. 'TextField. Discard this row after importing.

' Create your table empty in Access, with the desired field types;
Link to the spreadsheet rather than importing it, and run an Append
query to populate your table.

John W. Vinson[MVP]