Excel Import

Updated on 25 Mar 2024
3 Minutes to read
Contributors

Print
Share
Dark
Light

Article Summary

Share feedback

Thanks for sharing your feedback!

About Excel Import

Early in a project there may not yet be access to an external system and there may be no, or inadequate content delivered with Syniti Migrate. This section imports via Excel spreadsheet the datasource tables and columns. This page ONLY imports Excel extensions and fails should the user attempt to import other extensions (Ex: txt, csv)

Note
UTF- 8 double-byte characters load using Excel Import (EX: Japanese or Korean language).

View the following page by navigating to Administer > Excel Import from the Syniti Migrate Homepage. The initial page displays as a vertical split page with Excel Import Setup in the left panel and Preview Imported Data in the right panel.

Add a File for Import

Click the Select File button to open the local file path directory. Select the appropriate Excel file, and then click Open button to add this file to the page. These imports are usually for target table data.

If the file contains two or more rows as a header/description, update the value for Start Row to the first row of data. The first ten rows of data displays with the count of total rows showing in the Import Setup panel as Number of Records Detected.

Should the file contain Header rows, toggle the switch active and the top row will display as a Header row.

The Excel Import page includes these fields:

Section	Heading	Definition
Import File	Start Row	Enter the row number where the data begins for import.
Import File	My data contains headers	Toggle this switch to active should the file contain Header rows.
Select Data	Datasource Id*	The name of the SQL database that stores this new table.
Select Data	Table Name*	Table name (may be existing or new name)

Click the drop-down list for field Datasource Id and select the appropriate working database to store this new file as a table. The field labeled Table Name allows for selection of an existing table name - used for SAVE AS. A new table name may also be manually entered, and that new table is added to database as the import is executed.

The Table Name field allows for selection of an existing table name - used for SAVE AS. A new table name may also be manually entered as shown in the image below. Click Import Excel button to import this file as a new table to the SQL database. If the table does not already exist in this database a warning message displays. Click OK to proceed or CANCEL to quit the import process. The fields of this table are set as NVARCHAR(4000) to allow all record data to load without issues.

If the table exists within the database, the system displays a window with messaging "Do you want to append or truncate and then insert the data?" - Select the appropriate action for Cancel, Append, or Truncate. The system displays a progress bar with percentage complete.

Since the table in this case does not exist already, a selection window displays with message that the table does not yet exist. Do you want to create new table, and select OK to proceed.

Note
Larger files display a progress indicator while the file is imported into the system. Files larger than 50k rows or 10MB produce an error and must be split into smaller file sizes.

At this point, If the import is successful, a message displays "Imported Successfully!"

The other option is to select Clear Fields button to remove all data and reset the page to same initial entry.

If the Import of spreadsheet data fails, a message window displays to provide details of the issues - Any issue details are stored within the Debug Log in pathway Monitor > Debug Log. Click OK and the page resets back to the main page.