This tutorial shows how to load text and csv data files into Excel and then analyze them using the XLSTAT feature Data management.
This import method loads data into the computer’s memory rather than the worksheet. Therefore, we are able to load data files which exceed the standard Excel worksheet size (1,048,576 rows by 16,384 columns in Excel 2016).
A sales manager records daily two files. The first one contains the product IDs and the invoices (table 1) while the second one includes the order ids of each store (table 2).
Click here to download Table 1
Click here to download Table 2
Click here to download the output file.
Goal of this tutorial
The sales manager wants to define the total invoice amount by each store. To this purpose, he doesn’t want to load the data in the Excel worksheet but directly work into memory. First, we will join the two files to match each invoice with the right store. Then, we will compute the invoice sum by store.
Setting up the import data file dialog box
Step 1: Join two tables
Once XLSTAT is activated, select the XLSTAT/Preparing data/Data management command.
The dialog box appears:
In the Method field, choose Join (Inner). Two new fields appear: Table 1 and Table 2.
To use a text file as data source, click on the mouse icon until the orange paper sheet appears (figure 4). A question mark appear next to each Table field (circled button figure 4).
To load the first file, click on the question mark next to Table 1. The Import data file dialog box appears (figure below). Set up the parameters to read demoDMAJoin_Order.txt. These parameters are: a) the Delimiter which defines the separator of each column (in this data file, TAB is used), b) the Text qualifier which defines a complex element (i.e. an element with space, delimiter character, etc), c) the Start import at row field allowing to skip the firsts lines used as header (here, none are skipped) and d) the Comment qualifier field necessary for commented text in the file (here, for each line, all text after the character “#” isn’t loaded).
Click on OK button to save the parameters.
To see a preview before loading the data file, click on the Preview button:
You can now select the variables in the dialog box.
Repeat the same process with Table 2.
Click OK to display the results in a new worksheet named Join (Inner).
Step 2: Aggregating (grouping) data
The next step is to compute the total invoice by store. Select again the XLSTAT/Preparing data/Data management command. Click once on the orange paper sheet to switch into the mouse selection mode and select the Group method. Click on the Data field and select the Invoice and Store columns. Finally, select the grouping key Store.
The results of the data aggregation are displayed in a new worksheet named Group. The total invoices are 109 € for store A, 97 € for store B and 27 € for store C.