Loading large CSV or text files into Excel

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).

Datasets

A sales manager keeps two daily records. 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).

Goal of this tutorial

The sales manager wants to define the total invoice amount by each store. The first step would be to merge the two tables then compute the invoice sum by store. Let's suppose that the data records exceed Excel's row limits so the manager will need to load the data into the computer’s memory using XLSTAT rather than in the worksheet.

Setting up the import data file dialog box

Step 1: Join two tables

Once XLSTAT is activated, select XLSTAT / Preparing data / Data management.

The Data management dialog box appears:

In the Method field, choose Join (Inner). In the fields Table 1 and Table 2, import the two data files.
To use a text file as data source, click on the mouse icon until the orange paper sheet appears. A question mark appear next to each Table field.

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 Data management feature. 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.

Conclusion

In this tutorial, we showed how to import two data tables into the computer's memory using an alternative importation XLSTAT tool. This comes very handy in case your data files are too big and they exceed Excel's limitations. We then merged the two tables using the Data management feature.