Results 1 to 4 of 4
  1. #1
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84

    How to import excel to access

    In an Excel spreadsheet i have case fields and contact fields - the same fields are in my db but are in two separate tables which are related in a query.



    How do i import this data into Access so that cases are related to contacts?

    Thanks,

    John

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    download the excel file to the same place everytime, i.e.: c:\temp\File2Import.xls
    overwriting it each time.
    link this as an external table. (done only once)
    build an append query to import the data from the xl file.
    put this query in a macro
    you may need to run a 'fix data' query before or after the import.


    do this for all sheets in the xl file.

    then the steps are:
    1. save the file,
    2. run the macro.
    done.

  3. #3
    jmuirman1 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    84
    I'm not sure this is within my skill set. Here are some questions I have. Thanks.

    INSTRUCTION MY QUESTIONS THOUGHTS?
    download the excel file to the same place everytime, i.e.: c:\temp\File2Import.xls overwriting it each time. I only have one Excel sheet. I’m not sure why multiple downloads are necessary. Am I to split the Excel Sheet into two – one for cases table and one for contacts table?
    link this as an external table. (done only once) Do I import as a new table? Also, my db is split. Assuming I import to front end??
    build an append query to import the data from the xl file. Not sure how to do this.Can you point me in the right direction?
    put this query in a macro Need help here too. what is the macro for and how do i build?
    you may need to run a 'fix data' query before or after the import. Is this to make sure the fields/cells are the same data type with same column headings?
    do this for all sheets in the xl file. Only one sheet
    then the steps are:
    1. save the file,
    2. run the macro.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You really have not provided enough information to provide a focused response to your initial or subsequent post. But by the sound of it you will need 2 or more append queries. Would need answers to all of the following


    1. you say case fields - does that mean there is more than one column in the excel file with cases?
    2. ditto contacts?
    3. does your excel data have a primary key column?
    4. if so, is that something that needs to be in access?
    5. which is the 'parent' table in access? cases or contacts?
    6. what is the relationship between cases and contacts? one to many (in which case which is the one?) or many to many? i.e. one case can have many contacts and one contact can have many cases.
    7. do cases and/or contacts already exist in access which are also in the excel file?
    8. does your access tables contain the required primary and foreign key fields
    9. from your second post this is a one time exercise, never to be repeated

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  4. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  5. Replies: 4
    Last Post: 12-17-2012, 01:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums