Share via

Importing data from Excel into an existing Access table

Anonymous
2013-12-30T15:41:50+00:00

We receive data from our customers in Excel files and transfer that data into Access.  The format's a simple spreadsheet-as-flat-file, but the columns and content vary widely from client to client. We have a table in an Access shell file that contains all the columns that correspond to the various types of data we receive in these Excel files from our various clients (about 50 columns).  When we receive an Excel file from a client, we remove the columns from the spreadsheet that don't correspond to a column in our Access table (data we don't need, like proprietary IDs and timestamps), and we make sure the remaining column names in the spreadsheet do correspond to the proper columns in the Access table.  Then we use Get External Data / Import / Select Spreadsheet / First row contains column names import in Access. 

Until we switched over to Access 2010, this retained the Access table format and only inserted the data available based on the column names in the Excel file. I haven't figured out how to do that in Access 2010 - no matter what we do, it seems to reformat the table structure to match that of the Excel file. 

I've considered linking and appending, but that adds to our overhead, since we'd have to change the append query each time to match the particular content of the Excel file (can't wildcard the column names).  So, I'd like to know if I'm just missing something, or if this functionality has been removed.

What's the analogous import process in Access 2010?

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2013-12-30T16:59:46+00:00

    Activate the External Data tab of the ribbon.

    Click Excel in the Import group.

    Click Browse...

    Select the Excel file that you want to import, and click Open.

    Select the 2nd radio button: "Append a copy of the records to the table:". This is essential!

    Select the existing target table from the dropdown list.

    Click OK.

    The Import Spreadsheet Wizard starts; you should be able to click Finish immediately.

    4 people found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2017-06-04T15:30:02+00:00

    The way I would do this is with Link and Append. But you don't need to change or store the Append query. Create a table like this:

    tblClientFormat

    ClientFormatID (PK Autonumber)

    ClientID

    SourceColumn

    ColumnOrder

    Then you have a form with a combo to select the client and a button to run the import.

    Dim DB AS DAO.Database, rs AS Recordset

    Dim strSQL As String, strSQL1 As String

    SET db AS CurrentDB()

    DoCmd,TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "importTable", "C:\test\test.xlsx", ", True

    strSQL = "INSERT INTO tablename (field1, field2, field3,...) "

    strSQL1 = "SELECT SourceColumn FROM tblClientFormat WHERE ClientID = " & Me.cboClient & _

                      " ORDER BY ColumnOrder;"

    SET rs as db.OpenRecordset(strSQL1)

    strSQL1 = "SELECT "

    With rs

        .MoveFirst

        Do While NOT .EOF

            strSQL1 = strSQL1 & .fields("SourceColumn") & ", "

            .MoveNext

        Loop

    End With

    strSQL1 = Left(strSQL1,Len(strSQL1)-2) & " FROM importable;"

    CurrentDB.Execute strSQL, dbFailOnError

    Note: this is untested code off the top of my head but should give you the idea. It will generate an Append query taking the column names from the spreadsheet and appending them to the matching column in your table. There will be no need to modify the table eliminating columns and changing column names. Just maintain tblClientformat so it matches the spreadsheet.

    1 person found this answer helpful.
    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2017-06-04T07:59:57+00:00

    Perhaps you can create a Delete query with criteria to delete the empty rows, and run that query after importing an Excel file.

    0 comments
  4. Anonymous
    2017-06-04T03:06:33+00:00

    This WORKS, but it overlooks the problem of Access importing hundreds of blank records from the spreadsheet or how to prevent that.

    0 comments