Export Formatting to CSV

[jarett]

I am fumbling through some steps to automate an export from access to a csv file. I have created a macro to run a query when access opens;

[CODE=vba][CODE=vba]'------------------------------------------------------------
' AutoExec
'
'------------------------------------------------------------
Function AutoExec()
On Error GoTo AutoExec_Err

    DoCmd.OutputTo acOutputQuery, "po_detail3", "MS-DOSText(*.txt)", "P:\Jarett\smtest1.csv", True, "", 1, acExportQualityPrint


AutoExec_Exit:
    Exit Function

AutoExec_Err:
    MsgBox Error$
    Resume AutoExec_Exit

End Function

[/CODE][/CODE]

All is good but the csv file that is produce has some weird formatting with all values in the first column. Also couldnt get the XL2BB add in on my excel.

My end goal is to automate this export the easiest way maybe through a task scheduler, only other issue is deleting the existing file before the new file is saved. (might be a new thread)

 

[6StringJazzer]

What is the query you are using to produce this output? If all values are in the first column, it seems that you are not using commas to separate the fields and instead are using spaces and vertical bars to emulate a table.

 

[jarett]

The query produces the output from 3/4 different tables in the correct format. When I connect the query in excel it comes over in separate fields like it should, it just seems like when I go to a csv file or try to export the query through a macro that it goes haywire.

 

[Joe4]

Can you post the data, as it appears in your Access query (so show it to use from the Query view)?

Also, you appear to be exporting to a Text file format, instead of a CSV file format:
"MS-DOSText(*.txt)"
Why haven't you chosen a CSV option?

 

[jarett]

Attached is the query results and in the autoexec macro they didnt have the options to put the "Output Format" as csv.

 

[Joe4]

It has been a few years since I exported CSV files out of Access, but I think I used to always use "TransferText" instead of "OutputTo".
Have you investigated trying to use that instead?

Also, I found this code that writes directly to a CSV file: Export Access query results to csv

 

[jarett]

I did some research on the "TransferText" but that option is not available when I use the macro builder, I get completely lost when I try to go the VBA route cant seem to find a dummied down explanation. I create a module but don't know how to run it.

 

[Micron]

Make sure you have selected "show all actions" from macro ribbon, find "ImportExportText" and see if the transfer type option you need is there. Note the "tell me more" link for topic help.

You don't run modules, you run procedures in modules and those procedures are triggered from events. A user driven event might be a button click or combo selection. You can also run code based on updates to data or other events. I don't think an AutoExcec macro is the way I'd initiate any kind of transfer though. If you get a macro working, it might present you with the option to convert to code if you're curious.

 

[jarett]

Got the format to come out correctly through a macro, just need to figure out how to save over the existing file if I name the macro "AutoExec". Micron you mentioned you dont think "AutoExec" is not the way to go, my thought process was to use the task scheduler app to schedule opening up access and then access would run the export. Is this not a good train of thought? I know I need to figure out the code/VBA part because that's the way it seems I need to go instead of the macro.

 

[Micron]

I forgot about the automated comment from before. The possible downside is that if anyone forgets or doesn't know of the autoexec and opens the db normally it will run the transfer regardless of whether or not it is a good or bad thing. You can live with that, or you can do the following:
- Task Scheduler opens db using a command line switch in the shortcut path
- macro first step would be to run code
- the code first tests the database Command property. If it is not a zls ("") then TS opened the file, so run the transfer code portion. If it is "" then someone manually opened the db so don't run the transfer code portion.
During initial testing, I'd have nothing more than a simple message box run while I get the TS & command line switch part setup right.

Don't know what method you ended up with, but I believe TransferSpreadsheet appends data, not over-writes it. So if all these transfer methods are the same in that aspect, then I believe you need to run a delete query first if the data is in Access. If it's in Excel, then I don't know what the approach is. Most of my Access data transport experience is in ODBC so I'm afraid I'm not much help with your issue.