Connecting to Excel

Note: This topic describes how to import data using the online Sisense Web Application. To import data through the desktop ElastiCube Manager, click here.

Sisense enables easy and quick access to tables contained within Excel spreadsheets.

There are two options for uploading data:

When you import multiple files from a folder and build your ElastiCube, you have the option to combine the data together in a single table. To build multiple tables with separate tables for each file, you must repeat the process for each file you want to import as a table. If you have any questions about data accumulation between builds, please contact Sisense Support.

Note: Click here to see tips on how to prepare your Excel files before adding them to your schema in the ElastiCube.

To import data from your Excel files:

  1. In the Data page, open an ElastiCube or create a new ElastiCube.
  2. In the ElastiCube, click . The Add Data dialog box is displayed.
  3. Click Microsoft Excel. The Microsoft Excel Connect area is displayed.
  4. Select the relevant option for importing your data:
    File Upload
    1. Select this option if the Excel file you want to upload is stored on your local machine. The data will be imported into the ElastiCube as it was at the time of the upload. If the file is updated later, you will need to upload it again.
    2. To upload the file, click Browse and navigate to the file to be uploaded or drag the Excel file to the File Upload area.

      The uploaded file is added to the Uploads list. You can delete the file by clicking and confirming you want to delete the file.
    3. Select the relevant Excel file(s) to be uploaded.
    4. (Optional) Toggle the Union Selected switch if you want to append several Excel files together when the data is imported into the ElastiCube. The data in the files must have the same column and data types, and the sheets you want to append must all have the same name. For details, see Appending Excel Files.
    5. After you have finished uploading the relevant Excel files, select them from the upload list and click Next. Continue to Step 5.

    Server Access

    1. Select this option to define the location of your files on the Sisense Server. It is advised to place your files on the Sisense Server if your Excel files frequently change, if your Excel files frequently change, as the data is taken from the Excel files each time the ElastiCube is built.
      Note: To use this option, you must have remote access to the server.
    2. Select Input Folder Path and enter the full file path with the file name where your Excel files are located. This will display each Excel file in the folder in the next screen where you select what tables to add to the ElastiCube.
      OR
      Select Input File Path and enter the full file path with the file name and its extension of your Excel file. For example, C:\Example.xlsx. This file display all the tables in the Excel file on the next screen where you select what tables to add to the ElastiCube.
    3. (Optional) Toggle the Union Selected switch if you want to append several Excel files together when the data is imported into the ElastiCube. The data in the files must have the same column and data types, and the sheets you want to append must all have the same name. For details, see Appending Excel Files.
    4. After you have finished defining the locations of your Excel files, click Next and continue to Step 5. A list of Excel files in the directory are displayed.
  5. From the Select Table list, click to select preview the columns in the Excel file and display the Settings, which provides more options for customizing your data.
  6. In the Settings area, define the following settings:
  1. After you have selected all the relevant tables, click Done. The tables are added to your schema in Sisense.

Appending Excel Files

When appending data from several Excel files together, the data in the files must have the same column structure and data types, and the sheets you want to append must all have the same name.

For example, the three files below have the same structure and data types, but different sheet names. Sisense cannot append the data in these files, because it does not recognize the files as identical.

However, if you rename the sheets so they all have the same name, Sisense recognizes these files as identical, and can successfully append the data in these files.

Out-of-Memory Issues

When building an ElastiCube with this connector, you might receive an "out of memory" error. To add more memory, see Troubleshooting Out-of-Memory Issues.

BaseURL and File Uploads

If you have implemented BaseURLs with Nginx, the maximum CSV or Excel file size you can upload is 1.5mb. To upload files larger than 1.5mb, you need to add the following line of code to your ngnix.conf file located on the Sisense Server.
Note: The location of the this file depends on where you installed Ngnix.

client_max_body_size 220M;

Where 200M is the size in megabytes that you want to allow.

This line should be added to the server object as shown below: 

Before After
location ~/reporting(/.*)$ {
set $path $1;
proxy_pass http://127.0.0.1:8081$path$is_args$args;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection "upgrade";
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header Host $host;
proxy_connect_timeout 6000;
proxy_send_timeout 6000;
proxy_read_timeout 6000; send_timeout 6000;
}
}
location ~/reporting(/.*)$ {
client_max_body_size 200M;
set $path $1;
proxy_pass http://127.0.0.1:8081$path$is_args$args;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection "upgrade";
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header Host $host;
proxy_connect_timeout 6000;
proxy_send_timeout 6000;
proxy_read_timeout 6000;
send_timeout 6000;
}
}