Excel import problems - please help!

[JediDG]

Hi All,

Not sure whether this has been previously covered, but hey ho.

I'm having problems importing an excel spreadsheet into Access. It comes up with the generic "There was an error" error message and then thats it.

I've been playing around with the data in the excel sheet importing it column by column and it seems to import fine until I get to anything that is displayed as a text column in excel, but not as a text column in Access (for example a Yes/No field).

I've checked that all of the column headers are the same for both the table and the spreadsheet but it still won't let me import the information.

Does this have something to do with the formatting of the table in Access?

Any ideas would be appreciated, this is for a work task and I'm a bit stuck!

Cheers,

JediDG.

 

[FoFa]

You will get a data type mismatch if the fields are not the same. If it is a text field in Excel, it needs to be a text field in Access, or you must convert it. In the case you have, if you do not wish to change your table format, then I would import to a seperate (import) table, then use a query to update the main table converting data types on the fly.

 

[The_Doc_Man]

The alternative is to redesign your table to match the exact properties of the spreadsheet you are importing.

I.e. even if the field says YES or NO, import it to a (short) text field and then convert it to another format later.

 

[JediDG]

Hi All,

Thanks for the replies on this, a bit of an update though.

This morning I have exported the access 97 table into excel with the "save formatted" option ticked.

I've then copied into this excel sheet the information I want to eventually end up back on the Access table. After doing this I have then tried to import the sheet back in.

The import takes place but then throws all of my rows out because of a "key violation". I suspect this is down to an autonumber field as this is the primary key but I can't figure out how to get round it.

Incidentally, the autonumber has got to 8066 and the next "number" it generates is 1E+04 - Should this happen? I would have assumed it would go up to 8067?

I'm pretty confident that the formatting of the colums is now the same, but I can't get it to import.

Starting to lose patience now, so any help would be appreciated.

Cheers,

JediDG.

 

[st3ve]

Inadvertantly set a format, possibly

Incidentally, the autonumber has got to 8066 and the next "number" it generates is 1E+04 - Should this happen? I would have assumed it would go up to 8067?

This part is down to formatting only. The 1E04 is scientific notation format and means 1 times 10 to the power 4 ie 10000, which is the closest approximation to 8067. At some point in your process you may have inadvertantly set a format, possibly.

As to the table you want to import, I guess you're trying to inport it into an existing table. Try importing it as a new table and compare results.

 

[JediDG]

Hiya Steve,

I've tried importing it as a new table and it works fine. I suspect it won't let me import because I may have set some sort of formatting for the autonumber by accident. Is there any way of undoing that format so that it counts up sequentially again?

 

[st3ve]

Adjust format

JediDG

Make a safety copy of your table then try this.

Go to design for the table and select the autonumbered field. In the lower option box select format and choose General Number (you'll see in there that one of the options is scientific). Close and save the table and check your data. Then re-open in design mode and delete the format option (General Number) altogether. You should now have what you require, I hope.

 

[JediDG]

Steve,

No good.

The only other thing I can think of doing is making a note of the relationships to the other tables, then deleting the relationships, deleting the autonumber, putting it back in and then putting the relationships back in.

Not 100% confident with doing that though, what do you think?

 

[st3ve]

I agree that it doesn't seem to be an import problem as such. If you import the data into a fresh table you have no problems. It seems to me that you are trying to populate a new database.

It could well be a relationship problem, but you could try this ...

Get the table imported as a fresh table, then run a query which updates the data into your target table. I'm not hot on relationships, but it may be that the import process can't cope with the relationship aspect. If that doesn't do it, then your suggestion of splitting off the relationship to make the import, may do the job.

 

[JediDG]

Hiya,

Yeah I am trying to populate a new database which I didn't write (unfortunately) but it does already contain entries which were copied across from a previous database.

Thats half the battle, I don't know how the database was set up so I'm having to 2nd guess everything and my access knowledge isn't great to start with.

Do you know how to set up a simple query so that I can get Access to look at one field in one table and get it to copy the contents across to another table?

Thanks,

JediDG.

 

[JediDG]

Steve,

Cheers for the email.

Unfortunately I can't send you the database as its full of government information and I don't think they'd be terribly impressed.

I've managed to get the data into a new table that has exactly the same field formatting as the existing table (typical) minus the relationships.

Is there any simple way to just ship this extra info across into the existing table?

 

[st3ve]

Append type query

Select an Append type query.

To do this ... bring the new table into a new query.

Then select Append query type from the selector next to the ! run icon and choose the table name where you want the data to go on the drop down.

Then match up each field between the source table and your existing table to be added to. If the names already match exactly, access will default to this, but you can still adjust this if required.