Automatic spreadsheet-content checking
The following points are among the leading causes of data exceptions when importing data from a spreadsheet:
- Invalid data formats
- Leading and trailing spaces in data cells
The Data Management spreadsheet use a macro to automatically check data formatting and to ensure that no leading and trailing spaces exist . Having the spreadsheets look for these types of inconsistencies greatly reduces the number of data exception errors that you encounter.
What the macro does
When you click Save to save the contents of a spreadsheet, the macro performs the following actions:
- Ensures that all cells in the spreadsheet are formatted as text.
- Prompts you with a question about:
- Removing leading and trailing spaces.
- Ensuring that cells containing only date and time values are saved using the correct data model.
If you answer Yes to the prompt, the macro goes through each cell in the spreadsheet and performs the following actions:
- Removes all of the leading and trailing spaces that it finds.
- Formats cells that contain date and time values according to the following data model: MM/DD/YYYY HH:MM:SS, where HH:MM:SS is 24 hour, or military time, format. For example: 6:15 p.m. expressed in 24 hour format is 18:15:00.
The process of replacing leading and trailing spaces and checking date and time formats can take a long time, depending on the volume of data in the spreadsheet and the number of updates that need to be made. For this reason, if you are confident that the data does not contain leading or trailing spaces and that date and time values are formatted correctly, you might choose to skip the step that checks for spaces.
If you choose to replace leading and trailing spaces, the macro highlights all of the cells that it changes, to give you a visual record of which cells the macro updated.
Spreadsheet file extension change
Because the spreadsheets are provided with macro enabled, the file extension is changed from .xlsx to .xlsm.
If you use the Export feature of the Data Management Onboarding wizard, the spreadsheets created by the Export function do not include this new macro, because the data that you export has already been validated.