How to export data from Microsoft Access tables to .CSV, Excel and text formats?

by

Here’s a question from Kevin:

Just starting out with Microsoft Access and looking into sample database i just built. Wanted to understand bit more about the procedure for exporting data from an existing table. I understand that if i am interested to apply some follow up analysis to the data using Python’s Pandas library, then i would probably need to be able to export the data to the comma separated value (CSV) format (Even better if i can use the | delimiter). In Access, i see options to export Access data to Excel and to Text file formats. But which of them i can use in order to finally arrive to a CSV format that i can further analyze?

Thanks for the question Kevin.

There are several option to achieve your goal, but for the sake of brevity, let us focus on a couple of options:

  • Move your data from Access to Excel and then save your work as a CSV file.
  • Convert your data from Access to the CSV format using the Export to Text capability.

Let us get going.

Load data from Access into CSV and Excel

  1. Get started by opening your your Microsoft Access database file.
  2. In the left hand side, you’ll notice the All Access Objects view.
  3. Highlight the table, query or report you would be interested to export.
  4. Now from the Access Ribbon hit on External Data
  5. Hit the Excel button.

Note: Alternatively make a right click on your table, query or report, then select the Export option and choose Excel.

  1. The Export wizard will pop up
Convert an Access table to Excel and CSV
  1. Now, go ahead and set the File Name and Format as shown above.
  2. Make sure you hit the Open the destination file.. checkbox so that your Excel will open after the data transfer is done.
  3. Hit OK.
  4. Now hit Close.
  5. Now in Excel ensure that your file has exported correctly.
  6. If so, then go ahead and hit File and then Save As.
  7. In the Save as dialog, set the file name and select CSV UTF-8 as your file type.
  8. Finally, hit on Save.

Voila 🙂

Transfer Access data to Text and CSV

  1. Execute steps 1-4 from the previous section.
  2. Hit the Text File button.
  3. Specify the file name. Note that by default your file name will have the txt suffix. Change the suffix to CSV
  4. Hit the Export data with formatting box.
  5. Make sure you hit the Open the destination file.. checkbox so that your Excel will open after the data transfer is done.
  6. When prompted set Windows as the Encoding for the new file.
  7. Your comma separated value file will open in your favorite text editor or in Excel if you have set it as the default app for .csv file for viewing and editing.
  8. Save your file and you are done.

Note: If you know Python and are interested to supercharge your data export/import processes, you might want to look into our tutorial how to use Pandas with Excel.

Happy data analysis 🙂