Fix Cannot Export Access Query to CSV in Microsoft 365
Question details
Users are unable to export Microsoft Access queries directly to CSV or text files without encountering errors.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to export data from a Microsoft Access query into a CSV format to import into a different system.
- Observed behavior
- The direct export to CSV fails, often due to embedded double quotation marks or delimiters in the data, even though exporting the same query to Excel works successfully.
Verify that your data does not contain hidden or unsupported characters, and back up your Access database before running any bulk update queries to modify field values.
Export via Excel and Save as CSV
Bypass the Access text export bug by first exporting the query to an Excel workbook, then saving that workbook as a CSV file.
Since Access frequently handles Excel exports more reliably than direct text exports, using a spreadsheet application as an intermediary format is the most straightforward workaround.
In Microsoft Access, right-click the query you want to export, select 'Export', and choose 'Excel'.
Follow the prompts in the Export wizard to save the data to your computer as an .xlsx file.
Open the exported file in Excel (or a compatible spreadsheet tool), go to 'File' > 'Save As', and select 'CSV (Comma delimited)' from the file format dropdown.
Replace Double Quotation Marks Using an Update Query
Embedded double quotation marks can break string delimiters during the export process. Replacing them with single quotes can allow the direct text export to succeed.
Install Microsoft 365 Updates
Since direct export failures can sometimes be attributed to bugs within specific builds of Microsoft 365, updating the software may restore normal export functionality.
Handle CSV Conversions Seamlessly with WPS Office
If you need a reliable way to open exported Access data and convert it to CSV formats, WPS Spreadsheet is a powerful, lightweight alternative to Microsoft Excel. It handles data formatting effortlessly without requiring a paid subscription.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
- 2. Open the Exported File: Launch WPS Spreadsheet and open the .xlsx file you exported from Microsoft Access.
- 3. Save as CSV: Click on 'Menu' > 'Save As', select 'CSV (Comma delimited) (*.csv)' from the format dropdown, and click Save.
Frequently Asked Questions
Why does the Access export wizard fail when exporting to a text file?
This issue usually occurs due to unsupported characters in the data fields, such as embedded double quotation marks or delimiters like commas. It can also be caused by specific bugs in certain versions of Microsoft 365.
Will exporting to Excel first keep my quotation marks intact?
Standard Excel typically strips double quotation marks when saving as a CSV unless the field itself contains a comma. If your destination system strictly requires text qualifiers around all fields, you may need to use a custom VBA routine to generate the text file manually.
Can I use the 'Export Data with Formatting and Layout' option instead?
While that option is available in the ribbon, it is generally impractical for raw CSV data exports because it includes structural visual formatting (like headers and column spacers) that interferes with clean data imports in other software.
