Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    150

    Unanswered: Exporting to csv

    Ok, so I figured out a way to convert the tables into Quickbooks and for about the past year I've exported the table manually. Now I would like to do it automatically so I can take a vacation and others can do it.

    I have to export a table into a csv file, which is basically a text file but is separated by commas instead of tabs.

    I tried using DoCmd.Outputto acOutputTable, qbtblName, acFormatXLS, qbFileName

    qbtblName = "tblqwikconvert"

    qbFileName = "c:\qwikie.xls"

    ***Note: I have also tried doing this with it being exported as a text file

    Let me know if you need anymore information

  2. #2
    Join Date
    May 2005
    Posts
    150
    I just tried transfertext as well.. i am awaiting the results to see if it works

  3. #3
    Join Date
    Feb 2006
    Location
    NJ
    Posts
    3
    I just whipped up some quick code and successfully exported a table to a file on my C drive in CSV format. Here is the code behind my OnClick event:

    Code:
    DoCmd.TransferText acExportDelim, "lead_notes_export_spec", "lead_notes", "C:\temp.csv"
    
    OR
    
    DoCmd.TransferText acExportDelim, "{ExportSpecName}", "{TableName}", "{DestinationFileName}"
    After I created my form, I went and created an export specification. The way I did that, and there are probably easier ways, was to go to the table I wanted to export and right click, and then go to export. Then I put in a file name and chose TEXT/CSV as the file type, and chose export. In that screen I put in my formats and then clicked on "Save As" and then named it "lead_notes_export_spec", like in the above code. After it was saved I cancelled the export and went to my form and clicked my button, and voila, it created my file on my C drive.

    -jim

  4. #4
    Join Date
    Feb 2006
    Posts
    213
    Is there away of doing this so its an automated event at the end of an on click process? So far ive got my database importing, processing and now i want it to export to a text file. I want to be promped to choose a save name for the file.

    Thanks.
    Last edited by marleyuk; 02-21-06 at 07:07.

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need to use the TransferText method of DoCmd. To help you with the parameters, I would suggest creating a macro to do it. In my opinion, the descriptions in the macro setup better describe the parameters it is looking for. Once you have it successfully working as a macro, then it is very easy to turn it into code. In code, type:

    DoCmd.TransferText and then enter the values you did for the macro.

  6. #6
    Join Date
    Feb 2006
    Posts
    26

    Cool Exporting to Csv in a cool way

    Hope this will help.
    If you want to go really posh and would like to give the users the power to output where ever with a interface to select a destination folder as well as predicting the output file name and displaying within the form then read further:
    The idea is to make a form first with a some text box's named:

    'selfilename',
    'slash' with default value '\' - enabled=no
    'format' with default value '.txt' - enabled=no
    'Filename' with no default - enabled=no
    command button named 'command0'
    command Button named 'Startimport'

    Now make a module and save the code 'APIDialogBOX'(attachment) into it.

    Then go back to your form and open under the properties of 'command0' on click event type:
    Private Sub Command0_Click()
    Me.selfilename = BrowseFolder(CurDir & "\*.mdb")
    End Sub

    Now go to the properties of 'Startimport' and under click event add this code

    Note: this code is based on that you first dump the data into a table which needs to exported by a 'make a table query' this gives you the flexibility to change the restriction whenever you want.So make sure you make a query and save it on your database window, I named it 'QRYExportMakeTabRoutine' which is a make a table query type.)

    Private Sub StartImport_Click()
    Dim rst As New ADODB.Recordset
    Dim strNewTableName As String
    Dim strExistingTableName As String
    Dim strFileType As String

    On Error GoTo ErrorHandler
    If IsNull(Me.selfilename) Then
    MsgBox "You must select a Path or Destination Folder.", vbExclamation, "Error"
    Me.selfilename.SetFocus
    GoTo Done
    End If
    DoCmd.OpenQuery "QRYExportMakeTabRoutine", acNormal, acReadOnly
    DoCmd.TransferText acExportDelim, "SOexpo", "ExpoTemp", Me.selfilename & Me.slash & Me.Filename & Me.format, True, "", 50000
    DoCmd.DeleteObject acTable, "ExpoTemp"


    MsgBox "Export Was Successfully Completed. Check Your Destination Location for the File", vbExclamation, "Export Status"
    GoTo Done

    ErrorHandler:
    MsgBox Err.Description
    Done:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Application.Echo True

    End Sub

    Save and come out of VB, Now go and run just the query- make a table and do a manual routine to export
    when you choose to export a table then in the ExportDialogBox select what type you want to export as should be txt, then select if you want export with field name (I have choosen Yes) then under the advance tab select
    Field and txt sepraters and on the right hand side click save as 'SOexpo' , after that it should be available under SPEC to view. Cancel the routine and come out now go back to your form and try to export should work like a charm.
    And if it gives too many OK and cancel messages then go under tools/option/editfind/actionqueries and take the tick away

    Have fun!
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •