 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / February 2008Importing error
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. 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 >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 [quoted text clipped - 5 lines] > name). > The file was not imported. 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. > Shari, > [quoted text clipped - 26 lines] > > name). > > The file was not imported. >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?  SignatureJohn W. Vinson [MVP] 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 > That is all that it says. No help or anything. I looked and it does create > a [quoted text clipped - 41 lines] >> > name). >> > The file was not imported. 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. > Shari, > [quoted text clipped - 75 lines] > >> > name). > >> > The file was not imported. 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 > Thank you very much for all of your help!! > [quoted text clipped - 111 lines] >> >> > name). >> >> > The file was not imported. 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. > Shari, > [quoted text clipped - 134 lines] > >> >> > name). > >> >> > The file was not imported. 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 > Wouldn't it be easier to just change the data types of the columns? As far > as [quoted text clipped - 176 lines] >> >> >> > name). >> >> >> > The file was not imported. 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 > That is all that it says. No help or anything. I looked and it does create > a [quoted text clipped - 41 lines] >> > name). >> > The file was not imported. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.