Macro Export table to CSV

[STIRRELL]

Hi all,
I am working on a query and need to export the table as CSV and I was surprised to see that was not an option in access. I have been trying many different options to get it to work. ( export as text etc). Short of exporting to excel then converting an extra step I do not want to have to do. Does anyone have a macro that will work? Also is there another version to eliminate the . Some reports I need the headers and others I do not. thanks for your help in advance.

 

[STIRRELL]

sorry it was eliminate the header row

 

[Micron]

Not sure if you're expressly looking for a code solution or are doing so only because you couldn't find the right way when using the Export options from the ribbon. There definitely is the option to export delimited text without headers in Access 2016 if using the wizard. You choose Text File from the Export menu but don't choose the formatting and layout option box. No headers is the default. You could save the export specification but you'd need one per report.

Or are you looking for a code solution only, and if so, one that builds the specification dynamically or one that runs a stored spec?

 

[STIRRELL]

Hi- I tried the option above and its still saving as a txt file and unfortuatley for iipay the file needs to be a csv file extension. Also when I try text the formatting looks funny. I need the data to look like a normal excel spreadsheet with data in clean columns and not scattered like the text file More like an excel doc but with the csv extension. Any thoughts? I am looking for an automated solution. Do you know of an add in? I have not had any luck finding one so far.

 

[Micron]

You can select text as the import and change the file extension to csv. However, the resulting Excel file will put the data as fixed width when you don't want delimited data. That is still in a 'column' but not columns if that is what you meant.
Since you want an automated solution, I don't think this approach will work for you. Did you research ms access vba export query to csv file ? There are tons of code examples to choose from. Could change 'query' to report, table or whatever it is you're wanting to export. Why not do that and find one that is closest to what you need. No real idea what that is here:
- using file dialog (or not) to pick a file
- include headers for this object but not for this one
- looping over a set of objects (e.g. reports) or
- running several sql statements or recordsets
etc.