The M-Files Community will be updated on Tuesday, April 2, 2024 at 10:00 AM EST / 2:00 PM GMT and the update is expected to last for several hours. The site will be unavailable during this time.

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.
Parents

  • 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?
Reply

  • 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?
Children
No Data