Results 1 to 10 of 10

Thread: Export CSV

  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: Export CSV

    I'm creating a table and exporting it as a CSV file. This works most of the time but every now and then a some rows (20 - 100) in my CSV are out of order. It is always one big chunk that should be, let's say, rows 20 -50 that appear as rows 1 - 30 then the "real first row appears. There is a hole in the data where the chunk belongs and I have to cut and paste top repair the file. I can not figure out where the problem is generated because it is very random. Is there any known bugs with exporting CSV files?

    Here is what I'm doing. Sorry for all the code.

    In my main form (btn click)
    Code:
    ' Dynamically create new table schema based on the input table data
            strNewTable = "DataOut"
            strSql = "SELECT * FROM ScaledData"
            CreateTable db, strNewTable, strSql
     
            ' Combine data from two original tables into one new table
            FillTable db, "RealTimeData", "ScaledData", strNewTable
     
            outFile = Left(filename, Len(filename) - 3) & "csv"
            ' Create CSV file from new table
            CreateCSV outFile, strNewTable
    Create Table
    Code:
     
    Sub CreateTable(db As Database, tblName As String, strSql As String)
        Dim tdf As TableDef
        Dim rs As Recordset
        Dim rs1 As Recordset
        Dim headFound As Boolean
        Dim idx, pointNum As Integer
        Dim fieldName As String
     
     
        Set rs = db.OpenRecordset(strSql, dbOpenDynaset)
        Set rs1 = db.OpenRecordset("SELECT pointNo,description FROM Points")
     
        Set tdf = db.CreateTableDef(tblName)
        Set dbs = rs.Fields
     
        With tdf
            .Fields.Append .CreateField("CLOCKWORD", dbText)
     
            For Each fld In dbs
                rs1.MoveFirst
                headFound = False
     
                Do While Not headFound
     
                    idx = InStr(fld.SourceField, "_")
                    pointNum = Mid(fld.SourceField, idx + 1, Len(fld.SourceField) - idx)
     
                    If rs1![pointNo] = pointNum Then
     
                        fieldName = rs1![Description]
                        fieldName = Replace(fieldName, ".", "")
                        .Fields.Append .CreateField(fieldName, dbText)
                        headFound = True
     
                    End If
     
                    rs1.MoveNext
     
                Loop
            Next fld
     
            .Fields.Append .CreateField("-1", dbText)
        End With
     
        db.TableDefs.Append tdf
     
        rs.Close
        rs1.Close
     
    End Sub
    Fill Table
    Code:
     
    Sub FillTable(db As Database, tblIn1 As String, tblIn2 As String, tblOut)
        Dim rs1 As Recordset
        Dim rs2 As Recordset
        Dim rs3 As Recordset
        Dim dbs As Object
        Dim fld As Field
        Dim x As Integer
        Dim i As Integer
     
        ' Create recordsets that contain the new data
        Set rs1 = db.OpenRecordset("SELECT * FROM " & tblIn1, dbOpenDynaset)
        Set rs2 = db.OpenRecordset("SELECT * FROM " & tblIn2, dbOpenDynaset)
        Set rs3 = db.OpenRecordset("SELECT * FROM " & tblOut)
     
        Set dbs = rs2.Fields
        rs1.MoveFirst
        rs2.MoveFirst
     
        Do While Not rs2.EOF
     
            rs3.AddNew
            rs3![CLOCKWORD] = rs1![RealTime] / 20
     
            x = 1
            For Each fld In dbs
                rs3.Fields(x) = fld.Value
                x = x + 1
            Next fld
     
            rs3.Update
            rs1.MoveNext
            rs2.MoveNext
     
        Loop
     
        rs1.Close
        rs2.Close
        rs3.Close
    End Sub
    Create CSV
    Code:
     
    Sub CreateCSV(filename As String, tbl As String)
        If Len(Dir(filename)) > 0 Then
            Kill filename
        End If
     
        DoCmd.TransferText acExportDelim, , tbl, filename, True
    End Sub

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need an order by clause in your initial select statement if you want the data ordered a certain way. It's not necessarily stored in the same order that it displays when you open a table.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2005
    Posts
    333
    You may be right but I didn't use an Order By clause because the table is initially empty then I'm indexing and adding new rows to the table. So, at least in my mind, the first row has time =1 the second row has time= 2, etc. Which would order the table as it was built. This may be one of those relational database things that I always over look.

    I'll try your idea. Thanks

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Actually, it doesn't matter what order you put the data into the new table,
    you could still end up with the same problem.

    Maybe you should try exporting a query (with Order By) instead of the table.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there any reason why you're creating a new table instead of just creating a query for output?

    If there is, why don't you try query the results of the table to give them a logical order.

    I've never had a problem like this before when dealing with a CSV file, but if you can pinpoint a set of data that is causing this problem then that'd be a lovely starting point.

    I know you say it's sporadic but if you find it happens on a set of data - re-run the export and see if it happens again (might be a syntax/recordset issue).
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Yeah.. this all goes back to
    The physical order of data in a database has no meaning.
    - Brett Kaiser
    If you want to be certain your data is in a certain order, use a query.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Feb 2005
    Posts
    333

    Now I'm really confused

    I changed my createCSV sub to the code below. The new version creates a new table based on a query sorted by clockword (time). I'm getting the same results. The weird thing is that sometimes the original table looks fine but the sorted file is out of order. Again its really random. Now if I open the table after it's created and click and sort by the clockword column, the data is proprely ordered.
    Code:
     
    Sub CreateCSV(filename As String, tbl As String)
        Dim strSql As String
        Dim tblOrder As String
        
        tblOrder = tbl & "Ordered"
        
        strSql = "SELECT * " & _
                 "INTO " & tblOrder & _
                 " FROM " & tbl & _
                 " ORDER BY CLOCKWORD"
                 
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSql
        DoCmd.SetWarnings True
        If Len(Dir(filename)) > 0 Then
            Kill filename
        End If
        DoCmd.TransferText acExportDelim, , tblOrder, filename, True
    End Sub

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Once again the physical order of data in a database has no meaning.

    You're still outputting the table, rather than outputting the query on the table.

    Code:
    Dim Example As String
    
    Example = "SELECT * FROM tblOrder ORDER BY CLOCKWORD"
    
    DoCmd.TransferText acExportDelim, , Example, filename, True
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2005
    Posts
    333
    Example = "SELECT * FROM tblOrder ORDER BY CLOCKWORD"

    DoCmd.TransferText acExportDelim, , Example, filename, True
    I didn't know that you could do that.

    I guess that I can see the difference. Even though I made a table based on a query, it is still a table and the Physical order of the records may not reflect how the data is actually stored.

    I had a better fix. I had my boss stand behind me so that I could show him the "weird" behavior and the sort order was consistantly correct. I guess that I just need him to stand behind me every time I run this app and I will never have problems.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well I'd like to point out that I have not tested the above code at all, it's all theoretiacl, but I can't see a reason why it shouldn't work - let me know how it goes!
    Quote Originally Posted by campster
    I had my boss stand behind me so that I could show him the "weird" behavior and the sort order was consistantly correct. I guess that I just need him to stand behind me every time I run this app and I will never have problems.
    I believe that's called Sod's Law
    George
    Home | Blog

Posting Permissions

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