Import Excel Data

In addition to CSV and other formats, Cloud Dataprep by TRIFACTA® INC. can directly import Microsoft® Excel® workbooks and folders containing workbooks. The worksheets of a workbook can be imported as:

  • Individual datasets
  • A single dataset
  • A dataset with parameters

    NOTE: When importing as a parameterized dataset, all selected worksheets are imported into a single dataset.

Workbooks can be uploaded from your local system.

NOTE: Excel import is supported through file upload only.


Limitations:

  • Filepath and source row number information is not available from original Excel files. These references return values from the CSV files that have been converted on the backend. For more information, see Source Metadata References.
  • Source Excel files with cells bracketed by single double quotes may not be properly ingested if any terminating quotes are missing.

    Tip: You can check the data quality bars for mismatched values or, for strings, the data histogram bars for anomalous values to see if the above issue is present. If so, deselect Detect Structure on import. Then, use a Split rows transformation applied to the affected column to break up the column as needed.

  • You cannot import password-protected Excel files.
  • Compressed Excel files are not supported.

  • If loading your Excel-based dataset in the Transformer page results in a blank screen, please take a new sample. The file requires conversion again with each generated sampling.
  • Latest state of the Excel file may not be reflected in the Transformer page due to caching. When you run a job, the platform always collects the latest version of the data and converts it to CSV for execution.

Steps:

  1. In the menu bar, click Library.
  2. In the Library page, click Import Data to select and upload your Excel workbook as an imported dataset. See Import Data Page.

    Figure: Import Excel Workbook

    Tip: If you experience issues uploading XLS/XLSX files that are larger than 35MB, you can convert the files to CSV files and then upload them.

  3. To modify settings, click Edit Settings.

  4. To add datasets:

    1. Create one dataset with all workbooks: Click the Plus icon next to the top-level item (PointOfSaleData in the above image).
    2. Create individual datasets: Click the Plus icon next to each item you want to add as a separate dataset. The item is added to the right panel.
    3. You can always combine datasets together later using a union operation. See Union Page.
  5. After your datasets have been added, you can edit the name and description information for each in the right navigation panel.
  6. Optionally, you can assign the new dataset(s) to an existing flow or create a new one to contain them.