exporting access table data to excel (1 Viewer)

cb1

New member
Local time
Today, 13:50
Joined
Jan 21, 2021
Messages
5
Are there any common issues/challenges when it comes to exporting a full table of data from an access database out to a currently blank Excel xlsx file? We need to be sure an export of a large table with multiple fields of different data types, exports completely and successfully to a blank excel file - as this data will be used for a data import project into a 3rd party database. I was just trying to establish any common challenges with such an operation to ensure we do not fall foul to common issues.
 

ebs17

Well-known member
Local time
Today, 14:50
Joined
Feb 7, 2020
Messages
1,989
There are practically never any problems when exporting, but there are certainly problems with subsequent imports.
this data will be used for a data import project into a 3rd party database
In an Excel table, there is no guaranteed data type security for the entire column.

If you have the option, use a text file (CSV). Specifications or Schema.ini are then used here to precisely define how the text file (text table) is to be read. In addition, the text file is slimmer, and every conceivable database should be able to handle text files; this has been a standard since data exchange existed.
 
  • Like
Reactions: cb1

cb1

New member
Local time
Today, 13:50
Joined
Jan 21, 2021
Messages
5
Thanks for the response ebs17, could you elaborate a little on this please, and how text files help preserve the 'data type security' for all records.
Will suggest using a text file for the export destination and to use this for the import.
In an Excel table, there is no guaranteed data type security for the entire column.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:50
Joined
May 7, 2009
Messages
19,246
you need to try/test it first.
you may try:

Docmd.TransferSpreadsheet

command.
 
  • Like
Reactions: cb1

ebs17

Well-known member
Local time
Today, 14:50
Joined
Feb 7, 2020
Messages
1,989
Excel doesn't know data types, only formats - VBA in Excel already has data types. A data type can be interpreted from the contents and format of a cell, but each cell can contain something different.
When a read database access is made to an Excel table, the contents of the first rows (the first 8 rows in Access by default) are usually analyzed and the data type for the column is then determined. If a column that was interpreted as a number column then has alphanumeric content in later rows, you quickly have a problem that is more difficult to solve.

A text file contains plain text, so everything is just text. However, an import/export specification determines how these text elements are to be interpreted and read. If these text files are then standardized and generated automatically, you have a very high level of security that they are free of errors.
So I'm focusing on TransferText and related methods.

You can believe me, because only ruins can tell about war.
 
Last edited:
  • Like
Reactions: cb1

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:50
Joined
May 7, 2009
Messages
19,246
double check the output and format it as you wish, it's not a big deal.
that is called proof-reading. there is no place for complacency.

if you need you can do it by VBA:
Code:
Sub ExportTableToExcel(ByVal TableName As String, ByVal ExcelFile As String, Optional ByVal SheetName As String = "")
' Note:
' ExcelFile should contain the full path and excel workbookname.xlsx
'

    Dim appExcel As Object
    Dim wb As Object
    Dim ws As Object
    Dim rs As Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim j As Integer
    
    ' Create instance of Excel application
    Set appExcel = CreateObject("Excel.Application")
    'appExcel.Visible = True ' You can set this to False if you don't want Excel to be visible
    
    ' Create a new workbook
    Set wb = appExcel.Workbooks.Add
    
    ' Define your table query
    strSQL = "SELECT * FROM [" & TableName & "];"
    
    ' Open recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
    
    ' Add a new worksheet
    Set ws = wb.Worksheets.Add
    
    If Len(SheetName) <> 0 Then
        ws.Name = SheetName
    Else
        ws.Name = TableName
    End If
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
    End If
    
    ' Write field names to Excel
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Write recordset data to Excel
    i = 1
    Do While Not rs.EOF
        i = i + 1
        For j = 0 To rs.Fields.Count - 1
            ws.Cells(i, j + 1) = rs.Fields(j)
        Next j
        rs.MoveNext
    Loop
    
    ' Save the workbook as xlsx file
    wb.SaveAs ExcelFile ' Change the path as needed
    
    ' Clean up
    wb.Close
    appExcel.Quit
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set appExcel = Nothing
    
    MsgBox "Export Complete"
    
End Sub
 
Last edited:
  • Like
Reactions: cb1

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:50
Joined
Feb 19, 2002
Messages
43,491
Another way to check the output with less code is to use TransferText or TransferSpreadsheet to create the export. I also prefer TransferText to create a .csv file but if your exchange partner is dictating a specific format, then that is what you need to provide.

Once you have exported the data, you can link to the spreadsheet and then run a query that compares each field in the two tables to find differences.
 
  • Like
Reactions: cb1

ebs17

Well-known member
Local time
Today, 14:50
Joined
Feb 7, 2020
Messages
1,989
Once you have exported the data, you can link to the spreadsheet and then run a query that compares each field in the two tables to find differences.
Has there ever been a documented case of the export containing something other than the source table when using a standard method?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:50
Joined
Feb 19, 2002
Messages
43,491
Not that I know of but the OP wanted confirmation. That's an easy way.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:50
Joined
Feb 19, 2013
Messages
16,674
Only thing I would add is that Access does not export any format properties, only the values, so you need to confirm what format is required in excel. Typical examples are a double value in Access of say 1.2345 and formatted to 2dp will appear as 1.23 but will export as 1.2345 or a date field with a time value but formatted to only show the date will export the underlying date and time value.

I would expect your 3rd party to provide a specification stating the order and format of all columns - might be as simple as an example workbook showing some example data.

Personally I would export as a query so you can set the order of columns and use the format and similar functions to present the data in the right order and with the right value formats
 
  • Like
Reactions: cb1

raziel3

Registered User.
Local time
Today, 08:50
Joined
Oct 5, 2017
Messages
278
I've had a problem with this recently.


Filtering columns was the solution to my problem.
 
  • Like
Reactions: cb1

Users who are viewing this thread

Top Bottom