Export Access Tables to .CSV in User Inputted Folder?

[paulcook]

I have an Access database that has 13 tables which I need to export into 13 .csv files with the same name as the tables. I am attempting to do this with VBA in Access 2003.

So far I have used DoCmd.TransferText and hard coded the file names into the VBA code. I would like to use an "InputBox" to require the user to input at date which would be used to create a folder on a network drive which the .csv files would be created in. Something along the lines of this.

-Box comes up, already got this to occur (kind of?), and asks for date
-User inputs date, 2005-09-20, a folder called "Data Submission 2005-09-20" is created in this folder "\\CSServer\SPShare\MIS\Future\Data Submission 2005\"
-All Access table would be exported, "table1" would be "table1.txt", as delimited files with the .txt extension.

I don't have much experience in VBA but this is what I have gotten so far.

Sub ExportJTAtoText()

NewFolderName = InputBox("Input today's date in the YYYY-MM-DD format", "Create Folder", "Please enter a date")



DoCmd.TransferText acExportDelim, , "informix_app", "c:\Youth\informix_app.txt", True
DoCmd.TransferText acExportDelim, , "informix_case_tbl", "c:\Youth\informix_case_tbl.txt", True
DoCmd.TransferText acExportDelim, , "informix_clnt", "c:\Youth\informix_clnt.txt", True
DoCmd.TransferText acExportDelim, , "informix_partic_grnt", "c:\Youth\informix_partic_grnt.txt", True
DoCmd.TransferText acExportDelim, , "informix_wia_app", "c:\Youth\informix_wia_app.txt", True
DoCmd.TransferText acExportDelim, , "root_jtpa_supp_data", "c:\Youth\root_jtpa_supp_data.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_agcy", "c:\Youth\root_wia_agcy.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_base_wg", "c:\Youth\root_wia_base_wg.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_ipd_actvy", "c:\Youth\root_wia_ipd_actvy.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_ipd_app", "c:\Youth\root_wia_ipd_app.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_ipd_case", "c:\Youth\root_wia_ipd_case.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_ipd_folup", "c:\Youth\root_wia_ipd_folup.txt", True
DoCmd.TransferText acExportDelim, , "root_wia_ipd_goal", "c:\Youth\root_wia_ipd_goal.txt", True

End Sub

Those lines do not wrap in the VBA window.

This would save alot of time over exporting each table manually.

TIA,
Paul

 

[PHV]

A starting point (typed, untested):
Sub ExportJTAtoText()
CurrentDate = InputBox("Input today's date in the YYYY-MM-DD format", "Create Folder", "Please enter a date")
If Not IsDate(CurrentDate) Then Exit Sub
' Why not CurrentDate = Format(Date, "yyyy-mm-dd") ?
NewFolderName = "\\CSServer\SPShare\MIS\Future\Data Submission " & CurrentDate
MkDir NewFolderName
For Each TableName In Array("informix_app", "informix_case_tbl", "informix_clnt", "informix_partic_grnt", "informix_wia_app", "root_jtpa_supp_data", "root_wia_agcy", "root_wia_base_wg", "root_wia_ipd_actvy", "root_wia_ipd_app", "root_wia_ipd_case", "root_wia_ipd_folup", "root_wia_ipd_goal")
SysCmd acSysCmdSetStatus, TableName
DoCmd.TransferText acExportDelim, , TableName, NewFolderName & "\" & TableName & ".txt", True
Next
SysCmd acSysCmdClearStatus
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[paulcook]

PH,

I can't thank you enough for the help. It worked like a charm! I will also consider your suggestion of just using the date for when the VBA is run instead of asking for the input. I guess that I am just so used to creating the folder with the date I didn't consider any other way.

I had spent quite abit of time on the problem and had reached a dead end. I will study what you have given me to expand my limited knowledge of VBA.

Thanks again,

Paul

 

[4281967]

PVH,
this was exactly what I needed as well - however when I export to I csv, my text fields are dropping the leading zeros. I really need them since one field in particular is a bank routing number. Any suggestions?

Here is my implementation of your code example:

Private Sub cmdExport_Click()

    Dim CurrentDate, NewFolderName, TableName
    CurrentDate = Format(Date, "yyyy-mm-dd")
    
    NewFolderName = "C:\ACH\Exports\" & CurrentDate
    MkDir NewFolderName


    For Each TableName In Array("Monthly 1st", "Monthly 15th", "Quarterly - Month 1 - 1st", "Quarterly - Month 2 - 1st", "Quarterly - Month 3 - 1st", "Quarterly - Month 1 - 15th", "Quarterly - Month 2 - 15th", "Quarterly - Month 3 - 15th")
        SysCmd acSysCmdSetStatus, TableName
        DoCmd.TransferText acExportDelim, , TableName, NewFolderName & "\" & TableName & " - " & CurrentDate & ".csv", True
    Next
    
SysCmd acSysCmdClearStatus

    MsgBox "ACH File Export Complete", , "Finished"

End Sub

 

[PHV]

Are you doing calculations with the bank routing number ?
If the answer is no then the field should not be defined as numeric in the table ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[4281967]

no calculations are done w/ the routing number - I have it defined as text in the table. I think this is a problem w/ Excel, as if I view the .csv file with notepad, the leading zero's are being exported. For some reason when excel opens it, it doesn't retain the text formatting. Luckily I could work around this by exporting the files as .txt (the banks server would accept either format)
Thanks.