Import a Large Excel File

Last modified: April 18, 2024

1 Introduction

Use XML-to-domain mapping to create a new import entity from an Excel sheet in a quick, semi-automated way.

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

Import a large Excel file with a lot of columns

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

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

4 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:

1
2
3
4
5


<?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:
1 2 3 4

</xs:sequence> </xs:complexType> </xs:element> </xs:schema>
Your file should look like this: Country Import.
Click Save.

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