Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Posts
    16

    Unanswered: Export Table To Csv File By Date

    The following code exports a table to CSV files by date

    Code:
    Option Compare Database
    Option Explicit
    Private Sub Command0_Enter()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim var As Variant
        Dim strSQL As String
        Dim strFilename As String
        
        strSQL = "SELECT DISTINCT orders.orderDate"
        strSQL = strSQL & " FROM orders"
        strSQL = strSQL & " ORDER BY orders.orderDate;"
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
    
        If Not ((rs.EOF) And (rs.BOF)) Then
            rs.MoveFirst
            strSQL = ""
            Do While Not (rs.EOF)
    
                strSQL = "SELECT *"
                strSQL = strSQL & " FROM orders"
                strSQL = strSQL & " WHERE orders.orderDate = #" & Format(rs!orderDate, "mm/dd/yyyy") & "#"
                                   
                CurrentDb.QueryDefs("QryExport").SQL = strSQL
    
                strFilename = "e:\utils\csvFiles\" & Format(rs!orderDate, "ddmmyyyy") & ".csv"
                DoCmd.TransferText acExportDelim, , "QryExport", strFilename
                       
                rs.MoveNext
            Loop
        End If
        
        MsgBox "Exporting Done.." & vbCrLf & "Dir..  " & Left(strFilename, 13)
    End Sub
    I would like to extend the above to include in the filename, the column "ref" to the filename (see
    attached zip file) thus 01082014-01.csv and 01082014-02.csv. So a unique csv file is created
    when there is a change of date and ref

    Would appreciate some help on this
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off Id recommend that you use the iso date (YYYYMMDD), in place of real da\te format for the fuile name (its self documenting when you open the directory its more obvious which file is which. arguably insertign the month NAME is even clearer, however you ten loose the ability to sort by name in a meaningful way


    how do you 'know' the column being exported, so it can be added to the file. I dont see any reference to it in the query

    if this query runs frequently during the day and what you in efect want is the most recent version AND also the history of earlier csv exports then consider adding the hours,minutes & seconds to the file name by modifying your format statement that creates the filename (strfilename) eg:-
    Code:
    strFilename = "e:\utils\csvFiles\" & Format(rs!orderDate, "yyyymmhhnnss") & ".csv"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2005
    Posts
    16

    clarification - Export Table To Csv File By Date

    Healdem.
    Point taken as far as date format is concerned.
    What I would like to achieve.
    From the table orders
    orderDate orderID ref name amt vat
    02/02/2014 56789 01 magdi 600 100
    22/06/2014 121221 01 hargre 6540 1000
    22/06/2014 658978 02 simon 25 2
    22/06/2014 12345 02 peter 15 1
    23/07/2014 567812 01 pinco 400 25
    01/08/2014 124578 01 alicia 25 9
    01/09/2014 567878 02 alan 126 5
    02/09/2014 567812 04 pinco 400 25
    02/09/2014 12345 02 peter 15 1
    02/09/2014 658978 04 simon 25 2
    02/09/2014 121221 05 hargre 6540 1000
    02/09/2014 794578 05 hargre 2540 2000

    I need to create csv files from the above based on unique date and
    ref and with filename showing date and ref. So from the above we
    should get 9 csv files in this format

    02/02/2014-01.csv
    22/06/2014-01.csv
    22/06/2014-02.csv
    23/07/2014-01.csv
    01/08/2014-01.csv
    01/09/2014-02.csv
    02/09/2014-02.csv
    02/09/2014-04.csv
    02/09/2014-05.csv

    Some help on how to produce the above result

  4. #4
    Join Date
    Aug 2005
    Posts
    16
    The table I submitted looks a bit confusing so I am
    re-posting leaving only the orderDate and ref fields

    orderDate ref
    02/02/2014 01
    22/06/2014 01
    22/06/2014 02
    22/06/2014 02
    23/07/2014 01
    01/08/2014 01
    01/09/2014 02
    02/09/2014 04
    02/09/2014 02
    02/09/2014 04
    02/09/2014 05
    02/09/2014 05

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you wan to pull out one order per day per CSV

    and ref is coming from where....?
    its down to how you marshall and or store your data

    how do you expect to pull out order no 3 on 02 09 14
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I guess you could try something like
    Code:
        dim RefNo as integer 'is a counter used to identify each order extracted on a day
        RefNo = 1 'initialise it at 1
        If Not ((rs.EOF) And (rs.BOF)) Then
            rs.MoveFirst
            strSQL = ""
            Do While Not (rs.EOF)
    
                strSQL = "SELECT * FROM orders"
                strSQL = strSQL & " WHERE orders.orderDate = #" & Format(rs!orderDate, "mm/dd/yyyy") & "#"
                                   
                CurrentDb.QueryDefs("QryExport").SQL = strSQL
    
                strFilename = "e:\utils\csvFiles\" & Format(rs!orderDate, "yyyymmdd") & "-" &  format(RefNo,"000") & ".csv"
                DoCmd.TransferText acExportDelim, , "QryExport", strFilename
                       
                rs.MoveNext
                RefNo = RefNo+1 #increment the number of orders extracted
            Loop
        End If
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2005
    Posts
    16
    The ref is the name of a field. Like this

    OrderDate ref
    22/06/2014 01

    The value in the ref field is 01

    So the exported csv file will look like this
    22/06/2014-01.csv

    So to export the entire table, there will be x number of csv files
    based on the date and ref fields

    Are we at crossed purposes ?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No
    ...but I'm not psychic...
    so if that value is coming from the recordset, then you need to pull the value from there and use it when you build the CSV filename
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2005
    Posts
    16
    To start at the beginning

    I have a Table (as per previous attached mdb)
    Table has a date field and a ref field
    Need to export these records to csv files
    based on unique values of date and ref

    You continue to ask from where I get the ref
    The ref value is the one in the ref field

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if that value is coming from the recordset, then you need to pull the value from there and use it when you build the CSV filename
    you are already pulling in the value of the order date from the recordset, so it shouldn't be beyond your skills to also pull in the refno from the same recordset, in place of the variable suggested in post #6.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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