Import a Large Excel File

Last modified: November 19, 2024

Introduction

Use XML-to-domain mapping to create a new import entity from an Excel sheet in a quick, semi-automated way. There is also a new & fully automated way available to create placeholder entity based off of Excel files, refer section Using Data Importer Extension to create Entity using a Large Excel.

This how-to teaches you how to do the following:

Import a large Excel file with a lot of columns

Prerequisites

Before starting this how-to, make sure you have completed the following prerequisite:

Have an app with the MxModel Reflection and Excel Importer modules installed and configured from the Mendix Marketplace.

Modifying Your Excel Sheet

The Excel sheet you receive in this scenario contains almost every country in the world, as well as some supporting data. This data has to be imported into your application.

You can find the Excel sheet here: Countries.

You are going to make an XSD schema from the headers in the Excel sheet so you can import the data into the model.

To modify your Excel sheet, follow these steps:

Select the header row with all the country names.
Copy and paste them in a new sheet using the transpose function:

Your headers should be listed vertically and your sheet should look like this: Countries Transposed.

You are now ready to add some tags around the column.
Add one column to the left.
Place the following string in cell A1:
```
<xs:element type="xs:string" name="
```
Drag the string all the way down to the last country.
In cell C1, enter the following string:
```
"/>
```
Like you did with the previous string, drag it down to the last country. The Excel sheet should now look like this: Countries with Tags.

Now, group the three different columns into one column. This is necessary to copy the whole column into an XSD file.
Select cell D1 and type in the following into the formula box:
```
=(A1&B1&C1)
```
Drag the cells down like you’ve done with column A1 and C1. Now, column D should have columns A, B, and C combined into one, and your sheet should look like this: Countries with Tags and Column D.

Preparing Your XSD File

You have used some of Excel’s basic functionalities to create the first part of the XSD structure. For a proper XSD file, it needs a header and a footer. To prepare your XSD file, follow these steps:

Open a new file and name it CountriesImport.xsd.

Place the following text as the header of your XSD file:

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified">
<xs:element name="CountriesImport">
<xs:complexType>
<xs:sequence>

Go back to your sheet, copy the content from column D, and paste it underneath the header.

Sometimes the content copied from Excel contains extra double quotes. To eliminate these, paste the Excel content into Word, and then copy it from Word and paste it into the XSD file.
Place the following text as the footer:
```
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
```
Your file should look like this: Country Import.
Click Save.

Importing into Your Application Project

The XSD file is ready to be imported into your app. To import it, follow these steps:

Open your app and create a new XSD schema:
With the new XSD schema, create the XML-to-domain mapping. Do this by right-clicking the module > Add other > Import mapping.
Check all the attributes listed. After clicking OK, you see a mapping entity with all your countries.
You will now generate a real entity from it that you can use as an import table for your Excel sheet. Click Map automatically:

There’s your entity!
Go to your domain model and set the entity’s Persistable property to Yes.

The data is imported to the page, as seen in the image below:

To keep your application clean, you can delete the XSD schema and XML-to-domain files from your app.

A video demonstrating this technique can be viewed below:

Using Data Importer Extension to create Entity using a Large Excel.

Mendix Studio Pro 10.7 or above is required for this approach. You can also use these steps to create an entity from a CSV file.

The Data Importer extension can be used to automatically create an entity in your domain model. This example uses the same input Excel (countries.xlsx) to create an entity.

To create entity in your domain model using an Excel sheet, follow these steps:

Right-click your module and navigate to Add other > Data Importer.
Provide a name for the Data Importer document. You will then have the ability to upload a sample file.
Drop the Countries.xlsx file or click Select a local file and navigate to the file.
Set the configuration in terms of Sheet Name, Header Row No, and Read Data from.
Click Preview Source Data & Entity
- If the column names do not conform to Mendix naming conventions, they will automatically be corrected.
- The extension identifies correct data types of each column (such as string, boolean, or date).
After reviewing the preview, click Create Entity and a non-persistable entity (NPE) is created in your domain model.

You can change the name of the entity or change its persistence later, if necessary.