Add Spreadsheets as Data Sets

About Adding Spreadsheets or Other Data Files

You can create data sets from data stored in Microsoft Excel spreadsheets (XLSX and XLS ), CSV files, and TXT files. The maximum file size you can upload is 250 MB and the data column limit for a single file is 250 columns.

You must structure your Excel spreadsheets in a data-oriented way with no pivoted data. These rules apply 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 is repeated on every page of a printed report.
  • Row 1 must contain the names of the columns in the table. 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. If two columns 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 have two columns named Year Lease, then you can rename the columns to Year Lease Starts and Year Lease Expires.
  • Row 2 and greater must contain the data for the table, and those rows can't contain column names.
  • Data in a column must be of the same type because it's often processed together. For example, the Amount Purchased column must contain only numbers (and possibly nulls), enabling it to be summed or averaged. The Given Name and Surname columns must contain text values because they might be concatenated, and you might need to split dates into 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, suppose that you have a sales table at the granularity of Customer, Product, and Year that 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 daily summary values in the same table, because the sum of the Amount Purchased values wouldn't be calculated correctly. If you must analyze at the invoice level, the day level, and the 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 the day, 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 create a data set from an Excel spreadsheet (XLSX or XLS), CSV file, or TXT file located on your computer.

  • Confirm that the spreadsheet you want to use is either an Excel spreadsheet in .XLSX or .XLS format, a CSV file, or a TXT file.
  • For an Excel spreadsheet, ensure that it contains no pivoted data.
  • Understand how the spreadsheet needs to be structured for successful import.

Add a spreadsheet from your computer and use it as a data source:

  1. On the Home page, click Create, then click Data Set.
  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.
  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

You can upload Excel spreadsheets (XLSX or XLS), CSV files, and TXT files directly from Dropbox or Google Drive.

Before you start, you must set up a connection to Dropbox or Google Drive where your data files are stored. See Create Connections to Dropbox and Create Connections to Google Drive or Google Analytics.
  • Confirm that the spreadsheet you want to use is either an Excel spreadsheet in .XLSX or .XLS format, a CSV file, or a TXT file.
  • For an Excel spreadsheet, ensure that it contains no pivoted data.
  • Understand how the spreadsheet needs to be structured for successful import.
  1. In the Data page, click Create and click Data Set.
  2. In the Create Data Set dialog, click the connection to Dropbox or Google Drive.
  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.
  5. Click Add to create the data set.