Search code examples
vbams-accessexport-to-csv

Export Access Query WITHOUT Formatting

Relatively simple, but I can't seem to work it out. I want to export a query from access into a .csv (tab or comma delimited). When I do it manually through the wizard it works fine. But when I do it via vba, it comes complete with dash formatting that looks like the borders in the table!

I tried two methods and got the same results

DoCmd.OutputTo acOutputQuery, "Qry_GRADE", "MS-DOSText(*.txt)",_
 "grade.csv", True, *ExportSpec*, , acExportQualityScreen

I used it with or without "ExportSpec", which is a specification I created when exporting manually.

This is the second method:

Dim testSQL As String
Dim qd As DAO.QueryDef

    testSQL = "SELECT * FROM Qry_Grade"
    Set qd = db.CreateQueryDef("tmpExport", testSQL)
    DoCmd.TransferText acExportDelim, , "tmpExport",_
         "C:\Users\Databoe\Documents\KidsTV\grade.csv"
    db.QueryDefs.Delete "tmpExport"

This is a solution I've found which seems like overkill

And this is what the output looks like:

screenshot exported query

You can see it's not actually split any of the columns when opening the file in excel and that every second line is just a string of "-"'s

Solution

  • What about DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myQueryName, myExportFileName, True for direct excel file export.

    I tried your approaches, but I only get formated text with your first try DoCmd.OutputTo acOutputQuery, "Qry_GRADE", "MS-DOSText(*.txt)",_ "grade.csv", True, *ExportSpec*, , acExportQualityScreen which is as expected because it's a text export not csv.

    With your second method I always get an excel compatible result. Maybe you have an error trap that hides an error and the first grade.csv is not overwritten. Use a different filename for the second method to prevent that.

    Found a second trap. You don't specify full file path in first method, but in second. If C:\Users\Databoe\Documents\KidsTV is not your default document path, you have 2 grade.csv in different folders, but you maybe think that you only have one that gets overwritten.