If you have data in an Excel spreadsheet that you want to use in CSPro but do not have a dictionary to describe this data, you can:
- Select the Create CSPro Dictionary from Excel File tab.
- Click on Select Excel File and select the Excel spreadsheet that contains the data.
- The tool will display the worksheets in the spreadsheet. Select the worksheet that contains the data that you want to convert. (The tool can only create a dictionary based on the contents of a single worksheet.)
- If your data does not have headers, change the Starting row to 1.
- Click on Analyze Worksheet.
The tool will read every cell of your worksheet and determine what CSPro format best describes each column. The tool will determine if the column is numeric or alpha, and if numeric what kind of decimal formatting is best for the column.
For columns that are determined to be numeric, the values of the column will also be stored, up to 500 per column, and these can be used to create a CSPro value set with the values used in the column.
Once the worksheet has been analyzed, the tool will display each column in the worksheet, along with its suggested format. You can change settings, including:
| Setting | Description |
|---|
| Include | If you want to exclude a column from the dictionary, uncheck this checkbox. |
| Name | This is the name of the dictionary item. If your Excel data has headers, the name will be based on the header, but you can change it to any valid CSPro name. |
| ID | If this item is used to uniquely identify the data, check this checkbox. You must specify at least one ID item for your dictionary, but can also specify more than one. |
| Numeric | Check this checkbox if the data in the Excel column is numeric. You can override the Analyze Worksheet suggestion. |
| Alpha Length | If the item is alpha, this is the length of the item. |
| Integer Length | If the item is numeric, this is the integer length of the item. (This differs from how Length is used in the CSPro dictionary editor.) |
| Decimal Length | If the item is numeric, this is the decimal length of the item. If the data in the Excel column is all integers, this should be 0. |
| Create Value Set | If the Excel column contained numeric data with fewer than 500 unique values, you can create a value set with each value found in the data. The value labels will appear as Value 1, Value 2, etc., so you may want to edit them later in the CSPro dictionary editor. |
After you have mapped columns to CSPro items, you are ready to create the CSPro dictionary. The tool will determine whether your settings are valid, checking that:
- At least one item is specified as an ID item.
- The length of numeric items is consistent with CSPro dictionary guidelines.
- The names of items do not use reserved CSPro words.
You can change some settings related to the dictionary creation:
| Setting | Description |
|---|
| Name prefix | This name will be used as a prefix when determining the names of the dictionary, level, and record. For example, PERSON would lead to PERSON_DICT, PERSON_LEVEL, and PERSON_REC. |
| Zero fill numerics | Numeric values will be zero-filled when converting Excel data to CSPro data (or when collecting new data in CSPro). (For example, 5 might be stored as 005.) |
| Use explicit decimal character | Numeric values will be saved to the CSPro file with a decimal mark. (For example, 3.14 can be stored as either 314 or 3.14.) |
After modifying the settings, click on Create Dictionary. The tool will validate your selections and then ask you for the file name of the new CSPro dictionary.
If you want to immediately convert your Excel data to CSPro format, click on the
Excel to CSPro tab. The tool does not remember the mappings so you will have to
remap your new dictionary back to Excel.