Import Data

Users have the ability to import data into the DSP® from an Excel spreadsheet created by the user (based on the current DSP® page) or from a spreadsheet previously downloaded from the DSP®. If the imported spreadsheet has multiple worksheets, the user will be able to choose which worksheet data to import. If multiple worksheets are to be imported, a separate import process must be performed for each worksheet.

NOTE: When importing data via Excel, records that are locked by another user are listed as failed on the Import Results tab. The user has the ability to download the failed record(s) and upload the data later when the record has been unlocked.

NOTE: DSP® will calculate functions in an Excel spreadsheet before importing the results.  It is the result of the calculations that will be imported.

NOTE: If the user passes a value of “Y”, “True”, "true", or “1”, the check box would be enabled during the import process. If the user passes a value of “N”, “False”, "false", or “0”, the check box would be disabled during the import process.

NOTE: When importing records via Excel Integration, if a cell in the Excel file is left blank, the default values from the page are applied. Defaults values can be defined either on the page column property, the page’s defaults view or a database default. To insert a null value in a column that has a default, the special keyword, {NULL}, can be placed in the cell in the Excel file.

NOTE: When importing records using Excel Integration, the pre-check panel displays a message indicating which columns contain default values. 

To import data: 

  1. Navigate to the page where the data is to be imported in the DSP®.

  2. Select Excel Integration from the page gear menu; the Excel Integration panel displays.

  3. Click the Import Data tab.

  4. Click the Click here to browse for your Excel file button.

  5. In the Choose File to Upload window, navigate to the desired file.

  6. Select the file.

  7. Click Open; the Import Option tab displays along with a confirmation message informing the user the file chosen is valid

    NOTE: If the file is invalid, an error message displays indicating the file is invalid.

  8. Click Insert Records.

  9. Once the process is complete, the Import Results tab displays.

    NOTE: While the Excel import is processing, a process bar and a Stop Import button appears. Click the Stop Import button to cancel the process.

    NOTE: The worksheet name appears in the Choose Worksheet list box. If there is more than one worksheet in the template, click the down arrow to select the worksheet to import.

  10. Modify the value in the Data Starts on Row field if necessary.

    NOTE: The value entered in the Data Starts on Row field is the starting row of the data in the Excel template where the user wants to begin importing data. The default value for the Data Starts on Row field is the first row containing data after the columns headings in the Excel template.

  11. Modify the value in the Column Names on Row field if necessary.

    NOTE: The value entered in the Column Names on Row field is the starting row of the column names in the Excel template. The default value for the Column Names on Row field is 1.

  12. Click the Insert Records button; the Import Results tab displays.

    After data has been imported via the Excel import functionality, a list of inserted versus failed records display and is grouped with error summaries based on reasons for failure:

  • Inserted – These records were successfully imported into DSP®.

  • Failed – These records were not imported. Expand the list to display the reason(s) and row number of the failed record. Click the failed row to display the details of the failure. Reasons for failure include but are not limited to:

  • Locked records – An attempt was made to import a record that is locked. Refer to Lock Records for more information.
  • Incorrect data type – The data type of the imported data does not match the data type of the corresponding data in the DSP®.
  • Data length exceeded – The length of the data for the imported value was exceeded.
  • Foreign key constraint violation – An attempt was made to insert a record with a value whose column references a foreign key constraint. The value was not found in the foreign key table.
  • Duplicate primary key – An attempt was made to insert a record whose primary key already exists in the table.

    NOTE: A record can fail for a duplicate primary key when the record is initially imported, if there are no records on the page and if the import file contains duplicate records. If the record already exists on a page, it is updated on import, and does not fail for duplicate primary keys.

  • Data Volume Limitations - The data volume limitation on imported spreadsheets cannot exceed the Excel Import Record Limit. The default for this parameter is 100k rows. Refer to the  Parameters page for more information.

If records fail to import, a button appears in the upper right corner of the Import Results window.

To download an Excel spreadsheet of records that fail to insert:

        1. Click the Download Excel Sheet of Records that Fail to Insert button.

        2. Follow the browser specific instructions that are presented to open or save the file.

  • Skipped – These records were skipped. Blank rows in the spreadsheet are skipped. Click the row listed to view the reason it was not inserted.

NOTE: If errors occur, modify the worksheet accordingly and perform the import again.

NOTE: If the data is encrypted in the DSP®, then the data is encrypted on import.

NOTE: After performing an Excel Import, a Bulk Execution icon displays in the top right area of the Results tab. When this icon is clicked, the Bulk Execution panel opens and is filtered on the records which were successfully inserted. The user can then proceed to perform the Bulk Execution process. Refer to the Bulk Execution topic for more information.

NOTE: If Audit is turned on for the table when the Excel import is performed, an audit is conducted to show which fields have changed. To view the results of the audit, select Admin > Data Sources > Audit Report in the Navigation pane. The Data Source Audit Report displays. In the table, the field names are listed along with the data type and data length in the source table and audit table. If the Audit Table column is populated, that means the corresponding field was changed.