2. Click the New button.
3. In the Name field, type in a suitable name for the import. Leave the Template field blank.
4. Click OK. The Data Import dialogue box, containing a number of different tabs, is displayed.
5. In the Excel Load tab, you must now complete the fields in the File Information section to identify the location of the required data in the
7. If the first row of the spreadsheet is made up of column names (headings), so that the data itself actually begins on the second row, enable the First Row Contains Column Names option.
8. With the data structure defined, you now have to retrieve a sample of data from the file to help you choose exactly which data items to use for the import. Click the Query Sample button and observe that the Data Columns/ Data Preview list now shows the first of the queried set of sample records as follows:
The record is displayed as a list of column names (auto-generated if non- existent in the source file), together with their respective values. You can display other records in the set by means of the >> and << buttons at the top right.
9. Once you have decided which columns should be included in the import process, select their associated checkboxes. Only these columns will now be available to you for mapping to the target Supportworks database, as described in the following steps.
11. In the Database field, select the database into which you want the data imported.
12. In the Table field, select the table, within that database, into which you want the data imported. Note that the Unique Key field is now filled by default with the name of the selected table’s primary-key column, and that the Target Columns list is populated with all the columns of that table.
13. If necessary, you can manually select a unique key other than the primary key.
14. By virtue of the default Data Update Operations setting, the import process will both create (insert) and update records in the Supportworks database, as dictated by their absence or presence with respect to the corresponding source records. If, however, you wish to preserve the Supportworks data on this import, you should enable the “Only allow inserts” option. Alternatively, if you wish to prevent further Supportworks records being created on this
15. You are now ready to map your previously selected source columns to columns of your selected target table. First select the checkboxes associated with your proposed target columns.
16. Highlight one of the selected target columns and click anywhere in the Value Transformation and Assignment field.
17. For a simple mapping, click the Insert Value button and, from the menu that pops up, select the source data column that you want mapped to the target column you have highlighted. Notice that the chosen column appears in the field in the form xls.<datacolumn> or xls[“<data column>”], depending
on whether or not there are spaces in the source data column’s name.
For more complex mappings, you would have to manually type the required expression into the Value Transformation and Assignment field. See the section entitled Advanced Import Techniques on page 56 to find out what is possible here using JavaScript.
18. If you want the import process to skip each Excel-file record where there is no value assigned for this column (or for any column in the expression), enable the “Non-empty value required” option. This will always be enabled if the target column being mapped is a unique key.
19. If you have entered an expression manually into the Value Transformation and Assignment field, you can confirm correct resolution of the currently highlighted target column’s values by clicking the Check Syntax button. This will display, one at a time in the Preview Value Assignment field, the values for that column as computed by the expression, using the set of sample data you previously queried. You can use the >> and << buttons to show the following and preceding values, respectively.
20. Repeat step 16 to step 19 for each of your proposed target columns. 21. Click Apply.
22. Having completed the mapping, you can now run or schedule the import. First select the Control & Schedule tab.
23. If you wish to run the import immediately, click the Run Import Now button. A separate window is displayed, in which you can see the import’s progress. 24. On completion of the import, you can click View Log in the progress
window to view the details of the import process. Note that this is a
cumulative log, which means that the current import details are appended to any existing details already logged in this session. (The log will be deleted when you exit from the Data Import Manager.) You can also view the log by using the View Log File button located next to the Run Import Now button. 25. Click Close to dismiss the progress window.
26. If you wish to schedule the import, first enable the “Schedule this import” option.
27. Specify when, or how often, the import is to run by selecting the relevant item from the “Run this import when” field’s drop-down list. If you select
In this case, select the day(s) of the week on which the import should run, and choose whether this should keep occurring indefinitely, or a specified number of times.
If you select any frequency other than “Once a day”, the multi-day selection options are replaced in all but one case with a single-day drop-down
selection field that allows you, for example, to choose the first day of the relevant period on which the import should run. The exception is “Once every period”, which retains the multi-day selection options, but replaces the Starting At field with an Every (n) Minutes field, allowing you to specify repeated occurrences of imports during the course of selected days of the week.
In the Starting At field (for those options that have it), specify the date and time at which the import is to run, or at which the import scheduling is to “switch on”. Alternatively, for the “Once every period” option, enter the period (in minutes) required between successive import occurrences. 28. Click OK.