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 user 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.
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
- Get started by opening your your MS Access database file.
- In the left hand side, you’ll notice the All Access Objects view.
- Highlight the table, query or report you would be interested to export.
- Now from the Access Ribbon hit on External Data
- Hit the Excel button.
Note: Alternatively make a right click on your table, query or report, then select Export and choose Excel.
- The Export wizard will pop up
- Now, go ahead and set the File Name and Format as shown above.
- Make sure you hit the Open the destination file.. checkbox so that your Excel will open after the data transfer is done.
- Hit OK.
- Now hit Close.
- Now in Excel ensure that your file has exported correctly.
- If so, then go ahead and hit File and then Save As.
- In the Save as dialog, set the file name and select CSV UTF-8 as your file type.
- Finally, hit on Save.
Voila 🙂
Note: You might want to look into our tutorial on exporting Excel files to the CSV format.
Transfer Access data to Text and CSV
- Execute steps 1-4 from the previous section.
- Hit the Text File button.
- Specify the file name. Note that by default your file name will have the txt suffix. Change the suffix to CSV
- Hit the Export data with formatting box.
- Make sure you hit the Open the destination file.. checkbox so that your Excel will open after the data transfer is done.
- When prompted set Windows as the Encoding for the new file.
- 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.
- 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 🙂