Excel Workbook

Requirements

Data Hub supports connections to Excel files in the .xlsx format. This format can be created by Excel version 2007 and later, and by Excel versions 97 - 2003 with the Office Compatibility Pack installed.

You can connect to a single Excel file as a data source. The file can be linked to via a Windows file share or HTTP or uploaded into the model.

Add the Excel data source

An Excel data source can be added to a new or existing model.

Select source

  1. Under Files & Other select Excel Workbook.

  2. From the source type pop-up, click Connect.

Connect

  1. Complete the following fields:

    1. File Location.Specify the location of the Excel file using one of the following methods:

      1. Windows File Share. Type the path of a .XLSX file in a network location that is accessible from the Data Hub web server (for example \\Server\SalesFolder\Sales.xlsx). The referenced file is reloaded each time the warehouse is processed.Windows File Share

      2. HTTP / HTTPS. Type a URL for downloading the Excel file using the HTTP protocol (for example http://excelfiles/Sales.xslx). The referenced file is reloaded each time the warehouse is processed.

      3. Upload Click the Upload File button to locate an Excel file on your local computer. The file name appears in the box. A static copy of the file is uploaded into the data model. The file is not reloaded automatically when the warehouse is processed.

    2. User Name (Optional). Specify a user name to connect to the specified file (if needed). If left blank, the Data Hub application pool service user's credentials are used. The following authentication types are available:

      1. Windows File Share

      2. Active Directory User

      3. Basic HTTP Authentication

    3. Password (Optional). Type a password for the user name specified above, if needed.

    4. Has Header. When selected, the spreadsheet table's first row is assumed to contain the column names and is not included as a data row in the pipeline. The number of rows specified in the Rows to Skip setting is skipped first to reach the start of the table, and then, if this setting is checked, the next row is skipped before starting to import the data.

    5. Culture. The culture determines how numbers and dates in the file are parsed as they are imported.

      • The culture includes settings for the decimal point character and for the order and format of day, month and year values within dates (for example, dd/mm/yy or mm/dd/yy).

      • For more information on available cultures and their settings, refer to: http://msdn.microsoft.com/en-au/goglobal/bb896001.

    6. Rows to Skip. Specify the number of initial rows to ignore. This option should be used if the table in the Excel spreadsheet does not start at the first row. The number of rows specified in this setting is skipped first to reach the start of the table, then, if the Has Headers setting is selected, the next row is skipped before starting to import the data.

      Note

      If columns must be skipped, you can unselect those columns in the pipeline's Initial step.

  2. Click Test Connection > Next.

Select data

  1. To add tables from the data source to the data model:

    1. Select tables to be added as pipelines using the check box column.

  2. To add the pipelines to the data model:

    1. Use Add Pipelines to add tables as individual pipelines.

    2. Select Add Pipelineas Union of Tables option from the Add Pipelines drop-down to add a single pipeline created from a union of the selected tables.

  3. Click Next.

Note

Adding pipelines can take some time.

In this section: