Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    MegaViperSnake is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    7

    Help:Importing multiple excel worksheets as mutliple in tables first row= title. First column= INDEX

    Please read this fully:

    I am currently trying to construct a plant database, with copied data over to excel. Because I could not make a spreadsheet and to keep the data organized, I needed to spread it out over multiple worksheets, each consisting of two columns where the top row are the tiles.

    Now each species name is actually unique so I decided to make the name the PRIMARY INDEX of database, because it is with the plant name its traits can be searched. However considering that a Primary Index cannot have duplicates, I had to split the list over multiple worksheets. This is exacly the reason why I could not make a spreadsheet.

    Example worksheets 1: representing value 1 of category
    Soortnaam Bomen
    Acer campestre 'Anny's Globe' Halfstam
    Acer campestre 'Nanum' Halfstam
    Acer griseum Halfstam
    Acer negundo 'Aureomarginatum' Halfstam
    Acer negundo 'Flamingo' Halfstam
    Acer negundo 'Variegatum' Halfstam

    Example worksheets 2: representing value 2 of category
    Soortnaam Bomen
    Acer campestre Klimbomen
    Carpinus betulus Klimbomen
    Castanea sativa Klimbomen


    So how do I make a macro for "Mass" importing a lot of worksheets with the first row as title and the first column as the primary index? I can do it one by one but there so much worksheets, that is kind of nuts and time-consuming to do it that way.



    Database1.zip

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Before worrying about how to import worksheets, design your table structure. You attached an empty database!

  3. #3
    MegaViperSnake is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    7
    Quote Originally Posted by aytee111 View Post
    Before worrying about how to import worksheets, design your table structure. You attached an empty database!
    Yes the database is empty because I before I will construct I need to know how to import these tables. I know kind of how to design my database, I have already setup groups and categories where the data should go after Imported it. I need to know how to import this. Sorry I added the file by mistake.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Show us your table structure, then we will know how to help you to import.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Here is some code which will import your data into one table. Copy and paste it into a new module (Create>Visual Basic>Insert Module). Then read the parts in green and make changes where it tells you to (such as the name of the file and table). Click on Debug>Compile and make sure that there are no compile errors. To run it, click anywhere inside the function and run it (f5, or one of the methods provided).

    Code:
    Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly  As Boolean
     Dim lngCount As Long
     Dim objExcel As Object, objWorkbook As Object
     Dim colWorksheets As Collection
     Dim strPathFile as String, strTable as String
     Dim strPassword As String
    
    ' Establish an EXCEL application object
     On Error Resume Next
     Set objExcel = GetObject(, "Excel.Application")
     If Err.Number <> 0 Then
           Set objExcel = CreateObject("Excel.Application")
           blnEXCEL = True
     End If
     Err.Clear
     On Error GoTo 0
    
    ' Change this next line to True if the first row in EXCEL worksheet
     ' has field names
     blnHasFieldNames = False
    
    ' Replace C:\Filename.xls with the actual path and filename
     strPathFile = "C:\Filename.xls"
    
    ' Replace tablename with the real name of the table into which 
     ' the data are to be imported
     strTable = "tablename"
    
    ' Replace passwordtext with the real password;
     ' if there is no password, replace it with vbNullString constant
     ' (e.g., strPassword = vbNullString)
     strPassword = "passwordtext"
    
     blnReadOnly = True ' open EXCEL file in read-only  mode
    
    ' Open the EXCEL file and read the worksheet names into a collection
     Set colWorksheets = New Collection
     Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
           strPassword)
     For lngCount = 1 To objWorkbook.Worksheets.Count
           colWorksheets.Add  objWorkbook.Worksheets(lngCount).Name
     Next lngCount
    
    ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
     objWorkbook.Close False
     Set objWorkbook = Nothing
     If blnEXCEL = True Then objExcel.Quit
     Set objExcel = Nothing
    
    ' Import the data from each worksheet into the table
     For lngCount = colWorksheets.Count To 1 Step -1
           DoCmd.TransferSpreadsheet acImport,  acSpreadsheetTypeExcel9, _
                 strTable,  strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
     Next lngCount
    
    ' Delete the collection
     Set colWorksheets = Nothing

  6. #6
    MegaViperSnake is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    7
    I thought I gave you guys what the structure you guys need the know. The EXCEL file is in the zip file. I need to explain, there are two columns, the third column are just notes to make things clear keep this in mind.


    Explanation First Column Second Column
    Titles Soortnaam Category
    What they serve in the database This column will be the primary index Value of the category, these value's which can be of the same category are spread over multiple worksheets.
    So this is the structure in excel. Keep in mind the different value's of the same category are spread over multiple worksheets, for me it is clear which worksheets stand for which category. Why I really want this database made is to create new tabelquerry's where I want specific data to be retrieved and that is either in two ways:

    1. Searching by which value's: For example, I want a plant that has gray autumn color and can be planted in a hardened surface. So I search two tables and if the plant name is both in those category's its get saved in a new queery table
    2. "Merging" tables of the same category together: So for example I have three worksheet


    Soortnaam Heigth categorie Merged cel with height categorie Merged cel with height categorie
    Plant name Value 1 of the height category. Value 2 of the height category
    if there is one
    Value 3 of the height category. If there is one
    Keep in mind I want to import them as seperate tables not as one big one. This one I will create with querry's.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It seems that there are different types of worksheets sharing the same information. These would obviously go onto the same table and from there be added to the tables. Without knowing what your Access table structure is, it is impossible for us to know how to import the worksheets.

    The code above is to import to one table; to import to separate tables makes the changes as specified here http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've looked at your spreadsheet for a while and since I do not understand what you want, it is confusing..... mostly because I don't understand Dutch (I hope it is Dutch - if not, I apologize).

    You have around 185 sheets (I might have lost count), but the first column seems to be plant names??
    So it looks like, to me, there would be at least 4 tables. I am not sure what to do with the sheets with names like "Breedte klasse 5-10".

    Sheet "Alle Plantensoorten" has 1590 rows of names - not sure if there are more unique names in other sheets.


    Sheet "BDVCHT-DR" has "BDVCHT-DR" in row 1 and "Droog" in the rest (1088) of the rows.
    Sheet "BDVCHT-NAT" has "Bodemvochtigheid" in row 1 and "Nat" in the rest (311) of the rows.
    Sheet "BDVCHT-VCHT" has "BDVCHT-VCHT" in row 1 and "Vochtig" in the rest (1513) of the rows.
    Sheet "BDVCHT-ZDR" has "BDVCHT-ZDR" in row 1 and "Zeer droog" in the rest (1513) of the rows.
    Sheet "BDVCHT-ZRN" has "BDVCHT-ZDR" in row 1 and "Zeer nat" in the rest (22) of the rows.

    What is the difference in the sheets? Maybe you would translate the names?



    What is the difference in the following names?
    ------------------------------------------------
    Acer negundo '<-- this is the main name?
    Acer negundo 'Aureomarginatum' ' is 'Aureomarginatum' one variation of Acer negundo?
    Acer negundo 'Flamingo'
    Acer negundo 'Variegatum'

  9. #9
    MegaViperSnake is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    7

    Further explanations

    Quote Originally Posted by ssanfu View Post
    I've looked at your spreadsheet for a while and since I do not understand what you want, it is confusing..... mostly because I don't understand Dutch (I hope it is Dutch - if not, I apologize).

    You have around 185 sheets (I might have lost count), but the first column seems to be plant names??
    So it looks like, to me, there would be at least 4 tables. I am not sure what to do with the sheets with names like "Breedte klasse 5-10".
    "BDVCHT"


    Sheet "Alle Plantensoorten" has 1590 rows of names - not sure if there are more unique names in other sheets.



    Sheet "BDVCHT-DR" has "BDVCHT-DR" in row 1 and "Droog" in the rest (1088) of the rows.
    Sheet "BDVCHT-NAT" has "Bodemvochtigheid" in row 1 and "Nat" in the rest (311) of the rows.
    Sheet "BDVCHT-VCHT" has "BDVCHT-VCHT" in row 1 and "Vochtig" in the rest (1513) of the rows.
    Sheet "BDVCHT-ZDR" has "BDVCHT-ZDR" in row 1 and "Zeer droog" in the rest (1513) of the rows.
    Sheet "BDVCHT-ZRN" has "BDVCHT-ZDR" in row 1 and "Zeer nat" in the rest (22) of the rows.

    What is the difference in the sheets? Maybe you would translate the names?


    What is the difference in the following names?
    ------------------------------------------------
    Acer negundo '<-- this is the main name?
    Acer negundo 'Aureomarginatum' ' is 'Aureomarginatum' one variation of Acer negundo?
    Acer negundo 'Flamingo'
    Acer negundo 'Variegatum'



    Quote Originally Posted by ssanfu View Post
    I've looked at your spreadsheet for a while and since I do not understand what you want, it is confusing..... mostly because I don't understand Dutch (I hope it is Dutch - if not, I apologize).
    What is the difference in the following names?
    ------------------------------------------------
    Acer negundo '<-- this is the main name?
    Acer negundo 'Aureomarginatum' ' is 'Aureomarginatum' one variation of Acer negundo?
    Acer negundo 'Flamingo'
    Acer negundo 'Variegatum

    It is indeed in dutch, with the plant names being in Latin/scientific name with the writing rule "Genus name species name 'varriation'. So you were right to assume that Acer negundo 'Aureomarginatum' is a variation of Acer negundo, it has specific attributes that were cultivated into the Acer negundo species. Creating a "culture" variation of the same species.
    You are also correct that the first column is the is the species names.

    Quote Originally Posted by ssanfu View Post
    Sheet "Alle Plantensoorten" has 1590 rows of names - not sure if there are more unique names in other sheets.

    The "Alle plantsoorten" worksheet literally translated "All plant species" is the complete unfiltered list. So, in other words, this the list that shows how many total unique names there actually are.


    Quote Originally Posted by ssanfu View Post
    You have around 185 sheets (I might have lost count), but the first column seems to be plant names??
    So it looks like, to me, there would be at least 4 tables. I am not sure what to do with the sheets with names like "Breedte klasse 5-10".

    Sheet "BDVCHT-DR" has "BDVCHT-DR" in row 1 and "Droog" in the rest (1088) of the rows.
    Sheet "BDVCHT-NAT" has "Bodemvochtigheid" in row 1 and "Nat" in the rest (311) of the rows.
    Sheet "BDVCHT-VCHT" has "BDVCHT-VCHT" in row 1 and "Vochtig" in the rest (1513) of the rows.
    Sheet "BDVCHT-ZDR" has "BDVCHT-ZDR" in row 1 and "Zeer droog" in the rest (1513) of the rows.
    Sheet "BDVCHT-ZRN" has "BDVCHT-ZDR" in row 1 and "Zeer nat" in the rest (22) of the rows.

    What is the difference in the sheets? Maybe you would translate the names?
    Okay this concept of a database is based on a website where I get my information from. It is kind of an online search engine where you can search for plants online by certrain traits filters. But we wanted an offline version to create our own lists based on querry's, that is why we need to can acces
    This is the dutch version: https://www.ebben.nl/nl/treeebb/
    And this is the English version: https://www.ebben.nl/en/treeebb/

    Like I said before because I could not make this into a spreadsheets because I want the plant names to be the Primary Index so doubles are out of the question.
    I had to organize the tables so in a way that I knew wich trait category and which traits value the category has. So the structure "normally" would be the following:

    "Sub category" "-" "value trait"
    There are exceptions to this rule, but I believe I do not need to explain these for you. Also there is a limit to how much text I could have in the worksheet title. So I had to improvise here and there.

    To make sure my data would be easily processed I made it so that first row would always contain the titles.

    Though I kind of made some mistakes by naming the second the column here and there since I was completely copying over the first row from a previous worksheet. So I understand that you are confused here. Sometimes the second column would not have a title at all.

    Not all plants have the same traits, so of course, some worksheets would have more names and others less. This was also a way for me to also tracks to make sure that the "right" data was copied over. Since I had complications with the clipboard before, where I realized to later after even copying over new data it was still pasting the "old data" on the clipboard. You can ignore this last part it is not relevant here.

    In this example:
    So "BDVCHT" stands for the search filter "Bodemvochtigheid" translated "Soil measure level", the "-" stands for the value so for "ZRD" it stands for "Zeer droog" translated "Very dry".

    Also because each species has a certain min and max height and width. I had to categorize these in certain ranges. For width, I took categories with a difference of 5 meters. For Height I used a special category Belgium uses called the "Drie categorieen systeem" translated "Three category system"
    Cat 1: >12 meters
    Cat 2: 6-12 meters
    Cat 3: < 6 meters.

    I hope this clears things up for you.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    For me, I still want to see your table structure. The process would be a loop which would:
    1) import worksheet into temp table
    2) depending on the data on that worksheet, the data would be added to the correct tables/inserted into the correct fields within those tables

    If the first worksheet is a complete list then that would be one table (maybe only one table is required). The prefix of the worksheet would determine which field is being populated - sub-category, value trait, etc.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by aytee111 View Post
    For me, I still want to see your table structure.
    I think that is what the OP is looking for - how to create the table structures.


    @MegaViperSnake,
    I attached a sampling of the names from your spreadsheet from tab "Alle Plantensoorten". How would you split the names?
    Would you only use/want the Genus, Species and Variation?



    So there are (at least) 3 tables. Then you have the main table. (that's 4)
    On the TreeEbb web site, I counted 28 attributes (combo boxes), so there would be 28 more tables (now 32 tables). Plus there would/could be junction tables....

    About the 28 attribute tables.... these are lookup tables, so all of the attributes could be in one table, with a field to specify which attribute the option belonged to.
    Attached Files Attached Files

  12. #12
    MegaViperSnake is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    7

    Further explanations 2

    Quote Originally Posted by ssanfu View Post
    I think that is what the OP is looking for - how to create the table structures.


    @MegaViperSnake,
    I attached a sampling of the names from your spreadsheet from tab "Alle Plantensoorten". How would you split the names?
    Would you only use/want the Genus, Species and Variation?



    So there are (at least) 3 tables. Then you have the main table. (that's 4)
    On the TreeEbb web site, I counted 28 attributes (combo boxes), so there would be 28 more tables (now 32 tables). Plus there would/could be junction tables....

    About the 28 attribute tables.... these are lookup tables, so all of the attributes could be in one table, with a field to specify which attribute the option belonged to.

    Yes you are right that I can create this in one table but I am still unsure if I can search for certain trait attributes I want. That is why I created these lookup tables to create querry's. If I still can search by trait attribute even it is all in one table by using drop down menu's I would go for that solution.

    Also I should note that not all attributes so far were not being used from the site, only those that were relevant from my work location were used. I have added an attachement that shows how many worksheets, the structure of the website, what I really want and etc.


    As for the names I want to keep them full but I might want to split them later.

    The reason why is that I can create a link to another separate database I have on hand that has the taxonomical tree, which is still in progress. That last part is optional. I will ask this is in a different topic.
    Attached Files Attached Files

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, I've been looking at the Excel spreadsheets for a while.

    What I remember from school:
    This is a dandelion, it is a weed, kill it.
    This is grass (not pot!), mow it.
    This is a tree, leave it alone.


    Now you know my level of expertise about Taxonomy.
    In Post #11, I attached a spreadsheet and asked how you would split the plant names, but since you didn't show me the correct splits of the plant names, I did it myself. (which may not be correct)


    Here is the problem I am having: all of your examples are designed in a spreadsheet, AS a spreadsheet.
    Access and Excel are two extremely different object models. How you set up a spreadsheet is very different than designing a database.

    BTW, thanks for the links to the 'tree' web sites. They allowed me to make a "cheat sheet" - see the tab "X-Ref".


    The dB is my attempt to create a dB. Next would be junction tables for the categories that can have multiple entries.
    For example 'Bomen' - in your spreadsheets "Abies koreana" can be 1 of 3 trees: Beveerd, Hoogstam or Naaldhoutgewassen
    There are 9 of these multiple option categories.

    Take a look and see if any of this helps you........
    Attached Files Attached Files

  14. #14
    MegaViperSnake is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    7
    [
    Quote Originally Posted by ssanfu View Post
    OK, I've been looking at the Excel spreadsheets for a while.

    What I remember from school:
    This is a dandelion, it is a weed, kill it.
    This is grass (not pot!), mow it.
    This is a tree, leave it alone.


    Now you know my level of expertise about Taxonomy.
    In Post #11, I attached a spreadsheet and asked how you would split the plant names, but since you didn't show me the correct splits of the plant names, I did it myself. (which may not be correct)


    Here is the problem I am having: all of your examples are designed in a spreadsheet, AS a spreadsheet.
    Access and Excel are two extremely different object models. How you set up a spreadsheet is very different than designing a database.

    BTW, thanks for the links to the 'tree' web sites. They allowed me to make a "cheat sheet" - see the tab "X-Ref".


    The dB is my attempt to create a dB. Next would be junction tables for the categories that can have multiple entries.
    For example 'Bomen' - in your spreadsheets "Abies koreana" can be 1 of 3 trees: Beveerd, Hoogstam or Naaldhoutgewassen
    There are 9 of these multiple option categories.

    Take a look and see if any of this helps you........

    Ok this is good so far. This actually close to what I really want but besides plant forms, I want to create a list of species that is based on multiple Traits I select. Like you do on the website.

    So how do I do this further then for the import-export, what Macro must I program and how?

    Also, I have re-updated my excel table also added a taxonomy table and an excel table where I merge all worksheets that were the same category. Though this delivers a lot of doubles so they need to be filtered out, but I do not know how without losing the extra info. Please read them.


    New EXCEL FILES PLEASE READ.zip

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How is this name divided?
    Acer campestre 'Anny's Globe'

    Geslacht = Acer
    Species = campestre
    Variation = 'Anny's Globe'
    Correct??


    How about this name? How/where does the 'x' fit in???
    Acer x freemanii 'Armstrong'

    Geslacht = Acer

    Species = freemanii
    Variation = 'Armstrong'

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing multiple worksheets and tables in the same specific way.
    By ThunderSpark in forum Import/Export Data
    Replies: 1
    Last Post: 04-04-2017, 05:57 AM
  2. Importing Excel Workbook with multiple Worksheets
    By MTSPEER in forum Programming
    Replies: 4
    Last Post: 04-21-2015, 01:50 PM
  3. Replies: 20
    Last Post: 02-02-2015, 03:02 AM
  4. Replies: 4
    Last Post: 10-03-2014, 12:27 PM
  5. Importing Excel datasheets into multiple tables
    By FishMT in forum Import/Export Data
    Replies: 3
    Last Post: 02-08-2011, 07:44 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