Skip to main content
XLSTAT is joining the Lumivero family. Learn more.

How to load large CSV or text files

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

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.

XLSTAT Data preparation menu
The Data management dialog box appears:

XLSTAT Data management dialog box
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.

XLSTAT Data management dialog box
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.

Importation preview in XLSTAT
To see a preview before loading the data file, click on the Preview button:

Data selection in XLSTAT
You can now select the variables in the dialog box.

Data selection in XLSTAT
Repeat the same process with Table 2. Click OK to display the results in a new worksheet named Join (Inner).
Joined table in XLSTAT

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.

XLSTAT dialog box for grouping
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.
Grouped data table in XLSTAT

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.

Was this article useful?

  • Yes
  • No
Home

A complete statistical add-in for Microsoft Excel