Share via

I want to import an excel file into access db on a monthly basis

Anonymous
2012-12-07T22:59:19+00:00

Hello All,

I am totally new to really working with office products in the way that I am now.  I have an excel report I receive on a monthly basis which is basically used to show our teams 'Problem Report' statistics.  As a test I have modified 2 monthly files by cleaning them up and removing blank field and giving a 'High Date(12/31/2400)' for the completed dt field.  I was then able to import that excel sheet into a single table which I believe is all that is really needed for this particular purpose.  The files are typically only 150 or so rows and 9 columns(fields).  I am not sure what I am doing wrong. I used the FOlder ID as the key because it is unique. However one folder ID on lets say an oct file that is in an OPEN status when reported to us may show up in Nov file and have the closed dt field populated.

I want to create a Acccess DB whereby I can import or create a table and then subsequently import the next months data and write new when new and update changed fields when folder id already exists.

Example of file.

Folder ID Receipt DT Completed DT Team Name Assigned Analyst System BSI Problem Type Reported By
44710880 9/4/2012 10/22/2012 MS_PROBLEM_REPORT Steve Foundation Finance Training Joe Shmoe
44732067 9/6/2012 10/12/2012 MS_PROBLEM_REPORT John Foundation Billlable Rates Data Issue Joe Shmoe
44799621 9/14/2012 10/12/2012 MS_PROBLEM_REPORT Glori Foundation Billlable Rates Program Error Joe Shmoe
44843446 9/20/2012 12/31/2400 MS_PROBLEM_REPORT David Foundation Finance Data Issue Joe Shmoe
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

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-12-12T18:34:17+00:00

    1- I said to run an append query first.

    2- Open the update query in design view, click on VIEW, select SQL View, highlight all, copy, and paste in a post.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-12-12T17:32:13+00:00

    Hello, Thank you for the help. I have imported the first month(for my test this is OCT) excel spreadsheet into access as a table. I have then linked the following months excel spreadsheet.

    I then created an update query and attempted to run it, only to receive the following error

    'Cannot update ('expression'); Field not updateable'.  I have a snapshot of the querry which would let you see the table name, the excel linked file and how the query is setup. However, I do not see a way to attach it to this forum.  It is a very simple spreadsheet, 10 columns and an average of about 150 rows per month.  In this test DB i am working on, for OCT info, the number of 'Problem Reports'(rows) that are 'OPEN' numbers 16 and so the following months information should include those 16 rows with updated info.  6 of the 10 fields would update and then there would be any number of new records to add to the DB.

    The DB is called 'WMS_PR_Tracker.accdb',   There is only one table here,called 'Problem Report Tracking' created from the first spreadsheet which consists of 10 columns and 159 rows.  16 of which are in an OPEN state and would need updating the following month by linking the next spreadsheet.

    The linked excel file is called 'Nov2012_WMS_PR'.  The fields in the table are as follows wiht the first field being unique and used as the key. Folder ID(Key),Receipt DT,Completed DT,Team Name,Assigned Analyst,System,BSI,Problem Type,Reported By,Days Aged. The ones in bold would be fields that get updated. 

    I am thinking that I did not setup my query properly?  I just don't know. So new to this.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2012-12-10T22:52:47+00:00

    Just create an update query that updates everything but the key field.

    For me to help more I need the names of the tables.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2012-12-10T19:20:22+00:00

    Thank You for the advise.  I have done as you suggested. Definitely works better than what I was trying to do which was to import each file.

    Could I please ask you to help me write the query update?

    I have an example of what the original table entry contains.

    EXISTING RECORD IN DB TABLE.

    <br>45138652 <br>10/29/2012 <br>12/31/2400 BS_PROBLEM_REPORT OPEN Foundation OPEN OPEN FAITH <br>1000

    The linked file contains the updated information

    NEW INFORMATION

    <br>45138652 <br>10/29/2012 <br>11/5/2012 BS_PROBLEM_REPORT Glori Foundation Finance Training FAITH <br>5

    I want an update that adds the new records to the db table and also updates existing record with any different info found on linked file(override basically)

    Was this answer helpful?

    0 comments
  5. Anonymous
    2012-12-08T01:14:56+00:00

    Always backup you database before proceeding.

    I assume that your Folder ID is unique, so you need to make that field a Primary Key field.  To do so you open the table in design view and click on the Folder ID field, click on the icon that looks like a key.

    Then on a monthly basis only new records will be added to the table and no records will have all blank fields.

    Link the new excel file rather than import.

    Use 2 queries, an APPEND query to add any new records and an UPDATE query to update that fields that change.

    Was this answer helpful?

    0 comments