XLSTAT: Run a linear regression on millions of data points

 

Background

XLSTAT allows loading 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).

 

Dataset for Example

Data are fictitious and were created for this tutorial. The file contains only a very limited number of observations but you can repeat the below procedure using txt or cvs files containing millions of rows.

The goal of this tutorial is to show how to import a large txt file into Excel using the Import data tool of XLSTAT and how to run a linear regression on the imported data.

 

Setting Up

• Open XLSTAT.

• Click on the Modeling data menu and select the Regression feature.

• The Linear Regression dialog box then appears.

• To use a text file as data source, click on the mouse icon at the bottom of the dialog box until the orange paper sheet appears. A question mark appears then automatically next to the first data field.

• Click on the question mark next to the Quantitative dependent field to load the data file.

• The Import data file dialog box then appears.

• Set up the different parameters in order to read the file.

  • Delimiter defines the separator of each column (in this data file, TAB is used),

  • Text qualifier defines a complex element (i.e. an element with space, delimiter character, etc),

  • Start import at row field allows to skip the firsts lines used as header (here, none are skipped) and

  • Comment qualifier  field required for commented text in the file (here, for each line, all text after the character “#” isn’t loaded).

• Click on the Preview button to see a preview before loading the data file.

• Click on the OK button.

• Once the data file is imported, click into the first data field of the dialog box in order to select the Dependent variable.

• A new window pops up listing the imported variables. 

• Select the variable Y and click on the OK  button.

• In the same way, select the variables X11 and X21 in the Explanatory field.

• Once the variables to be included in the mode are selected, we can choose, in the other tabs, the appropriate options and output according to the needs of our analysis. 

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

To see how you can interpret the results of such an analysis using XLSTAT, check out our tutorials on multiple linear regression.