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
- 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.
- On the Home page, click Create, and then click Data Set.
- Click File and browse to select an XLSX or XLS (with unpivoted data), CSV, or TXT file.
- Click Open to upload and open the selected spreadsheet.
- Make any required name, description, or column attribute changes.
- If you’re uploading a CSV or TXT file, then in the Separated By, Thousand Separator, and Decimal Separator fields, confirm or change the default delimiters.If needed, choose Custom in the Separated By field 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.
- Click Add to create the data set.
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.
- 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.
- In the Data page, click Create and click Data Set.
- In the Create Data Set dialog, click the connection to Dropbox or Google Drive.
- 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.
- Double-click a spreadsheet to select it.
- Click Add to create the data set.
Index File-Based Data Sets
You can specify if and how to index data sets created from uploaded CSV or TXT files. You won't be able to search for and find uploaded data or use the uploaded data to create visualizations from the home page until its been indexed.
- In the Home Page, click Navigator and select Data.
- Locate the data set and click its Actions menu button and click Inspect.The Inspect Data Set dialog is displayed.
- Click the Search tab.
- Click Index Data Set for Searching field and set the options for how and when you want the data set to be indexed.
- If you want other users to be able to search for and access the data in the data set, then in the Inspect Data Set dialog, click the Access tab.
- Confirm that the administrator is granted full control.
- Use the Users and Roles tabs to add users and roles and set data access permissions as needed.
- Click Save.
- Ask your administrator to certify your data set.The data set is indexed and available for searches after your administrator certifies it.