Importing CSV Data into Access 2010

Synopsis: Access 2010 provides facilities in importing data of many types and in many formats.  Here we look at the import of data in CSV format.

CSV (Comma Separated Value) format is a very popular way of moving data from one system to another.  Although it has its limitations in terms of the types of data that can be accommodated, it is a particularly simple format to understand and use.

Here is an example of data in CSV format.

Each row contains information about one item.  In this case the information relates to courses to be offered in a college in the coming year, so the items are courses.  Each item begins with a number (25, 26, …) and then the name of the course between quotation marks.  The first course is called “Access: Information Technology”.  The next piece of information is the course code.  The code for the second course is “1C200111W11”.

For each item the values of the individual attributes (fields) are separated by commas, which is where the name Comma Separated Values comes from.  In fact, they could be separated by some other suitable character, such as a semi colon.

This file contains details of 24 courses that will be offered at the college next year, and we’re going to see how to import this information into an access database.  It’s actually very straightforward, but even so it might be appealing just to type this information into the database rather than use an import facility.  After all, there are only 24 items and it wouldn’t take long, would it?  There are two reasons not to retype the information:

1.    You’d be quite likely to make a mistake, and you’d certainly need to check it all very carefully.
2.    Once you’ve found out how to do this automatically, you could use the same approach to import 24,000 items.  That would save a lot of typing!

So, let’s see how to import this CSV file.  I’m going to import it into my student database, which is already open.

On the External Data tab in the Import & Link group select Text File.

This starts a wizard that will take you through the steps of importing the data.

First you need to locate the CSV file that is going to be imported.  Mine is on my desktop.

Then you need to decide whether you want Access 2010 to create a new table for this data or whether to append the data to an existing table.  (There is also a third option where you can just link to the data where it is rather than actually import it into our database, but we definitely don’t want that in this case.)

We’re going to append it to an existing table, so select that option and then the table to append it to.  The dialog now looks like this.

Click OK.

Access 2010 investigates the import file and works out what it thinks it is.

It has (correctly) worked out that it is a delimited file, i.e. that the pieces of data are separated by delimiters.  In this case the delimiter between fields is a comma.  (Note that Access 2010 can also handle fixed width data, where each field has a fixed number of characters, so we might allow 40 for the course title, 11 for the course code, and so on and we don’t need or use delimiters, but that’s not the case here.)

We’re happy with the choice Access 2010 has made, so click Next.

Access 2010 now works out (correctly) what the delimiter is and that:

•    The first row does not contain field names (although it could, of course)
•    That the text qualifier is the double quotation mark (i.e. that’s what we’ve put round text expressions to keep them together)

If anything it works out is wrong, this is our chance to change it.  It’s all OK, so click Next.

There’s one last screen to check, then click Finish.

When the import is complete, Access 2010 gives you the option of saving the steps you’ve just performed to use again another time.

And finally, here are the appended records in the database table.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!