The Excel Import option allows you to import data from a Microsoft® Excel file in order to update specific segment information. (The file should be in .XLS or .XLXS format. The .CSV format is not supported.)
To execute a Prospero Excel Import process:
1. Select the Excel Import option from the Data Integration Navigation Pane to open the Import workspace.
2. Enter the fields and options necessary for your Integration job. Fields and options on the workspace include the following:
•Batch Name – This option allows you to specify a name for the Excel import process up to 24 characters. You must enter an Import name for the Import button to activate. This is a required field.
•File Name – This option allows you to browse to select the file
which will contain the specific Excel data with which to update your segment.
Once you provide a file, you will be prompted to select the Excel worksheet tab
within the workbook.
If a <BLANK> worksheet is specified, you will receive an error message: "No importable data found in specified worksheet. Please select another worksheet." Otherwise, a sample of the data to be imported will appear in the Preview pane.
The File Name field is a required field.
•Specify Book Code to use on all data – This checkbox option activates an ellipsis button allowing you to select from the valid book codes defined in the Prospero system. Only data associated to this book code will then be imported.
•Specify Year to use on all data – This checkbox option allows you to enter the year value which will be applied to any data imported in using the process. You can enter up to 4 characters in the year field.
•Specify Currency to use for all data – This checkbox option allows you to select the currency code to be applied to all data imported in using the process. You can select the currency code from valid options specified in your system from the dropdown.
In addition, you can specify the currency via the option in the preview grid. This allows you to define your data’s currency code from the import file (and you don’t need to specify this yourself.)
When this option is specified, when you upload your file, the Excel Import process will verify that the currency code in the file is active in the currency list for the company. For more information see Currency Definition.
Note: While selecting the currency to use is optional and when not specified - or if currency is not used - the process will use the default type. In this case, the currency code must be set on the import process or Excel import file and cannot be <BLANK>.
•Period option – This option allows you to specify the Period value which will be applied to any data imported in using the process. Three options are available:
o Periods in Rows – This option indicates that period values will be applied in rows for the imported data.
o Periods in Columns – This option indicates that period values will be applied in columns for the imported data.
o Specify Period – This option indicates that period values will be applied starting in the field “Period to use for all data”.
•Number of Periods – This option allows you to specify the number of periods for the columns to be imported. You can enter up to 3 characters in the Period field.
•Period to use for all data – When the “Specify Period” option is selected in the Period option field, you must specify the period to use for all imported data. You can enter up to 3 characters in the Period field.
•Reverse signs in imported data – This option allows accounts types typically stored with credit balances (i.e. revenue accounts) to have the sign reversed before posting. As a result, data imported from the spreadsheet showing as "4000" will then post in to the database as "-4000".
Do not check this box if the data in the spreadsheet already shows the natural sign.
•Remove successfully posted items from import batch - This checkbox option allows you to remove successfully posted items from the batch. Where large amounts of data are processed, this option will limit the amount of time and data required as part of any re-post process.
3. The Import Preview pane will provide a snapshot of the data which is to be imported.
4. Once you have made your value specifications, select the Import button to complete your import.
NOTE: Each field name you specify in your import file must be unique. If you duplicate the field name for whatever reason, you will receive an error message when you attempt your import process and the import will not proceed.
In addition, if you import an Excel file and errors occur during the posting process, you will receive a message that records have failed the validation process and were not posted: “Import completed with validation errors. Please check the import batch for details”.