One of the quickest and easiest ways of getting data into an Access database is to import a Comma Delimited (CSV) text file.
In a comma delimited file the fields are separated by commas which makes them easy to create but there are a few nuances that you will have to be aware of.
In this Access Tutorial I use Access’ text import Wizard to make quick work of importing data into our Access table.
Importing A CSV File Into Access Step #1:
The Import Text File Button
The first step in importing a text file into Access is to click on the Text File button in the External Data – Import area of the Ribbon Bar.
This picture was taken using Access 2007 and will work with both Access 2007 and Access 2010.
If you have a prior version of Access the same import text functionality is available but you might have to hunt it down in a menu bar.
Import A CSV File Into Access Step #2:
Select A CSV Text File
After clicking the Text File button you will enter the Import Text File Wizard.
This is the first screen of the Wizard which allows you to select both the file you want to import and how you import the file.
The file selection is straight forward: you can either type in the filename into the File Name input box or you can click on the Browse button to launch the file dialog and select the file graphically.
Import A CSV Files Into Access Step #3:
File Import Options
The next set of options are very important. Access gives us three choices of how we are going to import the data.
I recommend selecting the first option – “Import Data Into A New Table”.
The reason I recommend bringing data into a new table is what happens if the data import goes wrong? If you are appending data to an existing table now you have to separate out the new data from the old data and then delete the newly imported (and wrong) data. Not easy.
If you import the data to a new table it’s very easy to write an Append Query to move the data from one table to another.
Import A CSV File Into Access Step #4:
Delimited File Option
This step of the Export Text Wizard allows you to select the ulimate file format: Delimited or Fixed Width. Here we are importing a Comma Delimited file so we will select the delimited option.
Access generally does an excellent job picking the correct format because its usually pretty obvious which format the file uses. If you select the wrong option just click on the back button until you return to this screen and then you can select the right option.
Now click the NEXT button.
Import A CSV File Into Access Step #5:
Select Your Delimiter
Now we have the option to select the delimiter for the file. There are many standard options: a comma and a tab character are the most common but you can also select a semicolon, a space or a custom character in the Other box.
Usually Access is very good at selecting the correct delimiter and if it doesn’t select the right one it’s very obvious – the imported file will look like garbage. If you get it right the file will look like the picture above, all the fields will be nicely arranged and ordered.
Import A CSV File Into Access Step #6:
Header In The First Row?
This screen gives you the option of having the first row contain field names. If you have set your file up with field names in the first row then check this box and the first row will be used for field names.
HINT: I recommend having the first row contain field names. It makes things easier because you can just look at the first row to determine which field is which. There are many situations where fields contain data that is very visually similar and figuring out which field is which may take precious time and energy.
Import A CSV File Into Access Step #7:
The final option in this screen is to select a text qualifier. A text qualifier is a character that begins and ends a text field so that it doesn’t get corrupted if it contains a delimiter character.
Here again Access is very smart about choosing the correct text qualifier as it was with the delimiter. And again if Access does not choose the correct text qualifier it will be very obvious that something is wrong.
Import A CSV File Into Access Step #8:
Field Data Types
This screen is a very powerful tool in the data import process. It allows you to specify the Field Name, the field data type and whether or not the field is indexed. This screen is a mini table designer – it allows you to specify the most basic elements of table design. If you want to have the more advanced elements such as validation rules, formatting and comments you can go into the table designer after the table has been imported.
Import A CSV File Into Access Step #9:
No Primary Keys
This step of the Wizard allows you to specify a primary key. Primary keys are very important in relational databases however at this point we are not ready to go into the nuances of relational database design so I am going to recommend that we do not use a primary key for now.
The reason I am recommending this is that the purpose of this course is to get you up and running with Access as quickly as possible. If I went into a whole detour about relational database theory we quickly get off track and move away from the most important thing – having you get your hands on a functional Access Database.
Import A CSV File Into Access Step #10:
Naming The Table
The final step of the table import process is to name the table. I highly recommend naming tables using the “tbl” prefix. After the “tbl” prefix I suggest using a short descriptive name. For example this table is called tblOrders which contains sales data for a mail order company.
Naming all your tables in a consistent way is become a lifesaver as you add more tables and other people start to rely on the data in your new database.
NEVER use an Access database’s default table names like Table1. If you use the default names you will quickly discover you have no idea where your data is. “Was that email address in Table3 or Table14?”