Textfiles provide one of the simplest ways to import and manipulate data. You can use corresponding data files for import of data, for update of data, or for creating, deleting, and renaming elements.
|We work with the database “Demo” and create a new cube “Market” for our imports, with the dimensions as shown to the right:|
The data to be imported are from the years “2002” and “2003”. Please add these elements to the dimension “Years”.
Importing data cells
Jedox comes with the file “Testimport.txt”, which contains approx. 31,000 data records. The records appear as follows:
This document is the result of a typical data export from a database containing targeted and actual sales figures from various countries and years.
Open a blank Excel worksheet, and click “Import Wizard” in the Jedox Ribbon. The Data Import Wizard appears:
You have four options for the data import:
- Flat Textfile: imports a file in TXT or CSV format. The data are separated by semicolon or a tab.
- ODBC Query: imports data from an ODBC data source.
- Cube: imports data from another cube.
- Internal Loop: processes a list in a worksheet via a loop.
Let’s start with the text file. Click Next.
You are asked for the data source. Click “Browse” to select testimport.txt. In Windows 10, this file can usually be found in C:\Users\Public\Documents\Jedox\Jedox Suite.
Depending on the file, you can select local code page or UTF-8 characters.
Enter the separator character. In this example, the columns are separated by tabs. There are no decimal points or header lines in the test data, and so the corresponding options can remain blank.
Click Next to access the following screen.
Click Next again and then Close. Then you see the following screen in the Excel worksheet:
This is the first data record of our text file.
If you clicked the Finish button instead of Next or Close, all data records would be written into the first row of our Excel worksheet. The worksheet would be recalculated after each data record, and the process would be repeated until the last data record.
However, in this case, we do not want to have the data in an Excel worksheet; we want to transfer the data into a Jedox cube. Consequently, we must create a formula that will transfer the contents of the first data record or first Excel row into the Jedox cube.
With the function PALO.SETDATA(), Jedox can use the entries in line 1 to guess the arguments of this function.
Click in a row below the first row; in D4, for example. It is important that the cell is in a column between the first and last field of the imported data record. Now click “Paste Data Function” in the Jedox Ribbon. Select the database, the “Guess Arguments” cube, and then select the function from the drop-down list:
When you then press F2, you see the formula:
Most arguments in the formula have been identified correctly: We must correct the remainder manually:
The cell F1 contains the value to be written.
With “TRUE”, writing the value into a consolidated cell is permitted. With “FALSE”, values may be written only into base elements.
In addition, “All Datatypes” is not applicable. ” All Datatypes” is a consolidated element that shows the difference between “Budget” and “Actual”. Therefore, E1 contains a different name for “Budget”. We must correct this.
You might wonder why there is an element with the name “Planned” in the import file.
We have set it up this way to show you how the DATAC function works when there are discrepancies between old and new names. Such discrepancies can occur, for example, when you import data from other data sources where other names are used than in Jedox cubes. Perhaps you do not want to import the other name “Planned” into the cube, but instead use your name “Budget”.
To achieve this, define an import rule: If E1 = “Planned”, then select the element “Budget”, otherwise = E1.
Write the following formula in E2:
With this formula, we instruct the PALO.SETDATA function to use “Budget” instead of “Planned”.
Then change the DATAC function in D4 by replacing the incorrect entry ” All Datatypes” with a reference to E2:
Then select the “Data Import Wizard” again, and test whether the formula is processed correctly for the remaining data records. Your previous settings still exist. Double-click “Next”. You see the import window again. Then click “Next” a few times and observe how the formula changes in cell D4.
After a few data records, you get this message:
Again, the PALO.SETDATAC function does not retrieve a cube cell with the coordinates it has. Close the wizard, and right-click in cell D4 to display the following context menu:
Select “Jedox Error” to display the error message:
Examine the elements in the dimension “Months” and you will see that the English term “Mar” is used instead of the German abbreviation “Mrz” in the database.
Consequently, we must provide a translation.
See the screen below:
Now change the PALO.SETDATA function in cell D4 again. Replace $D$1 with $D$2 to use the English abbreviations of the months.
Then select the Data Import Wizard again, and check whether the formula is being processed correctly.
Tip: in a few installations, the value in F1 is not recognized as a number. This also applies if the field is otherwise formatted correctly. In this case, Jedox outputs an error message (e.g., “Cannot write text in a numeric cell”). To get around this, enter the formula “=Value(F1)” in cell G1, and change the PALO.SETDATA function as follows:
Save the worksheet and start Data Import again. To be sure everything is correct, step through a number of record imports by clicking “Next”. If the write is performed properly, PALO.SETDATA() will return the value that it has written into the cube.
By using the Import Wizard, data can be auto-formatted during the import process. This happens by using the button “Next”. The format of a cell is applied when pressing “Next” while it is ignored with “Finish”. This auto-formatting of cell A1 changes the imported values to the regional settings (e.g. date, currency, number format) that can further lead to problems in depending formulas. To avoid the formatting feature, set the cell format to the format of the source or to “Text “.The data will be properly written to the cube if the value in cell D4 (PALO.SETDATA function) always equals the value in cell F1 and cell G1, respectively.
Once you are sure that no further problems are to be expected, click Finish in the Data Import Wizard. If you remove the check mark next to “Refreshing screen while finishing”, the import process will be a little faster.
To see whether a data import is currently in progress, you can use the variable _paloimportactive. You can find it in the Excel in defined names as soon as the first import has started.
Note: this information can protect you from writing data with the PALO.SETDATA function inadvertently into the cube. This function is executed every time you click in the formula bar and confirm this function with Enter, or when you press F9.
Use the following formula in D4:
In this form, the PALO.SETDATA function is only executed when the Data Import Wizard runs.
We insert this name into the cell G4. You get the value TRUE during import.
Once the import has been completed, the display changes to FALSE.
An indication of this state may also be necessary for some projects when specific PALO functions are allowed to start only after the state has changed to TRUE.
For comparison, check the last data record after the import has been completed:
With “Paste View” you can check that this record has been imported into the cube.
As a further example, you should update the market data. For this, you will read the current values from the cube with PALO.DATA(), then add the values from the file “updateimport.txt” to them
(path: C:\Users\Public\Documents\Jedox\Jedox Suite\updateimport.txt).
G4 contains “_paloimportactive ” and the function in D4 is:
The following screenshot shows the last record of the file “updateimport.txt”.
B4 contains the current value of the corresponding cube cell: =3024 + 3000: