Add a Spreadsheet as a Data Source

You can add a spreadsheet as a data source. You can browse for and upload spreadsheets from a variety of places, such as your computer, Google Drive, and Dropbox.

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.

You can't import an Excel spreadsheet that contains pivoted data. See About Adding Spreadsheets or Other Data Files.
  1. On the Home page, click Create, and then click Data Set.
  2. Click File and browse to select an XLSX or XLS (with unpivoted data), CSV, or TXT file.
  3. Click Open to upload and open the selected spreadsheet.
  4. Make any required name, description, or column attribute changes.
  5. 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.
  6. Click Add to create the data set.

Add a Spreadsheet from Excel with the Smart View Plug-In

The Oracle Smart View Plug-In enables you to publish an XLSX or XLS spreadsheet, a CSV, or TXT file from Excel and use it as a data source.

When you import the spreadsheet and before you add it as a data source, you can modify column attributes, like data type and whether to treat the data as a measure or attribute.

Before you use the Smart View Plug-In, confirm you’ve done the following:

  • Installed the latest version of Oracle Smart View for Office. To find the download, go to Oracle Smart View for Office. After you install Oracle Smart View for Office, be sure to restart all Microsoft Office applications.

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

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

Follow these steps to publish an Excel spreadsheet, CSV, or TXT file to use it as a data source:
  1. Open your Excel (.XLSX or XLS) spreadsheet, CSV, or TXT file in Microsoft Excel.
    If you're opening a .TXT file, follow the import steps for example, to specify the delimiter.
  2. Click the DV Desktop tab.
  3. If you’re publishing a .XLSX or XLS file with pivot data, follow these steps:
    1. Select the upper-left numeric data cell, or select an area of data cells that you want to publish.
      Don't include grand totals when you select an area of data cells to publish.
    2. Click Unpivot.
    3. Click OK.
  4. If required, format the new sheet content in Excel (for example, edit column heading names).
  5. In the DV Desktop tab, click Publish to publish the new sheet.
    If Oracle Analytics Desktop isn't running, it starts automatically. The spreadsheet data is displayed in the Data Set editor.
  6. In the Data Set editor, 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, 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.
  7. Click Add. If a data set exists with the same name, you're prompted to confirm that you want to overwrite it.
    You can update, re-pivot, or apply changes to the data set as needed.

    If you delete the Excel file created when un-pivoting, then the data set is no longer linked to the Excel file.

Add a Spreadsheet from Windows Explorer

You can add a spreadsheet as a data source from within Windows Explorer.

Before you add a spreadsheet as a data source, do the following:

  • Install the latest version of Oracle Smart View for Office. To find the download, go to Oracle Smart View for Office. After you install Oracle Smart View for Office, be sure to restart all Microsoft Office applications.

  • Confirm that you're working with either an Excel spreadsheet in .XLSX or .XLS format or a .CSV file to use as the data source.

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

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

  1. Open Windows Explorer and navigate to the spreadsheet file (.XLSX, .XLS, or .CSV) that you want to use as a data source.
  2. Right-click the spreadsheet file icon.
  3. Click Open with from the menu.
  4. Select Oracle Analytics Desktop.
  5. If a data set with the same name already exists, the Create or Reload Data Set window is displayed.
    • Click Reload and click OK to overwrite the existing data set with the same name.

      If you choose to reload, you don’t need to follow the final step, and the new data set overwrites the existing data set.

    • Click Create New, and complete one of the following options:
      • Enter a new name, and click OK.

      • To save using an autogenerated data set name, click OK.

  6. In the Data Set editor make any required changes to the Name, Description, or to column attributes.
    If you’re uploading a CSV 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. For CSV files, a custom delimiter must be one character. The following example uses a pipe (|) as a delimiter: Year|Product|Revenue|Quantity|Target Revenue| Target Quantity.
  7. Click Add.

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.