by    
Laura C. Larsson

Converting an Excel Spreadsheet to an Access Database

Table of Contents 

Introduction 

Why would you bother coverting an Excel spreadsheet to an Access database? In Excel you are limited by the number of rows you can use to slightly over 16,000.  The number of rows (and thus the number of records is not a limitation in Access). 

For this part of the workshop, I took information from a serials database I had created in ProCite, a citation manager produced by RIS, Inc., and turned it first into an Excel spreadsheet and from there into an Access database using the Access Conversion Wizard which comes with Access. The process is not a difficult one. 

ProCite worked for many years as a tool for managing all aspects of my journal records, including the Kardex, or individual issue check-in record,  but had the main problem that most flat file managers have of forcing the data entry person to key in the same information time after time. For example, several of the journals in my collection have the same publisher. With Access the data entry person enters the publisher once, and the information is then linked via one unique identifier to each journal with that publisher. Not only does this save time, but it reduces data entry errors. 
 

Moving ProCite Records into Excel 

Here is what I did to begin the process of converting a ProCite database to an Excel spreadsheet. 
 

Step 1

Once I had the records selected, I went to File, then Export Marked Records. In the Export Marked Records dialog box, I clicked the Custom radio button and then OK.  A warning box comes up indicating that all (text) styles are removed from the exported text. That's fine. I clicked OK. A Save As dialog box comes up. I indicated where I wanted the new file to be stored and keyed in a file name with a .TXT extension. Procite created the file. 
 

Step 2

Here's how you import an entire text file into Microsoft Excel. Select File | Open. In the Look in box, locate the drive, folder, or Internet location that contains the file you want to open. In the Files of type box, click Text Files. Double-click the file you want to import. The Text Import Wizard will appear. Follow the instructions to specify how you want to divide the text into columns. 
 
Please note that you can also export Access tables and queries into Excel. The process works quite well. 

Converting the Excel Records into Access - Before You Begin

You can import or link all the data from a spreadsheet, or just the data from a named range of cells (column). If you're careful you can move data a column at a time into the appropriate table and save yourself from having to rekey all the data. 

Data can be put in a new table or appended to an existing table as long as your spreadsheet column headings match the table's field names, or its column order is the same. 
 

Instructions for Exporting Data from an Excel Spreadsheet to Access

Once you have selected the option to convert from spreadsheet to database, Wizard will create an entire database with its related tables, forms and reports. Twenty or so database templates are available to choose from. Using the Database Wizard helps you to get started. If you do not see a database that perfectly suits your needs, look for one that comes close and modify it to get your ideal database. 
 

Instructions for Importing Data from an Excel Spreadsheet Table to Access

From the Import dialog box, in the Files of Type box, select Microsoft Excel. Locate the drive and folder where you've stored the Excel file and double-click on its icon, or select the file and click on Import. The Import Spreadsheet Wizard will appear. 

 

When the Import Spreadsheet Wizard appears you will be asked if the first row contains column headings. If it does, click the First Row Contains Column Headings check box and then Next. 

 
 
When asked where you would like to store your data, click the In a New Table radio button, then Next. 

In the next box define Field Name, Index type, Data Type, and whether you do, or do not, want to import the field. Then Next. 

You should now either choose a Primary Key, or not. We have chosen the Publisher ID as our Primary Key field. Then Next. 

Decide on the name you wish to call your table and enter it into the Import to Table box. Then click Finish. 

Access does the conversion. You will likely need to make some changes, but not too many, hopefully. 
 
 

For More Information 

Next:  Examining an Existing Access 97 Database 

Back: What Is MS Access?