Excel

This guide provides step-by-step instructions for connecting Excel files to Unistream.

Prerequisites

You will need:

• Excel files (.xlsx format)

• Files prepared with proper structure (see best practices below)

• For SFTP connection: SFTP server credentials

• Unistream platform credentials

---

Step 1: Prepare Your Excel Files

Before connecting Excel files to Unistream, ensure your files are properly formatted.

1.1 File Format

• Supported Format: .xlsx (Excel 2007 and later)

• File Size: Keep files reasonably sized for optimal performance

• Sheets: Multiple sheets are supported

1.2 Data Structure Best Practices

• Headers: Use the first row for column headers

• Consistent Format: Keep data types consistent within each column

• No Merged Cells: Avoid merged cells in data areas

• No Empty Rows: Remove empty rows between data

• Date Format: Use consistent date formats

• No Special Characters: Avoid special characters in column names

1.3 Example: Well-Structured Excel File

| Customer_ID | Customer_Name | Order_Date | Amount |
|-------------|---------------|------------|--------|
| 1001        | John Doe      | 2024-01-15 | 500.00 |
| 1002        | Jane Smith    | 2024-01-16 | 750.50 |
| 1003        | Bob Johnson   | 2024-01-17 | 320.25 |

---

Step 2: Connect to Unistream

2.1 Add Excel Source

1. In Unistream, navigate to Lake

2. Click Add Source

3. Select Excel

2.2 Configure General Fields

Fill in the General Fields:

• Name: Enter a descriptive name for your connection (e.g., "Sales Data Excel")

• Code: Enter a unique code identifier (e.g., "sales_excel")

2.3 Choose Connection Type

Select your preferred connection method:

Option A: Upload Excel (Direct Upload)

1. Select Upload Excel radio button

2. Choose Processing Type:

   • Process together: All uploaded files will be processed together

   • Process one by one: Each file will be processed individually

3. Upload your Excel file(s):

   • Click the File Uploader area

   • Or drag and drop your .xlsx file(s) into the upload area

Option B: SFTP (Remote Server)

1. Select SFTP radio button

2. Configure SFTP connection details:

   • Host: SFTP server address

   • Port: SFTP port (typically 22)

   • Username: SFTP username

   • Password: SFTP password

   • Remote Directory: Path to Excel files on SFTP server

2.4 Configure Table Processing Method

Choose how Unistream should handle multiple sheets and files:

Processing Method Options:

1. Each file's sheet as separate table

• Creates individual tables for each sheet in every file

• Best for: Different data types in each sheet

• Result: If you have 2 files with 3 sheets each, you get 6 tables

2. Union sheets inside file

• Combines all sheets within each file into a single table per file

• Best for: Same data structure across all sheets in a file

• Result: If you have 2 files with 3 sheets each, you get 2 tables

3. Union tables by sheet names

• Combines sheets with matching names across all files

• Best for: Multiple files with same sheet structure (e.g., monthly reports)

• Result: All "Sales" sheets from different files combine into one table

4. Union all sheets and tables

• Combines all sheets from all files into a single table

• Best for: All data has the same structure across all files and sheets

• Result: Everything becomes one large table

2.5 Additional Configuration Options

Configure these optional settings based on your needs:

• Transpose table data: Check to switch rows and columns in the resulting table

• Convert time format: Check to convert datetime header values to time format when possible

• Skip table validation: Check to skip automatic table structure validation

• First row is header: Check to use the first row as column headers (recommended)

2.6 Test and Save

1. Review your configuration

2. Click Add to save your connection

3. Unistream will process the Excel file(s) according to your configuration

Need Help?

If you encounter any issues not covered in this guide, please contact Unistream support