Importing Excel data into Access - error message

[shindig54]

Hello, I downloaded a year's worth of financial transaction data into Excel and exceeded the limit on one sheet but the data did download into 2 sheets. When I try to import into Access (even in just a blank data able) I receive the following "Cannot open database. It may not be a database your application recognizes, or the file may be corrupt". Can anyone help me? Thank you.

 

[Micron]

Try linking to the largest sheet from Access first. If it works, you could either work with the data as is or append it to a new table in Access. Warning: very often, spreadsheet data is not properly structured for use in Access. Excel data is spread across columns, Access data should be in rows, which usually requires that it be broken up into related tables. If that is news to you, suggest you research db normalization as a start.

 

[shindig54]

Try linking to the largest sheet from Access first. If it works, you could either work with the data as is or append it to a new table in Access. Warning: very often, spreadsheet data is not properly structured for use in Access. Excel data is spread across columns, Access data should be in rows, which usually requires that it be broken up into related tables. If that is news to you, suggest you research db normalization as a start.

This worked beautifully, thank you so much. Can you please tell how I could append the data to a new table? Sorry, I have never linked to an Excel file through Access before. Thank you again.

 

[Micron]

For the first time I'd try a make table query that uses your linked sheet. Then check table in design view that it correctly interpreted the field types (e.g. dates are date/time data type, numbers that should be numbers are, etc.). Access updates the view of your linked sheet automatically if the source data changes but I cannot say how often. Certainly it is upon opening but I don't know what drives that thereafter. Once you have the table correct (it may be already) when/if linked sheet data changes you either
- use an append query if sheet data is only ever added, not edited
- use append query to add new sheet rows to this table and an update query to edit the table. The trick here is to add only new records. You would have to decide what constitutes a unique index and create that in your table. Research either 'ms access unique table index' or something generic such as how to add only new records from Excel
- also could simply replace the table with the same make table query but many think this is a cause of db corruption

There is also something called an upsert query but I've never used it. Perhaps that is another option for you.

 

[shindig54]

Thank you again for your help. The data I downloaded out of our system was put on two tabs because it exceeded the number of rows Excel allows on one tab. So I split out the two tables into two Excel files and I was able to link to the two tables in Excel. I will try the make the make table query. Is there also a way that I can eventually combine the two tables that I linked to into one access table?

 

[Micron]

You'd link the 2 sheets in Access and append the rows from each into one Access table. Should work because each sheet has the same column headers, yes?
I think you meant you were able to link the two tables in Access?

I was able to link to the two tables in Excel

 

[shindig54]

One of the tables is giving me trouble when I try to combine them. I am to link to both tables but even when I try to create an individual table for just the second tab, I receive various error messages. They are:

-Cannot open database. It may not be a database that your application recognizes, or the file may be corrupt
-Search key cannot be found
-Data must be queriable (or something like that)

The first message is the one I just received. I deleted the entire database and started over from scratch. I made sure I didn't have any leading spaces in my headers, and the first Excel file loaded into an Access table just fine. The second table should be laid out exactly as the first table, it just came through on a second tab because there was too much data. Is there anything I can try?

 

[Micron]

So you managed to link both spreadsheets in Access? Then what happens when you double click on the linked spreadsheets? Do they both open or is trying to do that raising an error on one of them? Neither one of these spreadsheets has more than 255 fields I hope.

 

[shindig54]

They will both open individually. There are about 25 columns of data and it's accounting type data. The two tabs are part of the same extract. I can pull the data into one table, but when I try to add the other, I receive the error message about how it can't open the database.

 

[Micron]

All I can think of at this point is that you still have a lock on the target table. The most sure way to eliminate that as a possibility is to append the first sheet data then make sure the table and that query is closed. Better yet, close the db, reopen and try the 2nd append. If you still have that problem then I'm afraid I don't know what to suggest. If no one else chimes in here, the only other way I can think of that I could add anything to this is if I had the 2 spreadsheets and your db.