August 19, 2004

12:41 PM

I have a mountain of data to move from dozens of Excel spreadsheets into an Access database. The database is an existing one and sort-of documented. How do I go about doing this? I’m very comfortable with Excel, but not at all with Access---I’ve never used it before. How do I push Excel data into Access records? Are there any web resources I could use? Any books I should buy?
posted by bonehead to (10 comments total)

Not sure if this is what you had in mind, but you can open .xls files directly in Access. Just go to File > open and then click on the drop down box where it says "Files of type" at the bottom of the open dialog box. Find and highlight Excel, and then you can find your excel files and open them.

There's a kind of wizard thingee that you can walk through that allows you to alter field definitions, etc.

I do this all the time and it seems to work, mostly.
posted by jasper411 at 12:46 PM on August 19, 2004

Yeah, use jasper411's technique to pull the .xls files in as tables, and then go nuts with append queries.

If the fields match up really well, you don't even need to use an append query. Just right-click on one of the .xls files on the Table tab, and copy it. Then highlight the table you want to dump into and paste. You'll get an option to append the records to the table, and ping! there you are.
posted by COBRA! at 12:51 PM on August 19, 2004

well, i've never worked with access either but another way to do it would be to use excel vba to add all the files together in code and then output them as a comma seperated text file...then some sort of magic occurs (ie: i can't imagine access doesn't input csv's) and you end up with info in access. then again this may just be complete overkill. However, i would suggest picking up a book on excel or access vba because it makes using those programs MUCH more powerful.
posted by NGnerd at 8:20 PM on August 19, 2004

There is no need to write VBA to accomplich this routine task. Even if you needed to convert to .csv, you can do that directly from Excel (File->Save As)

Some points:

1) Make sure the .xls files are saved in the latest .xls format (97 - 2002). The Access import tools won't recognize the older .xls formats.

2) jasper411's method will yield a new .mdb file with the Excel file as a linked table . What you want is a linked Excel table (or many of them) in the existing Access database. Use File -> Get External Data. That will give you two choices: import or link. I recommend linking, as you will have fewer glitches with import errors to chase down; in general, it is a little simpler to link external data.

3) If you do import, there is a bug in Access 2002 that will prevent you from renaming any fields other than the first field. Office XP SP3 has a fix for this.

4) If you run into problems, you might try the microsoft.public.access.* newsgroups, which have tons of typical q&a in the archives. There is even one called microsoft.public.access.externaldata
posted by crunchburger at 3:33 AM on August 20, 2004

I automated this kind of thing, when I was the National Database Conversion Coordinator Design Consultant Guru or somefucking thing back in 1994, and in charge of the data purification and transfer of Ernst & Young Australia's data (all offices, the whole country) from their old practice management system to their new one.

Yes, I used spreadsheets and Access databases. Yes, my procedures were audited by government auditors.

All I can tell you at this advanced level of inebriation is that I wrote a fair bit of VBA code to make it all work the way I wanted, and automate as much as possible the datamapping and purification of tables that numbered in the hundreds of millions of lines into a new db schema thingo.

Man, I still can't believe I got that job. Heh.
posted by stavrosthewonderchicken at 4:24 AM on August 20, 2004

This method keeps you in excell as much as possible. I've used this a lot.

1. Make sure the excel spreadsheet is set up as a table. All of the information in a column is the same kind of information, each row is a single and complete record.

You can't have single records take up multiple lines. You can't have sprinkled throughout the spread sheet.

So from top to bottom, on a single worksheet have all of the like records that are going into the same database table together.

A table about cars would look like this

Volkswagen Passat 2000 75541 2004-07-29
Buick Century 1989 149007 2004-06-02


Then create a new row at the top of the worksheet and label each column with the exact same name as the field that holds the same data in the database. Even if the database designer used names that don't make sense, just match them.

Manufact Model YR numMile ServiceDate
Volkswagen Passat 2000 75541 2004-07-29
Buick Century 1989 149007 2004-06-02


Open the table in Access you're putting the data into.
Click the "Design" button. This shows a list of every field and its properties. To the right of each field's name is its datatype.
This determines if the data in that field in considered text, or numbers, or dates, or integers, etc. The data in the excel spreadsheet needs to match the datatype in Access. You do this using the "Format function in excel. Select an entire column. Right click and select format. Choose the format option that best matches the datatype in access. It should be fairly clear, but you'll have to monkey with data info sometimes. Dates are often stored as text or numbers depending on how they are used.

Okay. So now you have an excel file that mostly looks just like the access table you're importing into.

Now you can use the import wizard inAccess. From the file menu choose get external data>> import.
FInd the file you'll be importing on your hard disk. (You should not have it open in excel at this point.)

Then follow along with the wizard. Hopefully, Bob's your Uncle. If not, you'll get a table created as an error report that tells you what's wrong with the data going into access. You can track down and fix those issues pretty easily. Be doubly careful that the date info came in correctly. If it doesn't work, open the access table, delete the records that are fudged, respond to the errors and try again.
posted by putzface_dickman at 5:10 AM on August 20, 2004

You can't have LABELS sprinkled throughout the spread sheet.

you'll have to monkey with DATE info sometimes
posted by putzface_dickman at 5:13 AM on August 20, 2004

Thanks to all, especially putzface. It's going to be a lot of work (but I knew that), but I have a plan now. The import thingy seems to work. No dates, so that's good. The db I'm monkeying with is here, by the way.

There are multiple tables I need to update for each entry, however. That doesn't appear to be insurmountable---it will (just) take some careful coordination.

Anyway, thanks again. That cliff now has a goat path up it.
posted by bonehead at 5:34 AM on August 20, 2004

Happy to help. Another note on multiple tables. There is some table that is the main one. It should be obvious, probably the one where the names of the spills are located. One of the fields in that table is the primary key and will be called something like "ID." That primary key is how the data in the other tables link up with the main table. It is critical that the other tables get the right key input with the other data so that the database can cross reference between tables or it just won't work. So I would do one of these, depending on circumstance:

Create one worksheet that has all of the data together left to right, organized in order by table. All the fields in the main table adjacent to one another. All the fields in the "Density" table adjacent to one another, etc. One huge master table. Then select all of the columns for Table 1 and copy them into a seperate excel file and import it into the main table. Each of your new records will be assigned a new ID in that primary key field.

Go back into the master spreadsheet and create a new column for the primary key and enter it as assigned by the DB. It will probably just be sequential from top to bottom.
Then slide the primary key field next to the next group of data to go in table 2 . Copy and paste into a new worksheet. Import. Move key and repeat.

If that doesn't work because the way data is stored in each spreadsheet maked it hard to build this master worksheet, you'll probably need to create an ID field in each of the worksheets that's going to be uploaded to each access table and input the ID from a print out of the main table. Otherwise you'll never sort out which numbers go to which spill after they've been pulled into access and rearranged.
posted by putzface_dickman at 6:51 AM on August 20, 2004

CORRECTION: The bug I mentioned (you can't rename the Excel columns at import time) is CAUSED by Office XP SP3, not fixed by it. There is a "post fix" SP3 ONLY for affected systems.

This is a side issue (it's unlikely you will encounter this bug, especially if you follow the excellent advice on Excel preparation above). But, it needs to be corrected for the record in this thread. Sorry!
posted by crunchburger at 10:25 PM on August 20, 2004