Export Access Query WITHOUT Formatting

Question

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:

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

If you mean writing the file directly is "overkill" it is not. It's the only method where you have 100% control of the output. And once created, it just works with zero issues. Highly recommended. — Gustav
– Gustav, Commented Jun 17, 2016 at 10:58
I get your point, but given that there is a wizard within access that allows for exporting that works, surely there is a vba method that replicates this? Or am I just naively trusting that MS would implement such a function!? — REdim.Learning
– REdim.Learning, Commented Jun 17, 2016 at 11:21
To be honest, I can't recall. But years ago, when creating export routines for six different receivers with all kinds of headers, formatting, special handling of empty fields, etc., I gave up and wrote the routines myself. It takes a little to get started, but it is quite straightforward really. — Gustav
– Gustav, Commented Jun 17, 2016 at 11:43

BitAccesser · Accepted Answer · 2016-06-18 01:41:38Z

3

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.

edited Jun 18, 2016 at 1:41

answered Jun 18, 2016 at 0:07

BitAccesser

7194 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

REdim.Learning Over a year ago

Thanks, this sort of solved the problem. I exported as excel, as in your first suggestion. Then opened the excel file and saved as csv (as I needed that format over "acSpreadsheetTypeExcel9" ).

gknapper · Accepted Answer · 2017-09-19 18:09:59Z

1

I just ran into this problem myself, and found a great work around. It doesn't save as a .csv, but you can save as a comma delimited .txt file.

Use the export wizard on the External Data tab to export your query as a .txt file without formatting.
Once the file is exported you get a dialogue box asking if you want to save export steps. Click the box and save the export.
There is an action available in the Macro wizard called "Run Saved Import/Export." Select this action and choose your saved export from the dropdown menu.

answered Sep 19, 2017 at 18:09

gknapper

111 bronze badge

Comments

Phil Kelsey · Accepted Answer · 2022-06-02 16:01:52Z

0

Very frustrating that even now I cant seem to make Access export a simple csv file. I do not know why they think I need pretty formatting. Try this: open Excel, Click Get Data, From Database, From MicroSoft Access Database. Select the Access Database you wish to export from. Select the table/query we want saved as an csv. This will set up a link to this table. Once imported, save the Excel file to an csv file.

answered Jun 2, 2022 at 16:01

Phil Kelsey

657 bronze badges

Collectives™ on Stack Overflow

Export Access Query WITHOUT Formatting

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related