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. More importantly, you'll be learning how to establish relationships between records, as the data is imported.

Preparation

Use the Application Wizard to create an application called Movie Reviews. It should have the following objects and fields:

• Movies
  • Title - Text Field

• Reviewers
  • Name - Text Field
  • Email - Text Field

• Reviews
  • Rating - Number (a value in the range 1..5)
  • Commentary - Text Area (not a 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 labels for the objects.
• In the Reviews object, modify the Rating field:
  • Restrict its values to the range 1..5
  • Change the display length to 3

Learn more: Use the Application Wizard.

Note: You might have noticed that there was an option to create a new application by importing data. For a simple spreadsheet, that's the way to go. But when you need to construct relationships between object records, use the process described here.

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. Specify the filename and type:

   • File name - Movies.csv
   • Type - 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 Movies.csv is created.
7. Repeat the process for the Reviewers
8. 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 Num,Title 1001,The 300 1002,War Horse </syntaxhighlight>

Reviewers.csv

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

Name,Email Avery Goodview,avery@goodview Knota Goodseat,knota@goodseat Luvda Popcorn,luvda@popcorn </syntaxhighlight>

Reviews.csv

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

Movie,Rating,Commentary,Reviewer 1001,5,Awesome action,Avery Goodview 1001,5,Steamy and lovely,Knota Goodseat 1002,3,Rather predictable,Avery Goodview 1002,5,A real tear-jerker,Luvda Popcorn </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.)

Start by importing data into the Movies object:

1. Go to Settings > Data Management > Import Data
2. You've already created the CSV file, so Step 1. Data Preparation is done.
3. Under Step 2. Upload File, choose the object you'll be importing into: Movies.
   A new tab appears: Upload CSV File
4. Click [Choose File] and select the Movies.csv file you created.
5. No column mapping has been defined, as yet, so click [Next].
   The Map Columns tab becomes active.
6. Map the columns defined in the file to the object fields:
   • Record Id - Index Num - column (1)
   • Title - Title - column (2)

   File:HowTo ImportProcedure2 MapColumns.png

   Note that the labels defined in the heading row of the spreadsheet table are displayed in the dialog, to help you create the correct mapping.

7. Click [Next].
   A dialog appears asking you to confirm the mappings.
8. After checking to make sure they're right, click [OK].
   The Set Attributes dialog becomes active.
9. Give the mapping profile a name (say, "Movie Mapping"), and optionally a description, in case you need to use it again.
10. Click [Start]

--examine the Record Locator setting for the Reviewers object