error importing excel data to access table

[Jaylin]

When I try to import excel data to access table, I receive "Import error file
is not ....".

I do not know how to detect what is wrong with the format in the excel file.

Even when I save the file as csv or txt, I receive error file indicating
there is "conversion type" problem in many rows.

Hope I would be advise how to fix the file format or detect where the
problem is

 

[Jeanette Cunningham]

Jaylin,
the conversion type errors indicate that at least on of the columns in the
import file has mixed data in it.
Access is a bit like grandma's pantry - there is a place for everything and
everything goes in its right place.
Access will only allow text in a text field, numbers in a number field and
dates in a date/time field.
The numbers are separated according to field size - long integers, single,
double.

In excel it is easy to put text and numbers in the same column.
This will give the error about type conversion.

One option is to go back to the excel sheet and fix the columns that have
numbers and text in them. You might want to import them as text. To do this,
make sure that the first row of the column (below the heading row) has a
text word in it - type in xxxxxx as the word. Try the import again by
importing into text fields in the access table. After it imports, just
delete the first row to remove the xxxxxx words.


Jeanette Cunningham

 

[Dale Fye]

Jaylin,

I sure wish Microsoft would give us the ability to define an import
specification for Excel files, rather than letting Access decide for itself
what data type to use.

The way I generally work around this is to copy the column headers into the
second row of the Excel spreadsheet, ensuring that each one starts with a
letter. I then do the import into a new table in Access. Since all the
fields will be text data type, the import generally goes well. I then
delete that extra row that caused Access to think all the fields were text,
and check the data in each of the columns (a simple sort will generally help
you identify whether the field should be text, numeric, or date). I then
change the data type of the field accordingly and save the table. If the
table saves successfully, it is generally an indication that the new data
type is correct.

Along the way, I generally find some fields that contain both text and
numbers. When this happens, I generally leave the field as a text data type
and move to the next field.

When I have completed this process, I copy the temporary tables structure to
a new table, which is where the data will go when I am done importing and
transforming it. I then add columns that will allow me to transform mixed
numeric/alpha data into multiple columns. I then write some queries that
will move the data from the temporary import table to this final data table.
For example, my first step would be to append all of the fields that don't
require transformation into the new table. Then, I would link the new table
to the temp table on the PK field(s) (from the temp table) and then update
the data in those mixed data type fields based on the type of the data in
each record.

Once I have this working, I automate the process so that I can repeat it
over and over again. The key is that once you have the data structure
"right" for the temp table, use that as the table you do your initial import
into. Then perform the transformation steps to get the data into your final
table, then delete the data in the temp table.

HTH
Dale