Results 1 to 2 of 2
  1. #1
    ThunderSpark is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    32

    Question Importing a no-spreadsheet excel table in excel where each column is a "filter": how to do this?

    Hello, I am currently trying to construct a offline plant database based on the online catalog of a website. I had some troubles with importing from HTML and in both MC ACCESS and EXCELL (both 2010), because:


    1. The plant names are stored in on a server and therefore are not saved to the HTML file
    2. The website can only be opened in Firefox and there are troubles

    Website link is here if you are interested: https://www.ebben.nl/nl/treeebb/#?37...=1&37%5B2%5D=3

    I am currently trying in Excel to copy the data over in a very organized way.
    However I am doubting I can just simply import the data the way I have organized it now. Since from what I understand I need to have my data in a "spread sheet" and currently it is not actually a "spread sheet". Let me explain:

    Currently to make it for myself easy, I have organized my information so that each worksheet is the main filter of the site, and the sub-filter of the site stands for a column. so for example: Worksheet "kroonvorm Filter", which contains the species (which are trees) plants that have a specific Crown shape type. The Species name who have those crown filter types are pasted under each column. Not every species has the same amount of plants as I kind of illustrate this as below (text is in dutch):

    Zuil Ovaal Eirond Vaas Hart Piramidaal
    Acer campestre 'Green Column' Acer cappadocicum subsp. lobelii Acer campestre Acer capillipes Quercus petraea 'Mespilifolia' Abies concolor
    Acer x freemanii 'Armstrong' Acer davidii subsp. grosseri Acer campestre 'Commodore' Acer carpinifolium Tilia amurensis Abies koreana
    Acer x freemanii 'Armstrong Two' Acer x freemanii Acer campestre 'Elsrijk' Acer davidii 'George Forrest' Tilia henryana Abies nordmanniana
    Acer platanoides 'Columnare' Acer 'Norwegian Sunset' Acer campestre 'Geessink' Acer x freemanii 'Elegant' Tilia henryana var. subglabra Abies procera
    Acer platanoides 'Ebben's Column' Acer platanoides 'Cleveland' Acer campestre 'Huibers Elegant' Acer palmatum Acer x freemanii 'Celzam'
    Acer platanoides 'Olmsted' Acer platanoides 'Fairview' Acer campestre 'Queen Elizabeth' Acer palmatum 'Atropurpureum' Acer platanoides 'Eurostar'

    I have a list of all the plants which is actually unfiltered. So I know how many plants I have, in the future this list will be updated with new plants.

    However I am unsure if Access Can handle this, I technically can create a primary key in the unfiltered list on the plant species names, so that makes it easier to create search querries based on the species name. But since I am working with such a structure as set above, I cannot kind of set a primary key in those other tables.


    How do I handle this? Do I need to split up the worksheets into more worksheets (causing my work to be for nothing) or do I need to "transpose" the table? I am kind of stuck here so help is appreciated.

    Edit: I uploaded a zip file with my excel table so you guys can take a look.
    Attached Files Attached Files
    Last edited by ThunderSpark; 04-04-2017 at 12:43 AM.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If each plant can only fit into one category this is what I'd do:

    Create the following tables
    Code:
    tblCrownType
    CT_ID  CT_Description
    1      Ovaal
    2      Zuil
    3      Eirond
    
    tblTree
    T_ID  T_Name  CT_ID
    Then
    Cut and paste your list 1 at a time into tblTree in the T_Name column. Then run an update query against all the records that have a null (blank) CT_ID field and update them with the CT_ID from your tblCrownType table. This is going to be easier than trying to build the structure into your spreadsheet before importing it.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-20-2016, 05:34 PM
  2. Replies: 1
    Last Post: 07-10-2015, 06:33 AM
  3. Replies: 3
    Last Post: 01-13-2015, 05:21 PM
  4. Importing Excel spreadsheet and appending it to an existing table
    By Access_Novice in forum Database Design
    Replies: 3
    Last Post: 08-21-2014, 07:01 AM
  5. Excel format "Text to column" using ACCESS VBA
    By msaccessdev in forum Programming
    Replies: 3
    Last Post: 06-09-2014, 08:41 AM

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