Error when appending Excel files in Access RRS feed

  • Question

  • Hello comunity!

    I am just trying to append some excel files in Access for first time.... what is the problem?

    When i import the first table... the first column has a dot in the title ex: (Name.)
    When importing the data that dot disappears and i am left with (Name). The next ones that come later still contain the dot (Name.)

    so at the end (Name) doesnt match (Name.) and it creates an error.

    I am pretty desperate on this...Any idea on how could i solve this without changing all the titles from the other files? I have around 500! PLEASE!

    Thank you in advance :)



    Monday, May 18, 2020 10:33 AM

All replies

  • Follow this way of importing the Excel files into the Access database:

    1. Switch to the "External Data" tab in Access and locate the "Import & Link" group. Click on the "Excel" option. The "Get External Data – Excel Spreadsheet" dialog box opens.
    2. Click on the "Browse" button and use the "File Open" dialog box to locate the first Excel file you want to import. Click on the "Open" button to return to the "Get External Data" dialog box.
    3. Select the first radio button in the set of three choices to set how the Excel data will be used. This option creates a new table in the current database. Click the "OK" button to continue on to the Import Spreadsheet wizard.
    4. Click on the "Show Worksheets" button and identify the worksheet you want to use. If your spreadsheet file contains only one worksheet, your choice is simple. If it contains more than one, be careful to select the proper choice. Review the sample data at the bottom of the wizard to verify that you chose the correct worksheet and that the first row of the data consists of column headings. Click on the "Next" button to continue.
    5. Click on the "First Row Contains Column Headings" check box to tell Access to use these column labels as field names. You can see the proposed field names in the sample data at the bottom of the wizard. Click on the "Next" button to continue.
    6. Click on the first column of your data and use the "Field Options" above the sample data to provide information about the data. Use the "Field Name" box to override the column heading as the field name. Set the "Data Type" drop-down menu to "Text," "Memo," "Number," "Data," "Time," "Currency," "Yes/No" or "Hyperlink," depending on the column's contents. Use the "Do Not Import Field (Skip)" check box to omit a column. Use the "Indexed" drop-down menu to indicate whether the field contents should be indexed. Click on the "Next" button to continue.
    7. Define a primary key for your data import. By default, Access adds a primary key that it selects. You can override its choice or tell it not to create a key. Click the "Finish" button to continue.
    8. Rerun the Import Wizard, this time choosing to append data to your new table and specifying the name of the next file you want to import. Append your data and launch the wizard again for the next file. Repeat this process until you have imported all your spreadsheet data.


    Monday, May 18, 2020 10:46 AM
  • Thank you for your reply, but It does not currently answer my question....

    The issue is that all the files have as first column: "IMO/LR/IHS No." (with a dot). But when i first import the first file access automatically eliminates it. Then when i try to import the rest... "IMO/LR/IHS No." doesnt match "IMO/LR/IHS No"....

    What could I do?

    Monday, May 18, 2020 10:59 AM
    1. Column Headings in the EXCEL file should match the field names in the ACCESS table. So change one or the other so they are the same.
    2. Never ever ever use special characters (such as / \ . : ; ? etc.) in column headings or field names. EXCEL may not mind but ACCESS does mind. So IMO/LR/IHS No. should become IMOLRIHSNo or IMO_LR_IHS_No is OK too. Whatever, the ACCESS field name should match the EXCEL colmun heading.
    Monday, May 18, 2020 1:16 PM