Tutorial Blog 3/Importing and Exporting Data In Access

With today being the information age a lot of times I think we find ourselves overloaded with information while running out of room to store and manage it.  Well, microsoft access is a powerful database tool that will allow you to store and manage your data as you see fit.

Many people have heard of access but have never attempted to learn it's full capabilities.  One thing most people do not know is that you can import and export data into and out of microsoft access.  This is very useful when you realize that you have a very large excel or text file that would be easier to manage as a database.  Within the next couple of paragraphs I hope to show you how to properly import and export data in access.

First, you need to open microsoft access and open a blank database.  After opening a blank database you then will need to go to external data on the ribbon which will display two boxes; one with options for importing and one for exporting.  You should see something similar to the image below.

We'll start with importing data.  As you can see from above you have several options to choose from when importing data such as excel, sharepoing, text files and xml files.  We'll keep it simple for demonstration purposes and choose to import an excel file.  After clicking on the excel icon in the Import window you'll have a dialog box appear where you can browse your hard drive for the correct excel file to import as well as choose whether you want access to create a new table for the data or just append it to a table currently in your database.  Access will then give you the option to choose which worksheets you would like to include from the excel file.  After selecting your range you will then click next.  If your first row includes column headings be sure to check the check box on the next page so they will be recognized as field titles.  After that you will be given the option to select the data type necessary for each field as well as choosing a primary access key.  After all of this you're ready to name your file and finish the import.  After finishing the import it should open your new table in datasheet view with all the data from the excel file.  You're new table may look something like this.

So since you now know how to import data into access let's quickly go over exporting as it is very similar but much easier.  Let's export an access table to excel.  As you probably guessed you will simply click on the excel button in the Export pane.  You will then select the file type you want the table to be saved as, as well as selecting the file name.  After making the appropriate selections you will click ok and you will see a message that tells you your table was successfully exported. 

I hope that this was helpful in explaining how to import and export data in microsoft access.  It would be best for you to try it out yourself and play with the different file types.  There are several more options when importing a text file but mostly it is self explanatory.  Hopefully, by practicing you'll be comfortable with microsoft access in no time!