How to export data from Microsoft Access tables, queries and reports to Excel, Txt and CSV formats?


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. In Access 2016 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?

Thanks for the question.

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.
  • Move your data from Access to CSV using the Export to Text capability.

Let us get going.

Move data from Access to Excel and CSV

  1. Open your 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.
  6. Alternatively make a right click on your table, query or report, then select Export and choose Excel.
  7. Set the File Name and Format.
  8. Make sure you hit the Open the destination file.. checkbox so that your Excel will open after the data transfer is done.
  9. Hit OK.
  10. Now hit Close.
  11. Now in Excel ensure that your file has exported correctly.
  12. If so, then go ahead and hit File and then Save As.
  13. In the Save as dialog, set the file name and select CSV UTF-8 as your file type.
  14. Hit Save.
  15. 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 csv file will open in your favorite text editor or in Excel if you have set it as the default app for CSV viewing and editing.
  8. Save your file and you are done.

Happy data analysis :-)