HowTo:Import Data from an External System

For:   Designers Level: Beginner Time:  20 minutes See more:     ◾ HowTo Guides

If you have data in an external system, you can generally export it as a plain text file, with one record per line, where values in each line are separated by values. That kind of file is known as comma-separated value (Template:CSV) file. Using such files, you can import data into your application objects.

In this guide, you'll create a few items of data in a spreadsheet, and use that. But the data could come from any system that is capable of exporting CSV data.

Preparation

Using process described in the first step of HowTo:Create_a_Simple_Application, use the Application Builder to create an application called Movie Reviews. It should have the following objects and fields:

• Movies
  • Title (Text Field)

• Reviews
  • Rating (Number -- a value in the range 1..5)
  • Commentary (Text Area)

• Reviewers
  • Name (Text Field)

And the following relationships:

• One Movie can have many Reviews.
• One Reviewer can have many Reviews.

After the application is created:

• Adjust the singular and plural labels for the objects.
• In the Reviews object, modify the Rating field to restrict its values to the range 1..5.

Learn more: Use the Application Wizard to Create an Application.

Create a Spreadsheet with Sample Data

To start, create a spreadsheet with worksheets for some real movies, a whimisical list of reviewers, and sample reviews. (If you'd rather not deal with the spreadsheet, paste the data from the next section into CSV files.)

1. Create a table of Movies

   

2. Create a table of Reviewers

   

3. Create a table of Reviews

   

In those worksheets, movies have identifiers that are referenced by reviews. Those identifiers are numeric, and none are smaller than 1001. By meeting those requirements, the movie identifier can be used as a Record ID in the platform.

Export the Data as CSV Files

Next, export the data into files that contain comma-separated values, or Template:CSV data. (This process is based on Excel. The process for other spreadsheets is similar.)

1. In the spreadsheet, go to the Movies tab.
2. From the main menu, choose Save As.
3. For type, choose CSV (Comma delimited) (*.csv)
4. Click [Save]'
   A dialog appears, informing you that only the current worksheet can be saved.
5. Click [Ok] to save the current worksheet.
   A dialog appears, telling that not all features are supported in this format. (For example, formulas.)
6. Click [Yes] to save in the specified format.
   A file called MovieReviews.csv is created.
7. Copy the file to Movies.csv.
   (You can't rename it unless you close the spreadsheet.)
8. Repeat the process for the Reviewers
9. Repeat the process for the Reviews

You can now inspect the data files in a text editor. They should look like this:

Movies.csv

<syntaxhighlight lang="html4strict" enclose="div">

Index No.,Title 21001,The 300 21002,War Horse </syntaxhighlight>

Reviewers.csv

<syntaxhighlight lang="java" enclose="div">

</syntaxhighlight>

Reviews.csv

<syntaxhighlight lang="java" enclose="div">

</syntaxhighlight>

Import the Data

The process here is to start at the top of the lookup chain, so that the Lookups in the incoming record all resolve to an actual record. (It isn't strictly necessary to do things in that order, but it's good form.)

Coming soon...