Microsoft Access database export to excel/xml with 5 million rows-CodePudding

I am very new to the Microsoft Access database (table) and trying to convert the Access table to the format I can use as the pandas data frame in Python.

Here is the situation, the database is about 5 million rows and every time I tried to export to excel or XML it was only able to convert the first 1 million rows. I was wondering two things:

whether if there exists a format I can export all rows without row limitation?
if the above is not possible, then is there a method to break the Access database for every 1 million rows into separate files, and I can export those to different excel/xml files (copy to clipboard would exceed the limit here)?

Any thought is welcome!

[Update]: export to txt file works for me and can do 10 million records. The way to avoid generating converting error is to use the comma "," as the seperator. Thanks all!

CodePudding user response：

Well, in access, you should be able to export that table to csv, and not try to export to excel. I don't believe that the .csv (text) export has a limit.

From access, ctrl-g (jumps to command line/debug window), and then type in this:

 docmd.TransferText acExportDelim,,"LContactHistory","c:\test\mybig.csv", True

So, the above should export the file. However, Access often will 'freeze' up when you do the above - but it is worth a try. (above seems ok for about 1 million rows).

Next up?

Read the table row by row, output to a text file. This tends to be the best for REALLY big files. and the bonus points are that the VERY old legacy code in VBA for file output operations goes back to the DOS days - they are STUPID FAST high performance.

the other advantage of a loop, is you can give access (and the UI message pump) a gulp of air so to speak - this is what tends to freeze up anyway.

So, do this in native access VBA code - it will work the best.

This code will work:

Sub OutPutBig()

    Dim strOutFile          As String
    Dim intOutFile          As Integer
    Dim strMyTable          As String
    Dim rstData             As DAO.Recordset
    Dim Counter             As Long
    Dim CounterUp           As Long      ' value to update progress
    
    CounterUp = 20000      ' update display every 20,000
    Counter = 0
    
    strMyTable = "LContactHistory"
    strOutFile = "c:\test\big.csv"
    
    intOutFile = FreeFile
    
    Open strOutFile For Output As #intOutFile
    
    Set rstData = CurrentDb.OpenRecordset(strMyTable)
    
    Dim sOutline As String
    
    ' output first row of field names
    sOutline = ""
    Dim fField As DAO.Field
    For Each fField In rstData.Fields
       If sOutline <> "" Then sOutline = sOutline & ","
       sOutline = sOutline & qu(fField.Name)
    Next
    Print #intOutFile, sOutline
    
        
    Do While rstData.EOF = False
       sOutline = ""
       For Each fField In rstData.Fields
          If sOutline <> "" Then sOutline = sOutline & ","
          sOutline = sOutline & qu(fField.Value)
       Next
       Print #intOutFile, sOutline
       rstData.MoveNext
       
       Counter = Counter   1
       If Counter Mod CounterUp = 0 Then
           Debug.Print Counter
           DoEvents
       End If
       
    
    Loop
    
    Close (intOutFile)
    rstData.Close
    
    Beep
    Debug.Print "done export - " & Counter
    
     
End Sub

Public Function qu(s As Variant) As String
   
   If IsNull(s) Then
      s = ""
   End If
   
   qu = Chr(34) & s & Chr(34)
   
End Function