This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Import a value list and a sublist from Excel

Hi, My name is Sam Pieter and I want to share with you my solution to import certain data from an Excel file.
The data from Excel will be placed in a value list and a sublist.

As an example, I have created an Excel file with the planets of our solar system and their moons.
The goal is to create a value list with all the planets, and a sublist with the corresponding moons.
In M-Files, this will result in a menu where you can choose a planet, and another (sub) menu that will show you the moons that belong to the chosen planet.

Prepare your Excel data:
In one sheet, make a list of all planets and give each planet a unique, numbered ID.
Select the ID's and planets.
Go to 'Formulas' -> 'Defined Names' -> 'Define Name' and name the collection: 'planet'.
Yes: Pluto is a planet  ;)


In another sheet, make a list of the moons, give each moon a unique, numbered ID and also the ID number of the planet it's orbiting.
(This list of moons is not complete: I just used a few moons in order to populate this example list)
Select the ID's and planets.
Go to 'Formulas' -> 'Defined Names' -> 'Define Name' and name the collection: 'moon'.


Save and close Excel.
In Windows, make an ODBC connection to the Excel file:

With 'Select Workbook' you can search for your planets and moons excel file.

In M-Files, create a new value list, name it 'Planeet' and make a sublist called 'Maan':



In the 'Planeet' value list, make a 'Connection to External Database'. See your M-Files manual for instructions. Do not forget to enter 'Excel 8.0;' into the 'Extended Properties'
In the 'SELECT Statement' type: 'SELECT * FROM planet and hit the 'Refresh Columns' button.
Now choose your 'Target Property': The Object ID is column 'ID-P' from your Excel file. and the 'Name' is column 'Planeet' from your Excel file.


When you check the content of your 'Planeet' value list, you see:

The planets are in!

In the 'Maan' value list, you also make a External Database Connection.
You 'SELECT * FROM moon', 'Refresh Columns' and the target properties are:
'Owner' is the ID_P column from your Excel file;
'Object ID' is the ID_M column from your Excel file, and
'Name' is the Maan column from your Excel file.


When you check the content of your 'Moon' value list, you see:


And finally, when you take a peek a the content of your 'Planeet' value list:

Voila

I hope that other M-Files users find this helpful and I'm looking forward to your feedback.
  • Thanks a Lot! This is a M-Files "feature" I was looking for. It will be very useful in future implementations.

  • Hi, My name is Sam Pieter and I want to share with you my solution to import certain data from an Excel file.
    The data from Excel will be placed in a value list and a sublist.

    As an example, I have created an Excel file with the planets of our [color=black]solar kits[/color] and their moons.
    The goal is to create a value list with all the planets, and a sublist with the corresponding moons.
    In M-Files, this will result in a menu where you can choose a planet, and another (sub) menu that will show you the moons that belong to the chosen planet.

    Prepare your Excel data:
    In one sheet, make a list of all planets and give each planet a unique, numbered ID.
    Select the ID's and planets.
    Go to 'Formulas' -> 'Defined Names' -> 'Define Name' and name the collection: 'planet'.
    Yes: Pluto is a planet ;)


    In another sheet, make a list of the moons, give each moon a unique, numbered ID and also the ID number of the planet it's orbiting.
    (This list of moons is not complete: I just used a few moons in order to populate this example list)
    Select the ID's and planets.
    Go to 'Formulas' -> 'Defined Names' -> 'Define Name' and name the collection: 'moon'.


    Save and close Excel.
    In Windows, make an ODBC connection to the Excel file:

    With 'Select Workbook' you can search for your planets and moons excel file.

    In M-Files, create a new value list, name it 'Planeet' and make a sublist called 'Maan':



    In the 'Planeet' value list, make a 'Connection to External Database'. See your M-Files manual for instructions. Do not forget to enter 'Excel 8.0;' into the 'Extended Properties'
    In the 'SELECT Statement' type: 'SELECT * FROM planet and hit the 'Refresh Columns' button.
    Now choose your 'Target Property': The Object ID is column 'ID-P' from your Excel file. and the 'Name' is column 'Planeet' from your Excel file.


    When you check the content of your 'Planeet' value list, you see:

    The planets are in!

    In the 'Maan' value list, you also make a External Database Connection.
    You 'SELECT * FROM moon', 'Refresh Columns' and the target properties are:
    'Owner' is the ID_P column from your Excel file;
    'Object ID' is the ID_M column from your Excel file, and
    'Name' is the Maan column from your Excel file.


    When you check the content of your 'Moon' value list, you see:


    And finally, when you take a peek a the content of your 'Planeet' value list:

    Voila

    I hope that other M-Files users find this helpful and I'm looking forward to your feedback.



    Very nice tutorial.. I have been searching for similar information for a while and finally got it.
  • Thankyou!!
    Just want to report for others that I have this working as per your example.
    I am using M-Files version 10 (up to date as of 15th April 2014). It runs on a 2008R2 server with the Firebird Database and I installed the 64bit ODBC connectors on the server as per this post - https://community.m-files.com/index.php?topic=4362.0.

    The spreadsheet is stored on a local drive of the server.

  • I tried to follow this but I don't have the Excel DSN in the server. I installed it according the instructions here https://community.m-files.com/index.php?topic=4362.0.
    I have Windows 7 Professional and server is Windows Server 2008.

    When I try configure the User DSN, it gives me error: The setup routines for the Microsoft Excel Driver ODBC driver could not be found. Please reinstall the driver. Errors found: The specified DSN contains an architecture mismatch between the Driver and Application.
  • Are you sure that you have installed the correct 32/64 bit version? The link has both versions, you need to select the relevant one for your environment.
    I haven't used this setup the last few years so there may have been changes that I'm not aware of. In the meantime we have got the M-Files Importer which is a much more versatile tool for importing from different sources including Excel.

  • In the meantime we have got the M-Files Importer which is a much more versatile tool for importing from different sources including Excel.


    Hi Bright-Ideas (Karl?), I'm interested in this tool. Where can we download M-Files Importer?
  • If you are a M-Files partner, you can get it through your Account Manager or download it from the partner portal.
    If you are a customer, you need to buy a license from your M-Files reseller.
    Karl