none
Import with some blank fields and default values RRS feed

  • Question

  • Good Morning,

    Need to import a Table periodically that has 4 rows of data. I also have a standard value for 4 other columns that need to be populated at the same time. Is there a way during the import the other fields can ve auto populated with the standard data or should that be handled in a separate step after the import. Either way is fine but I need a method for accomplishing this goal.

    Thank you for your assistance.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, May 17, 2016 1:33 PM

Answers

  • I provided the solution in my previous reply: set the Default Value property of the User field to "New" and that of the Location field to "Supply".

    On the External Data tab of the ribbon, in the Import & Link group, click Excel.

    Browse to the Excel workbook that you want to import.

    Select the second option "Append a copy of the records to the table", and select the target table from the dropdown.

    Click OK and confirm.

    The Asset etc. fields will be filled with the data from the worksheet, and the User and Location fields will be populated with their default values.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Tuesday, May 17, 2016 8:34 PM
    Tuesday, May 17, 2016 8:17 PM

All replies

  • If you append the records to an existing table (you could delete the existing records first if you wish), Access will automatically apply the Default Value to other columns.

    If you import into a new table, you'll have to add the other columns afterwards.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 17, 2016 1:44 PM
  • Hans, Thank you for your response. I see now I used wrong terminology, sorry. I have a spreadsheet that I need to import new data from to an existing Table where some fields have no data (inventory).

    So in the spreadsheet are the fields

    Asset | Barcode | Serial | Description

    In the db Table there are some fields example

    User | Location

    When new equipment comes in, we need to import the first four fields data as new records and then on the example Fields list the user as "New" and the Location as "Supply" for all records imported.

    Thank you


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, May 17, 2016 4:19 PM
  • I provided the solution in my previous reply: set the Default Value property of the User field to "New" and that of the Location field to "Supply".

    On the External Data tab of the ribbon, in the Import & Link group, click Excel.

    Browse to the Excel workbook that you want to import.

    Select the second option "Append a copy of the records to the table", and select the target table from the dropdown.

    Click OK and confirm.

    The Asset etc. fields will be filled with the data from the worksheet, and the User and Location fields will be populated with their default values.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KCDW Tuesday, May 17, 2016 8:34 PM
    Tuesday, May 17, 2016 8:17 PM
  • Sorry I missed that in your earlier response.

    Thank you for the information.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, May 17, 2016 8:35 PM