Learn how to loop through an excel file to import data.

This tutorial teaches you how to design a page to import and handle table records from an Excel file. Learn about Structures and Entities.
Start Tutorial
in the Agile Platform IDE
Don't have the Agile Platform? Download it here!

Tutorial Transcript

This section presents a transcript of the tutorial contents for documentation purposes only.
These tutorial instructions appear in Service Studio within the full application context.

Download baseline eSpace


Step 1
Import Data from Excel Files
In this tutorial we're going to learn how to use the  Excel To Record List and import data from an Excel file. 



Click NEXT to continue

Step 2
Create the Page to Import from Excel
Letʼs create the page where the user will choose the Excel file and import the data.
  1. Drag a  Web Screen and drop it where indicated
  2. Type Contact_Import for the name

Step 3
Link to the Newly Created Page
Let's add a link to the  Contact_List to access the page we have just created.
  1. Double-click on  Contact_List to open it
  2. Drag a  Container and drop it next to the Export Contacts To Excel link
  3. Type Import Contacts from Excel inside the newly added Container
  4. Select  Import Contacts from Excel, right-click on it, select Link to, select (Another Destination...), and choose  Contact_Import from the list

Step 4
Design the Page Heading
Now we're going to design the heading of the  Contact_Import by setting a proper title.
  1. Double-click on  Contact_Import to open it
  2. Click inside the  Title placeholder to place and type Import Contacts
  3. Select the  Common\Menu web block and set ActiveMenuItemId to Entities.MenuItem.Contacts

Step 5
Design the Page Body Layout
After the heading, we're going to design the layout of the  Contact_Import body.
  1. Drag a  Table and drop it to the  Main Content placeholder
  2. In the dialog configure the table to have:
    -     3 Rows
    -     1 Column
    -     5 pixels of Spacing
    -     5 pixels of Padding

    And then press the Ok button

Step 6
Where's the Table?
Within Service Studio, the web page editor displays the web page similar to the way it will display at runtime. This means that if you do not add borders to your table you will only be able to see them by hovering inside its boundaries.

Alternatively, you can turn on  Outline Mode. This mode allows you to see the borders of the table you just created. If you'd like, give it a try now and leave it on if you're more comfortable working that way.

Step 7
Design the Page Body Content
Itʼs time to build the parts of the UI that will allow the user to import an existing list of contacts to fill the  Contact_List
  1. Type Excel File: inside the first cell, drag an  Upload and drop it after the text
  2. Right-click on  Contact_Import, select  Add Input Parameter, and type Overwrite for the name
  3. Set the Data Type of the  Overwrite to Boolean and the Is Mandatory to No
  4. Type Overwrite existing contacts? inside the second cell, drag a  Check Box and drop it after the text
  5. Select the Overwrite variable from the Variable drop down list of the  Check Box
  6. Drag and drop a  Button into the third cell and change the label to "Import". Then, open the Destination property drop down list and select  (New Screen Action)

Step 8
Add a Cancel link
To finish the screen let's add a Cancel link that directs the user back to the list.
  1. Click after the  Import Button to place the cursor and type " or " (without the quotation marks).
  2. Drag a  Link and drop it after the " or "
  3. Open the Destination property drop down list, select  (Select Destination...), choose  Contact_List from the list, and click OK
  4. Click inside the link to place the cursor and  type Cancel

Step 9
How are Excel Rows Defined?
When importing from an Excel file, you can define the records that will map to the Excel rows using either a  Structure or an  Entity:
  •  Structures: for data that only stays in memory and is not persisted in the database
  •  Entities: for data that is persisted in the database

Step 10
Use the Excel to Record List
Let's add the  Excel To Record List tool to the  Import action.
  1. Double-click on  Import to open it
  2. Drag the  Excel To Record List and drop it onto the connector between  Start and  End
  3. Open the drop down list of the Record Definition property and select  Excel_Contacts
  4. Open the drop down list of the File Content property and select (Expression Editor...)
  5. Double-click on the Content property of the Upload1 widget
  6. Click on the Close button

Step 11
Get Duplicated Records
We're going to build the logic to go through all loaded Contacts and get the ones with the same Email address.
  1. Drag a  For Each and drop it onto the  ExcelToRecordList1 outgoing connector when it glows
  2. Open the drop down list of the Record List property and select  ExcelToRecordList1
  3. Drag a  Query and drop it to the right of the  Excel To Record List, and type GetContact for the name
  4. Double-click on  GetContact to open it, right-click on the Parameters folder, select  Add Query Parameter, and type Email for the name
  5. Right-click on the Entities / Structures folder and select  Add Contact from the list
  6. Right-click on the Conditions folder and select  Add Query Condition
  7. Expand the  Contact and double-click on  Email, type ' = ' after it, and then double-click on  Email
  8. Click on the Close button of each editor

Step 12
Check Duplicated Records
We're going to design a condition for duplicated records and whether to overwrite them or not.
Connectors are going to be added in this step to define the flow.
  1. Connect the  For Each to the  GetContact
  2. Click on  GetContact, open the drop down list of the Email property, and select 
    ExcelToRecordList1.Current.Excel_Contacts.Email
  3. Drag an  If, drop it below  GetContact, and type  Discard? for the label
  4. Select (Expression Editor...) in the drop down list of the Condition
  5. Type the following expression: 
    not GetContact.List.Empty and Overwrite = False 
    into the Expression Editor (or copy paste it from here) and click the Close button
  6. Connect the  GetContact query to the  Discard? if
  7. Connect the  Discard? if to the  For Each

Step 13
Map an Excel Row to an Entity Record
We're going to design the assignment of an Excel row to a Contact record to be stored in a database. 
Notice how we make use of the Contact record from the  GetContact query instead of defining a specific  Contact local variable for that purpose.
  1. Drag an  Assign and drop it below the  Discard? if
  2. Connect the  Discard? if to the  Assign
  3. Click on the  Assign, open the drop list of Variable 1, and select (Select Variable 1...)
  4. Follow the arrows to expand  GetContact until you get to the  Name attribute, click on it, and then click OK
  5. Open the drop down list for Value 1 and select: 
    ExcelToRecordList1.Current.Excel_Contacts.Name
  6. Open the drop down list for Variable 2  and select:
    (Auto Assign GetContact.List.Current.Contact)

Step 14
Create or Update Records in Database
With the Contact records assigned, it's time to design the logic to create or update them in the database.
  1. Under Data, expand  Contact, drag  CreateOrUpdateContact, and drop it bellow the  Assign
  2. Connect the  Assign to CreateOrUpdateContact 
  3. Connect  CreateOrUpdateContact to the  For Each
  4. Click on  CreateOrUpdateContact and set its Source property to  GetContact.List.Current

Step 15
Go Back to the List after Importing
After importing all of the Excel records, we're going to display the  Contact_List web screen.
  1. Drag a  Destination and drop it onto the  End element to replace it
  2. Double-click on  Contact_List to select it

Step 16
Deploy the Application
Let's deploy the application with the changes.
  1. Click on the  1-Click Publish button.
  2. Wait for the 1-Click Publish to finish...

Step 17
Let's Test the Application!
  1. Open the application in the browser by clicking  Open in Browser
  2. Click on the Contacts menu, click on Import Contacts, and upload an Excel file.

Step 18
Congratulations!
You have successfully completed this lesson. Now you know how to:
  • Design a  Web Screen to import Excel data
  • Use the  Upload to handle files
  • Define the Excel row
  • Use the  Excel To Record List tool
  • Handle duplicated records
  • Map an Excel row into an  Entity record
  • Store imported records in the database

Your next step is to learn about:
Debugging and Troubleshooting

Click FINISH to end this lesson.

What IT pros like you say about the Agile Platform

“

It’s rare that I’m really impressed by software [...] but the Agile Platform is one heck of a product.”

Joshua Price, MakeTechEasier

“

It does what it claims to do, and does it well. In this industry, that is extremely unique.”

Justin James, TechRepublic

“

I'm blown away by this tool, it's depth, and the level & quality of training you provide.”

T.S. - Developer

Training & Certification.

Want to become a pro and extend you knowledge with in-depth training? See our training program.

Agile Network community.

Download samples and technical resources, get help from peers, or discuss your favorite development topics in the forums. Check out the Agile Network.

Free Download.

Start creating your next great web app with the Agile Platform! Download now.

Great web apps of all types.

The Agile Platform™ is being used to build custom web applications and websites - from apps used by dozens of employees to global web portals used by millions of users. Custom CRMs, Intranet Portals, HR solutions, eCommerce sites, Invoicing and Billing Systems... there's no limit to the type of web applications you can build.

Great companies of all sizes.

The list of companies using the Agile Platform™ spans from startups to Fortune 50 companies in 16 countries and across 19 industries including Banking, Telecom, Insurance, HealthCare, Consumer Goods, Distribution, Manufacturing, Utilities, Education and Government.