- 25 Mar 2024
- 3 Minutes to read
- Contributors
- Print
- DarkLight
Excel Import
- Updated on 25 Mar 2024
- 3 Minutes to read
- Contributors
- Print
- DarkLight
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.
Issues During Import of a File
The importing of an Excel file to the system is a pretty easy process, but there are a few guidelines to follow.
The file format MUST be Excel, not csv, not text, etc.
The file Must have only 1 tab sheet - the upload pulls from the first tab only.
The file MUST have columns and data.
See examples below:
File Extension issues
Should the user attempt to import a file that is not Excel, the process fails. The image below provides the Error message produced from this effort.
File Data Issues
The file for import must contain columns and data. If there is no data in the file, an error message displays.