Importing error

[Shari]

I am trying to import an Excel spreadsheet. The headers in the spreadsheet
match the same thing that is in my Access table, name and order. Everytime
I try to import, it gives me an error message. Is there something I am doing
wrong?

The message just says An error occurred trying to import file(my file name).
The file was not imported.

 

[Conan Kelly]

Shari,

That is the only thing the error message says? There is nothing else? I
doesn't tell you that it created a separate table that lists all of the
import errors? Is there a Help button on the error message? If so, click
the help button and see if it gives more details. Post the details back
here.

Sometimes incompatible data types will cause errors, but usually it will
import some rows and create an import errors table that will give you some
info on where the errors occured.

Another thing to try, instead of importing to an existing table, create a
new table in the process and see what kind of data types are used in this
new table. You should be able to create an append query that will append
the records from this new import table to your existing data table.

HTH,

Conana

 

[Shari]

That is all that it says. No help or anything. I looked and it does create a
table with import errors. They are in 2 of the fields, and it says the error
is Type Conversion Failure.

I have imported it as another table, but I have queries that run, and they
need to run out of both tables, not just one. I am not sure if it is possible
to set it up to look in both tables, instead of just one.

 

[John W. Vinson]

That is all that it says. No help or anything. I looked and it does create a
table with import errors. They are in 2 of the fields, and it says the error
is Type Conversion Failure.

That means that you have a Type Conversion Failure - i.e. that you're trying
to import text into a Number type field, or invalid dates into a date/time
field, or the like. What are the datatypes of these two fields? If you look in
those columns in the spreadsheet, do they contain invalid data? Some
spreadsheets will have (e.g.) "N/A" in a number field, or "Date not specified"
in a date field.

I have imported it as another table, but I have queries that run, and they
need to run out of both tables, not just one. I am not sure if it is possible
to set it up to look in both tables, instead of just one.

How are the tables related, and what do you want to do with them?

 

[Conan Kelly]

Shari,

Those type conversion errors should help you debug this and figure out what
is going on. If I remember correctly, this error table will have 3
columns/fields in it: 1 to tell you what row/record the error occured on, 1
to tell you what field/column the error occured on, and the error type. So
you have to look at the design view of the table you are importing into to
find out what the data type of the 2 error columns are, and you have to try
to figure out what data types XL is trying to make those columns. The best
way to figure out what type XL is making them is by checkin your new import
table to see what data types were assigned to those 2 error columns, then we
gotta figure out how to get the data into the table.

Please look up the data types of your error columns in your Data table. If
you don't have too many columns, please provide me with the all of the field
names and their data types. If that is too much work, just provide me with
the names and data types of your error columns. Do the same thing for your
new import table. Also, you will probably need to give me an example of
data in those 2 columns in your XL file.

FYI, there is a good chance that Access imported all of the records from you
XL file but left those 2 error columns blank in the rows where errors
occured. In other words, there may be some rows imported that were valid
and some that have errors. In order to import your XL file again without
errors, you will need a way to filter out all of those rows that were
imported, not just the error ones, so they can be deleted. If you import
again without deleting rows that were imported previously, you will have
duplicate data.

Please provide me with more info and I will try to help you out as best I
can,

Conan

 

[Conan Kelly]

Shari,

I have imported it as another table, but I have queries that run, and they
need to run out of both tables, not just one. I am not sure if it is
possible
to set it up to look in both tables, instead of just one.

I forgot to mention in my other reply, my goal is to get all of this data
into your main data table so you don't have to make the queries look at both
tables. If we are successful, you'll end up deleting this new import table.

HTH,

Conan

 

[Shari]

Thank you very much for all of your help!!

The data types in the Access database are both text. The names are Meter
Number and Serial Number. The data types and names are exactly the same in
the XL file. In the Meter Number field, all of the entires are a series of 8
or 9 numbers, no letters. In the Serial Number field some of the entires are
numbers and letters, and others are just numbers.

The thing is, not all of the entries are having problems importing.

 

[Conan Kelly]

Shari,

If I remember correctly, AC has a problem importing numbers from XL into a
text field when they are stored as numbers in XL. I think the only
work-around I've found for this is to precede every entry in XL with an
apostroph (') forcing it to be text (before entering data in XL, you can set
the formatting of that column to text instead of general, that way, when you
type in numbers, they will be stored as text). If you have a lot of records
to change, it would be quite time consumeing and tedious to go through and
add an apostrophy to every cell. If you are familiar with VBA in XL, it is
pretty simple to come up with some code that will do this for you in a blink
of an eye. If you are not familiar with using VBA/macros in XL, I can come
up with the code and walk you through it.

Once again, you are probably going to want to delete all of the records that
were imported in the previous imports that caused errors and then you can
reimport this file. Do you have a way to determine all of the records that
were previously imported from this file and delete them?

HTH,

Conan

 

[Shari]

Wouldn't it be easier to just change the data types of the columns? As far as
I can tell nothing is being imported into my original table, so there are no
issues with having to delete it. There are over 700 entries I am trying to
import. And putting an apostrophe in the field is not an option, because I
send letters out based on the information in the fields, and having an
apostrophe in there would look very unprofessional.

 

[Conan Kelly]

Shari,

Are these fields requiered? If not, leave blank and everything is fine.

If they are requiered, what would be easier:
1. Go into each record and manually enter the data
or
2. Delete all records and reimport without errors

WOOPPSS!!! I just reread your post. Ignore everything before this. Are
you sure nothing is being imported. I thought that AC would import the
columns that have no errors and leave the other columns blank. Go back an
make sure nothing is being imported. But in a previous post, you say "The
thing is, not all of the entries are having problems importing." That
sounds like some are importing. If nothing is being imported, then we don't
have to worry about deleting anything, but if there is something being
imported, some, if not all, records will be duplicated unless we delete
first.

When I say put an apostrophe in, I don't mean put it into each field in AC,
I mean put it into each cell in XL. During the import process, the
apostrophe will be eliminated. It is invisible. It is just there to tell
XL that the number entered is to be stored as text and not a number. Adding
apostrophes can be a very quick easy process with macros/VBA code.

And just changing the data types won't help. AC does not like to import
numbers (stored as numbers in XL) into a text field. You need to change the
numbers stored as numbers in XL so they are stored as text, and then import
in to AC. Changing data types doesn't work. Believe me, I've been through
this before.

Please write back and let me know how you want to proceed and I will try to
help you out.

Conan