Loading variables from Excel into GAUSS

This tutorial will explain how to load variables from an Excel® spreadsheet into your GAUSS workspace and retain the variable names from the spreadsheet. In addition to step-by-step instruction, source code for procedures that will automate this process is also included. This is a high-level tutorial that will explain the usage of supplied convenience procedures, rather than going through the details of creating this “from the ground up.”

Loading data and assigning variable names

First download the tutorial zip file, xl_load_tutorial. It contains:

1. load_test.xlsx - an Excel® data file with four variables.
2. load_test.src - a GAUSS file containing the procedures we will need for this tutorial
3. load_test.dec - a GAUSS declaration file

Place these files in the correct location by installing xl_load_tutorial.zip with the GAUSS application installer if you are running GAUSS 13. You can do this by selecting "Tools
->Install application" from the main application menu and then follow the install wizard's instructions. For older versions extract the ZIP file into your GAUSS home directory.

Adding the procedures to your 'user' library

After you have installed the contents of the ZIP file, the next step is to add the new procedures contained in load_test.src to your user library. Adding these procedures to your user library ensure that they are always available for you to use—you will never get an “undefined symbol” error when trying to use them. You will also be able view the procedures and navigate to them by clicking in the Library Tool on the Source Page.

Create a user library if it does not exist

If you do not see the 'user' library in your Library Tool, create it by clicking the '+' icon next to “Create Library” and follow the instructions.

Adding the source file to the library

To add the tutorial source file, load_test.src to your 'user' library:

1. Click the wrench icon next to “user” in the Library tool to bring up the menu:
   
   

   Library Tool wrench icon

2. Select “Add Files” from the menu
   
   

   Adding files to the user library

3. Browse and select the file load_test.src, which should be in your GAUSSHOME/src directory. Finish by clicking the "Open" button at the bottom of the file dialog.

Running xlsLoadVars

Now we are ready to load the variables from the sample file into our GAUSS workspace. The file load_test.xlsx should be in your GAUSS home directory. Look at the current directory toolbar widget to make sure your current working directory is GAUSS home.
From the GAUSS home directory we can load the variables from the file with this statement:

xlsLoadVars("load_test.xlsx");

To verify that the variables are loaded, we can click the data tab and view the list of matrices in the workspace.

Creating a declaration file

At this point, the four variables Dates, GDP, Oil and TBill are available for use in any GAUSS program. However, when we clear our workspace with the new command, they will not be available until after we run the xlsLoadVars command again. In fact this code below will return the error, “Undefined symbols”.

//Load the variables: Dates, GDP, Oil and TBill
xlsLoadVars("load_test.xlsx");

newGDP = GDP;

The reason is that the variable GDP will not be assigned by xlsLoadVars until run-time. However, at compile time--when GAUSS turns your program text into something it can execute--GAUSS needs to know if GDP will exist and what type of variable it is. The most robust method of avoiding this "undefined symbols" error is to add a declaration file to your 'user' library. A declaration file is a file that initializes variables at compile time. For this tutorial project, our declaration file will look like this:

declare Dates ?= 0;
declare GDP ?= 0;
declare Oil ?= 0;
declare Tbill ?= 0;

The '?=' tells GAUSS that if the symbol does not already exist, then initialize the symbol with a value of zero. An example declaration file for this tutorial, load_test.dec should be located in your GAUSSHOME/src directory.

Add the declaration file to your user library

For GAUSS to use a declaration file, it must either be run before the variables are referenced or it must be in a loaded library. You could ensure that the file is run before the variables are referenced by adding a statement like:

#include my_declaration_file.dec

to the top of your program file, or you could simply add it to your 'user' library. Once a declaration file is added to your 'user' library, the variables it declares will always be available with no extra steps on your part. Therefore adding the declaration file to your 'user' library is the preferred solution. Add the declaration file to your user library using the same steps that we used to add load_test.src.

Automating declaration file creation

This tutorial came with a declaration file. However, you will need to create them for your future projects. For your convenience, load_test.src also contains the procedure createDecFile. You can use this procedure to create a declaration file for another set of variables inside of an Excel file using these steps,

1. Load the variables names into a string array with the GAUSS function xlsReadSA.
2. Call createDecFile, passing in the string array list of variable names and the name for the declaration file.

For example, if you were starting a new project using a data file named my_new_project.xlsx which has four variables, then your code would look like this:

//Load the string column names from cells A1-D1
myVars = xlsReadSA("my_new_project.xlsx", "A1:D1", 1, "");

//Create a declaration file for the variables
createDecFile(myVars, "my_new_project.dec");