Before you convert data from Excel to CSPro format, you must specify two sets of mappings:
- Excel worksheets to CSPro records
- Excel worksheet columns to CSPro items
There are a few rules concerning valid mappings:
- There must be a one-to-one correspondence of mappings from worksheets to records, meaning that record items must be mapped to columns on the same worksheet, and that only one record can be mapped to each worksheet in the Excel workbook.
- You do not have to map all of the records or items in your dictionary. However, if you map multiple records, then you need to map the same set of ID items for each mapped record.
You can save a specification file (with the extension .xl2cs) with the file and mapping selections. You can then load this file at a future time to avoid having to respecify all of the mappings.
To begin the conversion operation you must specify the input and output files and processing parameters:
- Select the Excel to CSPro tab.
- Click on Select Excel File and select the Excel spreadsheet that contains the input data.
- Click on Select CSPro Dictionary and select the dictionary that describes the desired output data.
- Click on Select Output Data File and provide a name for the output data file. If the file already exists, it will be overwritten during the conversion operation.
- Review the Starting row parameter. By default this is 2, which assumes that your data has headers. If it does not, change it to 1 or to the appropriate value. If working with multiple worksheets, the starting row for all worksheets must be the same.
- The Case management options allow you to control how to handle existing cases in a CSPro data file. The options are as follows:
- Create new file: The conversion will create a new CSPro data file that will contain only the cases from the Excel file.
- Modify, add cases: The conversion will keep the existing CSPro data file and add cases from the Excel file that are not in the CSPro data file. For cases that are in both the Excel file and the CSPro data file, the case will be modified only if the Excel case is different from the CSPro case. Cases that are in the CSPro data file but not in the Excel file will be kept.
- Modify, add, delete cases: This option is similar to the one described above except that cases that are in the CSPro data file but not in the Excel file will be deleted.
- If the Skip processing if the CSPro output data file is newer than the Excel file option is selected, then the conversion process will only occur if the Excel file has a newer file date than the CSPro data file. Selecting this is a way to speed up deployment conversions as the conversion will only occur when you change the Excel file.
For every worksheet containing data that you want to convert, you must specify to which CSPro record it should map. The tool displays all of the records in your dictionary and then, in a drop down list, displays the name of each of the Excel worksheets. Select which worksheet corresponds to the record, or if you do not want to map the record, select the <unassigned> option.
Once you have mapped a worksheet, there are two buttons that you can use to automate column mappings:
- Reset Mappings: For all of the record's items, remove any column mappings, setting each to <unassigned>.
- Assign Default Mappings: If each column in the worksheet corresponds to an item, with the first column mapping to the first ID item, and the last column mapping to the last item in the record, you can select this option, which will automatically map each item to its corresponding column.
You cannot map the same worksheet to multiple records.
After mapping a worksheet to a record, you will see a list of the record's items. In addition to seeing the item's label, name, and occurrence, there is a drop down list that shows the columns in the worksheet mapped to the item's parent record. Select which column corresponds to the item, or if you do not want a mapping for the item, select the <unassigned> option.
You can map the same column to multiple items.
When you have specified all the mappings, click on Create CSPro Data File to convert the Excel data to a file that can be opened using your CSPro dictionary.
There are some things to keep in mind when evaluating your converted data:
- The tool converts data in a way that ensures a valid CSPro file. That means, for example, that if a column is mapped to a numeric value and the cell does not contain a valid number, that the value output to the CSPro file will be default. Additionally, if an item's length cannot fully handle the cell's number or string, the value will be truncated.
- If you are working with data in multiple worksheets, you will want to sort your data by the specified IDs, or at least ensure that the IDs in each worksheet exist in the same order. The tool will report if the data was not in the correct order and in that case will not fully convert your data.