If you have read the article Import of Flat Textfiles, you will remember that errors were contained in the file “Testimport.txt” in the “Importing Data Cells” section. Besides differing months, it also contained a description “Planned” as opposed to “Budget”, which was used in our Jedox model. This error was eliminated during the setup of the import.
In addition to such clearly recognizable deviations, there are also some errors that are not so obvious. For example, a data record would not be imported if the name of an element had additional blanks at the end. You can log such non-conforming data records using the PALO.ERROR_LOG function. This function especially makes sense for an extensive data import.
To use this function, open the file “Testimport.txt” with Excel from the directory C:\Users\Public\Documents\Jedox\Jedox Suite. An excerpt of this file will suffice for this particular example. Delete all data records after “Austria”, then change the name “Planned” to “Budget”, and save this file as “Testimport-short.txt” in the same directory.
Because the cube “Sales” has already been filled, create a new cube using the Cube Creation Wizard in the Modeler. Name it “Trial” and assign the same dimensions as for the “Sales” cube.
Start the import via the “Data Import Wizard”. Select “Testimport-short.txt” as your file, and select ‘”Tab” and leave “Header exists” unchecked. Double-click Next, and you will get the first data record. Abort the import.
From cell F3, select “Paste Data Function” dialog from the Query pulldown menu. Select the Market cube, “Guess Arguments” and “PALO.SETDATA”.
Correct the function in F3 to:
Please write “Market”, the name of the cube, in A4. Activate cell F5 and click “Insert/Function”, then select category “Palo” and afterwards the desired function PALO.ERROR_LOG().
When you click “OK”, you access the entry fields for this function. The parameters are:
- Error: the cell that contains the error, i.e. the cell with the “PALO.SETDATA” formula
- Path: the path to the log file and the file name (without detailed path, the error file will be stored in the folder of the import file).
- Value: the cell containing the error value, e.g. F1.
- Cube: the actual cube name, e.g. Market.
Important: Cell references as function arguments may not have quotes (e.g. if F3 receives quotes from the Excel wizard, they must be removed).
Tip: Since the error is written by the first argument “error” into the error log file and “value” and “cube” have the same behavior as the arguments of the coordinates, it would be advantageous to enter the first coordinate, A1, into the text box labeled “Value”.
The advantage of this procedure is that we will have records that are identical in structure to those of the original file “Testimport-short.txt”. It will then be possible to correct the errors in the error file. Consequently, we will be able to import the corrected error file with the same import procedure as before.
In this post-reading we can focus our view on the records, which have produced errors in the first import sequence. Records without errors will have been written successfully into the cube in their correct cells by the first import sequence. If we were to enter “value” and “cube” correctly as arguments, then we would have to change the columns in the error file to get the same structure as that of the original import file.
To get an optimal error message file for the remaining import, enter A1 for “Value” and B1 for “Cube” (in keeping with the tip above). Then enter the subsequent arguments (C1, D1, E1, and F1) as shown in the following screenshot:
We included F1 as a coordinate because we intend to use the error message file for the import of the remaining lines (after correction).
If all entries have been properly made, then the cell F5 contains the following function:
Important note: Setup of PALO.ERROR_LOG() is actually finished at this point. However, there is one more problem and it originates in Excel. In some Excel versions, the data record following the one containing the error will be logged. Consequently, the data record from April would be displayed in the “Error.txt” file. To prevent this, click the cell containing the “PALO.SETDATA” function (in this case F3) after having created of the PALO.ERROR_LOG Function. Close the cell by pressing “ENTER”. This time the correct error data record will be logged. Now restart Data Import Wizard, and click “Next” a few times.
You can see in the example below that a non-conforming data record is displayed in March (Mrz).
This data record was not processed, but instead rerouted to the “Error.txt” file assigned by you.
Verify by opening this file.
Now you can:
- Correct the non-confirming data records as described in the “Importing Data Cells” section with respect to months.
- Open the “Error.txt” file in Excel, and correct the spelling of the months. After you store the file again, you can carry out the import for file “Error.txt”, previously carried out for “Testimport-short.txt”. Because this file only contains the data records previously rejected, the end result must now correspond with the result expected in the first place.