Frequently Asked Questions
Frequently Asked Questions
Using Excel as a data source for external object connections (.xls and .xlsx)
Posted by Will Lyles (M-Files) on 02 July 2018 08:09 PM

Please note that using the method in this article is not officially supported by Microsoft Access ODBC driver, so you will be doing it at your own risk. We recommend using an intermediate SQL table instead.

Read this article before attempting to use Excel as a data source: Access ODBC driver not supported - Solution available

In this article we will be using an Excel file (.xls or .xlsx) to import a list of employees into M-Files.
To do this we have created an object type in M-Files called Employees.
Below we will be setting up the Excel file and the external object connection for the Employee object type.

1) Create a table in Excel that will be holding the data.
- Open Excel and create a table to be used for importing/creating objects in M-Files.
- For example, create a column for the object ID's (e.g. ID) and another column for the Employee names (e.g. Names, First Name, Last Name, etc.).
- Once the columns are defined, enter the appropriate data.

2) Create a 'Name' for the table.
- Select Formulas > Name Manger > New in Excel.
- Enter a name for the table (e.g. EmployeeTable).
- Set the Name's reference ('Refers to:') to point to the table (i.e. select the entire table, including the column headers).
- Click OK.
- You can confirm the creation of the Name by selecting the drop-down in the top left of Excel. The Name you created will apear in this drop down and selecting it should now highlight the table you created previously.
- Save and close the Excel file.

3) Create the ODBC Data Source.
- Open ODBC Data Source Administrator (64-bit).
- Select the 'System DSN' tab > Add.
- Select the 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)' driver > Finish.
- Enter a name for the ODBC source (e.g. Employees), select the appropriate version of Excel from the drop-down, then select the appropriate Workbook > OK.

4) Create the Employee object and configure the connection string.
- Open M-Files Admin and create a new object (e.g. Employee). Add the properties to the Employee class that you wish to map to the Excel table columns.
*Be sure to disable the setting 'Users can create objects of this type' in the 'General' tab.
- Open the new Employee object properties and select the 'Connections to External Database' tab.
- Check 'Use a connection to an external database...' option and click 'Define' for the OLE DB connection string.
- Select the 'Microsoft OLE DB Provider for ODBC Drivers' option in the 'Provider' tab > Next.
- In the 'Connection' tab, select the data source we previously created from the 'Use data source name' drop-down (e.g.Employees).
- Enter any credential information as well if necessary (i.e. accessing a network drive).
- Select the appropriate initial catalog (e.g. the file path to the Excel file).
- Test Connection to make sure all of the above steps were completed successfully > OK.

5) Configure the SELECT statement and map source columns to the target properties.
- To select the entire table created in step 1 enter 'SELECT * FROM <table name>'. Refer to the Name created in Excel in step 2 for this (e.g. SELECT * FROM EmployeeTable).
- Click 'Refresh Columns'. This will execute the query and display the columns from the Excel table in the Columns section of the connection.
- Map the 'Source Columns' from the Excel table to the correct properties in M-Files for the Employee class ('Target Property').
For example, ID = Object ID, NAME = Name or Title, etc..
- Once the properties are mapped, click Apply > OK. You can now log into the desktop client and search for the Employee objects to confirm that everything appears as expected.

(6 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako case