Acquiring data from multiple Excel workbooks

When acquiring data from multiple Excel workbooks, the data format and data type must be the same in all of the workbooks.

  1. On the Home page, select Acquire Data.
  2. In the Add new dataset dialog, select Microsoft Excel, and select Next.
  3. Choose one or more Excel files, and select Open.
    Data from the Excel files is previewed in the Add new dataset dialog.
  4. (Optional) In the Dataset Name box, enter a name for the dataset.
  5. Beside Files(s), select Add Files, and browse to and select the Excel spreadsheet to acquire data from.
    You can use wild cards to search for a spreadsheet name. By default, the first file in the path is considered the reference file to which data will be appended from other spreadsheets acquired.
    For example, enter C:\data\monthly updates\*.xls(x) to find all .xls(x) files in the path.
  6. In the Sheet list, select a worksheet.
    This worksheet is the reference sheet that data from other worksheets will be appended to. The count of records is updated to reflect the number of records from all acquired data. A “Source file” column is added to the dataset, listing each data source name. If you selected the Append all sheets check box, all worksheets in the Excel spreadsheet are added to the dataset.
    Data from the worksheet appears in the preview pane of the Add new dataset dialog.
  7. (Optional) To display hidden worksheet rows or columns in the dataset, select Advanced Options.
  8. (Optional) To display hidden worksheet columns in acquired data, select the Show hidden columns check box, and enter the column range to display in the Range Selection list.
  9. (Optional) To display hidden worksheet rows in acquired data, select the Show hidden rows check box, and enter the row range to display in the Range Selection list.
  10. Select Create.
The data is acquired and appears in the Prepare room.