Results 1 to 11 of 11
  1. #1
    stickman1019 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    5

    Issues with Data Import from Excel

    Hi Guys,

    I have an Access database which I have inherited from a previous colleague. My issue is with transferring data from an Excel Spreadsheet into the access database (Local Table).

    Limitations / issues I have are as follows

    1) The Excel spreadsheet (Source Data) is read-only and cannot be modified by me.
    2) The top two rows in the Excel spreadsheet contains merged cells, thus column headers can be contained in either rows 1 or 2
    3) Owner of the spreadsheet constantly modifies the structure i.e. deleting and adding columns

    I have tried a few different options

    1) Creating a linked table and then running an update Query to update a Local Table (Structure of data i.e. columns changing presents issues.)
    2) My colleague setup a VBA option which utilizes the DoCmd.TransferSpreadsheet option which unfortunately suffers from the same issues as the above method.

    I am thinking that a solution may be to match the local Headers in my local table to the column headers (Rows 1 & 2) in the Source spreadsheet, however I am unsure how to implement this.

    I am open to any suggestions / solutions that you guys may have.

    Any help is greatly appreaciated
    Last edited by stickman1019; 12-21-2016 at 11:18 PM.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am thinking that a solution may be to match the local Headers in my local table to the column headers (Rows A&B) in the Source spreadsheet
    I for one, have no idea on how this could be possible if the names or counts of the columns are subject to change. There would have to be some sort of consistency, otherwise I can only see a linked table with a manual reconstruction of any queries or transfers that are needed. What assurance do you have that one of these named columns will not be deleted? Can you convince the workbook owner to define ranges that will not be altered?
    rows A or B
    I think you mean rows 1 or 2, or columns A or B
    Last edited by Micron; 12-21-2016 at 11:09 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    stickman1019 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    5
    Sorry typo with the rows original post updated.

    Indeed the major stumbling block is data changing in the source spreadsheet

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What do you do with the data once you have imported it? It is easy to import without headings but the key is where the data is going once it is in a local table.

  5. #5
    stickman1019 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    5
    Currently the data is imported into a table as Fields F1, F2, F3 and so on. This then is displayed in a Form for viewing by users a separate sub-form then allows for comments by users. Note these comments stay in my DB and are not required to be transferred back to the source spreadsheet. (That is the purpose of the DB to allow users on my end to comment on a spreadsheet which we have no control over)

    Should also mention that DB is split front and back ends with tables contained in the back end.


    When importing without the headings the issue as I see it is when the source spreadsheet is modified i.e. column deleted then the data ends up in the wrong location in my form.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think your options are
    - forget the subform and display the data as a form datasheet based on the table, looking at non descriptive field names F1, F2... because the table you'd be looking at fluctuates wildly. Comments would go in your current form as a modal popup form. If you need to keep previous tables, then some way of telling the form which table to open. Too many tables in a period means dividing your data into db's.
    -forget Access and just work off of the workbook you're given
    - could you make use of the data easier if it was a csv file?
    - take the workbook submitter out to the woodshed, hang him/her by the eyelids and beat them until they blink...

    Edit:
    forgot to mention that you could probably hide all the fields that have no data (because the form was built for 20 and only 15 show up) by hiding or disabling them if they're null or contain the #Name or some other error. Just don't build the form for less than what you might get. Hmm, last option I gave is looking better all the time.
    Last edited by Micron; 12-22-2016 at 06:16 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    - take the workbook submitter out to the woodshed, hang him/her by the eyelids and beat them until they blink...



    Another option is to try and write a lot of VBA code to analyze the data and try and determine what field the data would fit in.
    I don't import Excel spreadsheets any more; I save them as CSV files, then import. Much, much easier/simpler to deal with a CSV file than a spreadsheet.

  8. #8
    stickman1019 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    5
    Quote Originally Posted by ssanfu View Post



    - take the workbook submitter out to the woodshed, hang him/her by the eyelids and beat them until they blink...

    Personally this option is looking more and more likely :-).

    I would not know where to start on on taking this data via high level VBA my skill-set would not be strong enough.

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Subform which displays the imported data in datasheet view, i.e. with the headings displayed. On the main form have comboboxes labeled F1, F2,..... The user will then enter the correct name for each field. Your append query will then take the names from the main form and will know which "F" field to put into which table field.

    To make this right you will need lots of VBA, but in its most simple view you can get away with static controls.

  10. #10
    stickman1019 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2016
    Posts
    5
    Thanks for the reply guys much appreciated.


    In the end the spreadsheet owner has agreed to modify the column so that the headers are contained in Row 1.


    Aytee111 I think I might have been able to implement what you were suggesting but thankfully the owner have saved me a lot f work.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the end the spreadsheet owner has agreed to modify the column so that the headers are contained in Row 1.
    You are very fortunate...

    Good luck with your project.....

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

Similar Threads

  1. Replies: 2
    Last Post: 12-19-2016, 07:30 PM
  2. Replies: 2
    Last Post: 06-25-2015, 03:56 PM
  3. Import from Excel to Access 2010 Issues
    By bcofie in forum Import/Export Data
    Replies: 7
    Last Post: 01-20-2014, 10:12 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: 2
    Last Post: 03-28-2013, 06:21 PM

Tags for this Thread

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