fbpx

Creating a Comma Separated Value (‘csv’) file from Access

Below is a simplified procedure for creating a .csv file from Access. As with so many applications, there are dozens of setting, parameters and options you can pick from, but to accomplish the simple task all you need to know follows.

What is "csv"?

Let’s just make sure we all know what we’re talking about. Data stored in Access, and pretty much every other database system, stores data in a specific format, usually proprietary for the database application. Getting data into and out of any of these systems can be effected by importing from and exporting to “plain text” files. Unlike the Access .accdb or .mdb file, which can only be viewed using Access, these are files that can be opened and edited using a plain text editor (such as Notepad) or any document editor (such as Microsoft Word) and in many cases by Microsoft Excel.

A particular type of plain text file is referred to as “csv”, for “comma separated values”. These files have .csv as the file extension. Note that Excel will open .csv files and display them as if they were native Excel files. Further note, having said that, there are many things Excel will do with .csv files that will confound you if you are not familiar with Excel’s rules for handling them.  More on this in a future article.

Below is an example of the contents of a .csv file. Notice the first row, referred to as the “header” provides the names of the data columns that follow. Note how commas are used to separate the values to create columns of data.

 

 

 

The biggest benefit to knowing this is that every commonly known database has the ability to import from and export to .csv files. Other formats also fall into this category, but you almost always find a way to move data using .csv files.

Actions to Take

Here is an example of exporting data from an Access database table named Contacts to .csv. In this case the user wanted to upload his contact list to a marketing application for the purposes of sending emails and texts.

  1. Locate the data table to export from the Access Navigation Pane (the default view when you open Access)

       2. Right click on its name, and select Export from the submenu

       3. Click on Text File in the sub-submenu. The following window appears.

       4. In the window below, change the name in File Name so it ends with “.csv” not “.txt”.      Do not check any other boxes on this window. 

       5. Now click OK.

       6. In the following window, select “Delimited” and then click Next.

       7. In the following window, make sure the “Comma” radio button is selected, check the box for “Include Field Names” and make sure there is a double quote mark in “Text Identifier”

Disregard the “Advanced” button.  This is not needed for simple exporting.  Now click Next.

       8. In the next window, confirm or modify the file name and its path (location) that will be created. Make sure the file name ends with “.csv”. Then click Finish.

       9. When the final window appears, click Close. You can now access the newly created .csv file.

Every effort was made here to keep this simple and only describe the most basic actions needed to get a result.  As you can see there are other options and actions that could be taken. I will take these up in later articles as the demand arises.

Mark Thomas is a Data Solutions Master. He is always happy to receive inquiries on any topic. He can be reached here.

Leave a Comment

Your email address will not be published. Required fields are marked *

Close Bitnami banner
Bitnami