Skip to main content

logoCorrectSize.png

Workday Adaptive Planning KB

Set Up a Spreadsheet Data Source

You can use Adaptive Insights Integration to extract data from an Excel spreadsheet.

Set your browser language to U.S. English before importing data using a spreadsheet data source. Data input for spreadsheet data sources is tested and certified for the U.S. English format only.

Before You Begin

  • Verify every cell in a column contains the same data type in your spreadsheet. Adaptive Insights Integration bases its data type on the contents of the entire column.
  • Permissions required: Integration > Data Designer and Integration Operator

How You Get There

Navigation Icon5.png Go to nav menu navicon.gif > Integration > Design Integrations. 

Create the Spreadsheet Data Source

  1. Access the Data Designer by going to Integration > Data Designer.
  2. In the Data Source folder in the Component Library on the right side of the screen, click Create New Data Source.
  3. Select Spreadsheet as your data source type and enter the name for the data source.
  4. Select Create.
    The center area of the screen displays your new data source settings.

5. Enter the data source information:

  • Skip failed uploads: Skip failed uploads from the spreadsheet. When you import from multiple tables, if one of the tables fails on upload, the process continues to the next table instead of ending with an error.
  • Log level: Select a log level from the drop-down list to specify the detail for the logging for this data source.
    • Error: Only logs errors.
    • Info: Logs all basic information, such as when the data source was updated.
    • Verbose: Provides very detailed information about all phases and actions. (This level is used primarily for debugging or auditing, as it may produce more log information than is practical for typical use.)

There is an import limit of 100 columns for each sheet used as a data source. Any columns beyond the 100th will not be imported.

Import the Spreadsheet

When you select the spreadsheet, Adaptive Insights Integration opens the spreadsheet and imports the data. Adaptive Insights Integration also imports the spreadsheet structure (field names, order of fields). The import action assigns a default data type to each column but does not interpret any formatting rules associated with each spreadsheet col­umn. The data structure appears in the Data Components menu on the left side of the screen. This menu populates with the worksheets and contents of the imported spreadsheet.

  1. Select Import Spreadsheet on the Actions menu.
  2. Select Browse to search for a spreadsheet file.
  3. Close the Import Spreadsheet dialog box.
  4. Click Save on the Actions menu.

By default, import uses the import mode All records replaced each time a data import is run. This mode:

  1. Clears all the rows in the staging area.
  2. Imports the data.
  3. Populates staging table.

You can use other import modes that append or merge data, described later in the Customize the Table section.

Reimporting and Changing Structure

When you load a spreadsheet, Adaptive Insights Integration determines the data type from the column contents. The structure of your spreadsheet can change if you edit your local file and reimport it after:

  • Renaming a sheet
  • Renaming a column
  • Removing a column
  • Changing the column order
  • Adding a different type of data to an existing column

If a single cell in a column of integers changed to a real number, the entire column changes data type on reimport. The loaders that depend on this data source may not function correctly.

Add Tables to a Spreadsheet Data Source

After you have imported the spreadsheet’s structure, you are ready to specify the informa­tion you want to import from the spreadsheet. The Tables to Import section of the screen lets you configure the data imported from the spreadsheet.

  1. Expand the entry for the data source in the Data Components menu.
    Each worksheet in the spreadsheet appears as a separate item. Data components with a slash through their icon will not import.

2. Drag and drop the tables you want to import to the Tables to Import section (the staging area) in the center of the screen. A tab appears for the table and information from the spreadsheet in the Tables to Import area, letting you see what added. Each table tab also contains a drop-down menu you can access by clicking the triangle next to its name.

Manage Columns

If a table contains a large number of columns, you can use Manage Columns to do quick edits for which columns to import. The Manage Columns dialog box displays the columns in a table so you can then check or uncheck columns for importing.

To manage columns for importing, follow these steps:

  1. Click the arrow on the tab to the right of the table name.
    A floating menu with several options appears.

  2. Click Manage Columns on the floating menu.
    The Manage Columns dialog box appears.

  1. Check the box next to a column to import it. When you check a column, the column also appears within the preview window after the popup contents save. Unchecking a column removes the column from the preview window.

    You cannot change the import status on system-generated fields such as isDe­leted. You can also change the import status on individual columns by selecting col­umn properties and setting the property or by dragging and dropping the columns onto the designer.

Customize the Tables

For each table, you can customize the way in which data is imported.

To customize the table settings, follow these steps:

  1. Click the arrow on the tab to the right of the table name.
    A floating menu with several options appears.

  2. Click Table Settings on the floating menu.
    The Table Settings dialog box appears.

   3. Enter the table settings:

  • Data Import Mode: Select one of the options from the drop-down list.
    • All records replaced each time a data import is run
      This is the default. The data in the new version of the spreadsheet replaces the previous version in the staging area.
    • Merge (add, update) received rows using the key columns
      New data is merged with existing data by matching key columns. After select­ing this option, you can set the key columns in the Column Settings area.
    • All received rows are new and so should be added
      The data already imported is left as-is and all rows of the spreadsheet you are importing are added to the table.
  • Sheet Has Header Row: Check this box if this sheet has a header row. If your spreadsheet has a header row and you leave this box unchecked, all rows includ­ing the header rows are treated as data rows and the column letters (A, B, C, etc.) are used for headings.
  • First Header/Data Row: Enter the number of the first header row (if there is a header row) or the first data row.
  • Ignore Parsing Rows: Check this box to ignore any parsing errors when updat­ing the table. If you select to ignore parsing errors, any cells with errors, such as text appearing in a date column, are ignored and the cells with errors are imported as empty. If you do not select to ignore parsing errors and there are errors, the spreadsheet will not import successfully. (You can use the error log to help you identify parsing problems.)
  1. Click Apply to apply the settings.
  2. Repeat steps 1-3 for each table.
  3. Click Save to save the settings in the data source.

Set Column Options

While using the Tables to Import section, you can see a preview of the data as it exists in the data source or as it exists in the staging area after it imports. Select Spreadsheet or Staging table from the drop-down to preview data.

You can change the options for each column by mousing over its heading and click­ing the drop-down menu next to its name:

  • Sort Ascending: Sort the entire table based on this column in ascending order.
  • Sort Descending: Sort the entire table based on this column in descending order.
  • Column Settings: This dialog is used to change the properties of the column within the staging table.
    • Name: The name of the column, displayed in the header.
    • Column Type: The type of data used in the column.
    • Convert Column Type: Select the new type from this drop-down menu.
  • Exclude column from import: Select this to remove this column from the data being imported.
  • Delete custom column: Select this to delete the column. (Available for custom col­umns only.)

Download Data from a Staging Table

You can download data from a single staging table.

To download data from a single staging table, follow these steps:

  1. Click the down arrow in a table heading in the Tables to Import section.

  2. Select Download Data.

  3. At the prompt “This operation will send an email with a url to download the data,” click Send. Adaptive Insights Integration sends an email containing a URL to the email account associated with the data designer.

  4. Open the email and click the URL. The data is downloaded in .csv format.

Use the Advanced Filter

Below the Source drop-down menu, you can click Advanced Filter to reveal options for fil­tering the data in the Tables to Import preview area. The Advanced Filter provides tools to view and browse a subset of the data within a staging table. By changing filter properties, you can change the view of the data shown within the preview window without changing the data in the staging area.

The preview area is specifically for previewing a small portion of the data in the staging area. You can explore the data that is available from the spreadsheet and verify that you are getting the data you expect by examining the staging area. (Filters set in the preview function do not affect the actual data in the staging area.) The Advanced Filter lets you experiment with the data in the preview to set up a data import filter.

The options for the Advanced Filter are:

  • Distinct Rows: Check this box to hide duplicate rows.
  • Max Rows: Limit the number of rows displayed in the preview area by entering a number in the text box.
  • Columns: Check or uncheck columns in this drop-down menu to show or hide the columns displayed.

Removing a column from this list does not remove the column from the import list.

  • SQL Filter: Click the large text box to display the Edit SQL Expression dialog box, shown below.

Enter a SQL expression in the field. This is a filter to restrict the rows being previewed. Click Apply to automatically check the SQL syntax of the filter. (If there are any errors, the error is indicated in the expression.) For detailed SQL syntax help, you can click Online Help in the Notes section. The expression you entered appears in the Advanced Filter section of the preview window.

Click Remove Filter (next to the Source drop-down menu) to clear all advanced filter set­tings.

As part of the table customization process, you can also create join tables and custom columns using SQL expressions.

Clear the Staging Area

Information remains in the staging area until you clear it.

To clear data from the staging area, do the following:

  1. Select Clear staging tables in the Actions menu.
    The staging area for all of the tables in the data source clear.

  • Was this article helpful?