Add Spreadsheets as Data Sets

About Adding a Spreadsheet as a Data Set

Data source files from a Microsoft Excel spreadsheet file can have the XLSX extension (signifying a Microsoft Office Open XML Workbook file), and the XLS (signifying Excel spreadsheet format), and be no larger than 100 MB. You can also add CSV and TXT files.

Before you can upload a Microsoft Excel file as a data set, you must structure the file in a data-oriented way and it mustn‘t contain pivoted data. Note the following rules for Excel tables:

  • Tables must start in Row 1 and Column 1 of the Excel file.

  • Tables must have a regular layout with no gaps or inline headings. An example of an inline heading is one that repeats itself on every page of a printed report.

  • Row 1 must contain the table’s column names. For example, Customer Given Name, Customer Surname, Year, Product Name, Amount Purchased, and so on. In this example:

    • Column 1 has customer given names.

    • Column 2 has customer surnames.

    • Column 3 has year values.

    • Column 4 has product names.

    • Column 5 has the amount each customer purchased for the named product.

  • The names in Row 1 must be unique. Note that if there are two columns that hold year values, then you must add a second word to one or both of the column names to make them unique. For example, if you’ve two columns named Year Lease, then you can rename the columns to Year Lease Starts and Year Lease Expires.

  • Rows 2 onward are the data for the table, and they can’t contain column names.

  • Data in a column must be of the same kind because it’s often processed together. For example, Amount Purchased must have only numbers (and possibly nulls), enabling it to be summed or averaged. Given Name and Surname must be text as they might be concatenated, and you may need to split dates into their months, quarters, or years.

  • Data must be at the same granularity. A table can’t contain both aggregations and details for those aggregations. For example, if you’ve a sales table at the granularity of Customer, Product, and Year, and contains the sum of Amount Purchased for each Product by each Customer by Year. In this case, you wouldn’t include Invoice level details or a Daily Summary in the same table, as the sum of Amount Purchased wouldn’t be calculated correctly. If you’ve to analyze at invoice level, day level, and month level, then you can do either of the following:

    • Have a table of invoice details: Invoice Number, Invoice Date, Customer, Product, and Amount Purchased. You can roll these up to day or month or quarter.

    • Have multiple tables, one at each granular level (invoice, day, month, quarter, and year).

Add a Spreadsheet from Your Computer

You can upload an Excel spreadsheet, CSV file, or TXT file dta source located on your computer to use as a data set.

Before you add a spreadsheet as a data set, confirm you’ve done the following:

  • Confirm that you’ve either an Excel spreadsheet in .XLSX or XLS format, or a CSV, or TXT file as the data source used to create a data set.

  • For an Excel spreadsheet, ensure that it contains no pivoted data.

  • Understand how the spreadsheet needs to be structured for successful import.

Follow these steps to add a spreadsheet from your computer and use it as a data source:

  1. On the Home page, click Create, then click Data Set to display the Create Data Set dialog.
  2. Click File and browse to select a suitable (unpivoted) XLSX or XLS file, CSV file, or TXT file.
  3. Click Open to upload and open the selected spreadsheet in Data Visualization.
    The Data Set editor is displayed.
  4. Make any required changes to Name, Description, or to column attributes.
    If you’re uploading a CSV or TXT file, then in the Separated By field, confirm or change the delimiter. If needed, choose Custom and enter the character you want to use as the delimiter. In the CSV or TXT file, a custom delimiter must be one character. The following example uses a pipe (|) as a delimiter: Year|Product|Revenue|Quantity|Target Revenue| Target Quantity.
  5. Click Add to save your changes and create the data set.
  6. If a data set with the same name already exists:
    • Click Yes if you want to overwrite the existing data set.
    • Click No if you want to update the data set name.

Add a Spreadsheet from Dropbox or Google Drive

If you’re storing spreadsheets in Dropbox or Google Drive you can add a spreadsheet to create a data set.

Before you add a spreadsheet from Dropbox or Google Drive, do the following:

Use the following steps to add a spreadsheet.
  1. In the Data page, click Create and click Data Set.
    The Create Data Set dialog is displayed.
  2. In the Create Data Set dialog, click the connection to Dropbox or Google Drive.
    The Data Set editor is displayed.
  3. In the Data Set editor, search or browse the Dropbox or Google Drive directories and locate the spreadsheet that you want to use.
    You can use breadcrumbs to quickly move back through the directories.
  4. Double-click a spreadsheet to select it. When you select a spreadsheet, its columns and data values are displayed.
  5. Click Add to create the data set.