Excel Load Block

The Excel Load block allows you to read and load data from Excel files (XLSX, XLS) into your automation process. This block extracts data from a specific sheet and stores it in a list for further processing.

Overview

This block is essential for processes that need to read information from Excel files, such as:

• Processing customer lists
• Importing billing data
• Reading inventories
• Loading master data
• Extracting reports

Common use case

The Excel Load block is ideal for starting automation processes that handle bulk data from spreadsheets, allowing you to iterate over each row in the file.

Parameters

1. File path (file_path)

Variable name: file_path Allowed types: secret, data

Full or relative path to the Excel file to be read.

Description: Specifies the location of the Excel file on your local system. Can be an absolute or relative path to the robot’s working directory.

Examples:

# Absolute path (Windows)
file_path = "C:\\Users\\username\\Documents\\invoices.xlsx"

# Absolute path (Linux/Mac)
file_path = "/home/username/documents/invoices.xlsx"

# Relative path
file_path = "./data/customers.xlsx"
file_path = "input/orders.xlsx"

Important

On Windows, use double backslashes (\\) or forward slashes (/) in paths. Avoid single backslashes which can cause errors.

Considerations:

• File must exist and be accessible
• Robot must have read permissions on the file
• Supported formats: .xlsx, .xls, .xlsm
• Can be stored in data or as secret if it contains sensitive information

---

2. Excel sheet name (sheet)

Variable name: sheet Allowed types: data

Name of the Excel sheet from which data will be extracted.

Description: Specifies which sheet in the Excel file should be read. The name must match exactly with the sheet name in the file.

Examples:

# Common sheet names
sheet = "Hoja1"
sheet = "Sheet1"

# Custom names
sheet = "Customers"
sheet = "Invoices_January"
sheet = "Data_2024"

Considerations:

• Name is case-sensitive
• Don’t include leading or trailing spaces
• If the name contains spaces, use the exact name: "My Data"
• If sheet doesn’t exist, block will generate an error

Tip

If you don’t know the exact sheet name, open the Excel file and check the name in the bottom tab. You can also use the default name “Sheet1” or “Hoja1” if it hasn’t been renamed.

---

3. Data columns (column_keys)

Variable name: column_keys Allowed types: list

List with the names of columns to extract from the Excel file.

Description: Defines which columns from the Excel file will be read and stored. Only specified columns will be extracted, ignoring the rest.

Format: List of strings with exact column names (first row of Excel).

Examples:

# Basic example - invoices
column_keys = ["InvoiceNumber", "Customer", "Amount", "Date"]

# Example - customers
column_keys = ["Name", "Email", "Phone", "Company"]

# Example - products
column_keys = ["SKU", "Description", "Price", "Stock", "Category"]

# Example - employees
column_keys = ["Employee_ID", "Full_Name", "Department", "Salary"]

Considerations:

• Names must match exactly with column headers in Excel (first row)
• Case-sensitive
• If a column doesn’t exist, an error will be generated
• You can specify columns in any order
• Specified order will be maintained in loaded data

Best practices

• Use descriptive column names without spaces in your Excel files
• Avoid special characters in column names
• If your columns have spaces, use them exactly: ["Full Name", "Birth Date"]

---

4. Result list (result_list)

Variable name: result_list Allowed types: list

List where extracted data from the Excel file will be stored.

Description: Output variable that will contain all read records. Each Excel row becomes a dictionary within the list.

Output format:

# Structure of result_list
result_list = [
    {
        "ColumnName1": "value1",
        "ColumnName2": "value2",
        "ColumnName3": "value3"
    },
    {
        "ColumnName1": "value4",
        "ColumnName2": "value5",
        "ColumnName3": "value6"
    }
    # ... more rows
]

Real example - Invoices:

# Excel file with columns: InvoiceNumber, Customer, Amount, Date
result_list = [
    {
        "InvoiceNumber": "F-2024-001",
        "Customer": "Company ABC",
        "Amount": "1500.00",
        "Date": "2024-01-15"
    },
    {
        "InvoiceNumber": "F-2024-002",
        "Customer": "Company XYZ",
        "Amount": "2300.50",
        "Date": "2024-01-16"
    }
]

Later usage:

# Access loaded data
total_records = len(result_list)
first_invoice = result_list[0]
first_number = first_invoice["InvoiceNumber"]

# Iterate over records
for record in result_list:
    number = record["InvoiceNumber"]
    customer = record["Customer"]
    amount = record["Amount"]
    # Process each record...

Important

All values are loaded as strings (text). If you need to perform numeric operations, you’ll need to convert values:

numeric_amount = float(record["Amount"])

---

Block Configuration

Basic Configuration

block_type: excel_load
parameters:
  file_path: "C:\\data\\invoices.xlsx"
  sheet: "Invoices"
  column_keys: ["InvoiceNumber", "Customer", "Amount", "Status"]
  result_list: loaded_invoices

Configuration with Variables

block_type: excel_load
parameters:
  file_path: ${file_path}  # Previously defined variable
  sheet: ${sheet_name}
  column_keys: ${columns_to_read}
  result_list: excel_data

---

Use Cases

Case 1: Invoice Processing

Scenario: Read pending invoices from Excel and process them one by one.

Configuration:

block_type: excel_load
parameters:
  file_path: "C:\\invoices\\pending_january.xlsx"
  sheet: "Invoices"
  column_keys: ["InvoiceNumber", "Vendor", "Amount", "DueDate"]
  result_list: pending_invoices

Result:

• All invoices loaded into pending_invoices
• Each invoice is a dictionary with the 4 specified columns
• Ready to process in subsequent iterator blocks

---

Case 2: Customer Import

Scenario: Load list of new customers to register in a CRM system.

Configuration:

block_type: excel_load
parameters:
  file_path: "./input/new_customers.xlsx"
  sheet: "Sheet1"
  column_keys: ["Name", "Email", "Phone", "Company", "City"]
  result_list: customers_to_process

Later usage:

# In subsequent Python block
for customer in customers_to_process:
    name = customer["Name"]
    email = customer["Email"]
    phone = customer["Phone"]

    # Register in CRM
    result = create_crm_customer(name, email, phone)

---

Case 3: Bulk Inventory Update

Scenario: Read stock updates from Excel and apply them to inventory system.

Configuration:

block_type: excel_load
parameters:
  file_path: "\\\\server\\shared\\updated_inventory.xlsx"
  sheet: "Stock"
  column_keys: ["SKU", "NewStock", "Location", "UpdateDate"]
  result_list: stock_updates

Advantages:

• Supports network paths (UNC paths)
• Allows processing thousands of updates
• Maintains traceability with update date

---

Case 4: Master Data Reading

Scenario: Load product catalog for validation in other processes.

Configuration:

block_type: excel_load
parameters:
  file_path: "./master_data/product_catalog.xlsx"
  sheet: "Products"
  column_keys: ["ProductCode", "Description", "SalePrice", "Category", "Active"]
  result_list: product_catalog

Usage:

# Create dictionary for quick lookup
catalog_dict = {
    product["ProductCode"]: product
    for product in product_catalog
}

# Validate if a product exists
if code in catalog_dict:
    price = catalog_dict[code]["SalePrice"]

---

Validations and Controls

Automatic Validations

The Excel Load block performs the following validations:

1. File existence: Verifies the file exists at the specified path
2. Valid format: Confirms the file is a valid Excel (.xlsx, .xls, .xlsm)
3. Sheet existence: Validates the specified sheet exists in the file
4. Column existence: Verifies all specified columns exist in the sheet
5. Read permissions: Confirms the robot has read access to the file

Common Errors

Error	Cause	Solution
File not found	File doesn’t exist at specified path	Verify path and file existence
Sheet not found	Sheet doesn’t exist in file	Check exact sheet name (case-sensitive)
Column not found	A specified column doesn’t exist	Verify column names in first row
Permission denied	No read permissions	Check file permissions or run as administrator
File in use	File open in another application	Close Excel before executing process
Corrupted file	Excel file is corrupted or damaged	Repair file or use backup copy

---

Best Practices

1. Excel File Preparation

✅ Recommended:

| InvoiceNumber | Customer     | Amount  | Status   |
|---------------|--------------|---------|----------|
| F-001         | Company A    | 1500.00 | Pending  |
| F-002         | Company B    | 2300.50 | Paid     |

❌ Avoid:

• Empty rows at the beginning
• Multiple header rows
• Merged cells in headers
• Excel table format (use normal range)
• Blank spaces before/after column names

2. Column Names

✅ Good names:

• InvoiceNumber
• ProcessDate
• TotalAmount
• Customer_ID

❌ Problematic names:

• Invoice Number (spaces)
• Date/Process (special characters)
• Total $ (symbols)
• Unnamed columns

3. Path Management

# ✅ Good: Use environment variables or configuration
file_path = "${input_folder}/invoices.xlsx"

# ✅ Good: Relative paths for portability
file_path = "./data/input/invoices.xlsx"

# ⚠️ Caution: Absolute paths (less portable)
file_path = "C:\\MyProject\\data\\invoices.xlsx"

# ✅ Good: Network paths
file_path = "\\\\server\\shared\\invoices.xlsx"

4. Handling Loaded Data

# ✅ Verify data was loaded
if len(result_list) == 0:
    log.warning("No data found in Excel file")
    # Handle no-data case
else:
    log.info(f"Loaded {len(result_list)} records")

# ✅ Validate expected structure
first_record = result_list[0]
if "InvoiceNumber" not in first_record:
    log.error("Missing InvoiceNumber column")

# ✅ Convert data types
for record in result_list:
    record["Amount"] = float(record["Amount"])
    record["Date"] = parse_date(record["Date"])

5. Performance

For large files (>1000 rows):

• ✅ Consider splitting file into smaller batches
• ✅ Use only necessary columns in column_keys
• ✅ Implement logging to monitor progress
• ⚠️ Consider memory usage

For very large files (>10000 rows):

• Consider using specialized batch processing blocks
• Evaluate if loading everything into memory is necessary
• Implement chunk processing if available

---

Complete Examples

Example 1: Complete Billing Process

# Block 1: Load invoices
- block_type: excel_load
  parameters:
    file_path: "./input/monthly_invoices.xlsx"
    sheet: "Invoices"
    column_keys: ["InvoiceNumber", "Customer", "TaxID", "Amount", "Date"]
    result_list: invoices_to_process

# Block 2: Iterate over invoices (configured in platform)

# Block 3: Process each invoice (Python)
- block_type: python
  code: |
    invoice = current_item  # Current invoice from iteration

    # Validate data
    if not invoice["TaxID"]:
        log.force_ko(f"Invoice {invoice['InvoiceNumber']}: Empty Tax ID")

    # Process in external system
    try:
        result = send_to_face(
            number=invoice["InvoiceNumber"],
            customer=invoice["Customer"],
            tax_id=invoice["TaxID"],
            amount=float(invoice["Amount"])
        )
        log.result("ok", f"Invoice {invoice['InvoiceNumber']} processed")
    except Exception as e:
        log.result("error", f"Error in invoice {invoice['InvoiceNumber']}: {str(e)}")

Example 2: Data Validation and Enrichment

# Load customer data
- block_type: excel_load
  parameters:
    file_path: "./new_customers.xlsx"
    sheet: "Sheet1"
    column_keys: ["Name", "Email", "Phone", "City"]
    result_list: customers_raw

# Validate and enrich
- block_type: python
  code: |
    valid_customers = []
    invalid_customers = []

    for customer in customers_raw:
        # Validate email
        if "@" not in customer["Email"]:
            invalid_customers.append({
                **customer,
                "reason": "Invalid email"
            })
            continue

        # Validate phone
        clean_phone = customer["Phone"].replace(" ", "").replace("-", "")
        if len(clean_phone) < 9:
            invalid_customers.append({
                **customer,
                "reason": "Invalid phone"
            })
            continue

        # Enrich data
        processed_customer = {
            **customer,
            "Phone": clean_phone,
            "Email": customer["Email"].lower().strip(),
            "LoadDate": datetime.now().strftime("%Y-%m-%d")
        }
        valid_customers.append(processed_customer)

    # Save for next block
    data.set("valid_customers", valid_customers)
    data.set("invalid_customers", invalid_customers)

    log.info(f"Valid: {len(valid_customers)}, Invalid: {len(invalid_customers)}")

---

Integration with Other Blocks

With Iterator Block

The Excel Load block is typically used followed by an iterator block:

[Excel Load] → [Iterator] → [Process Each Item]

Flow:

1. Excel Load loads all data into a list
2. Iterator takes the list and executes following blocks for each element
3. Each iteration processes one Excel record

With Validation Blocks

[Excel Load] → [Python Validation] → [Filter] → [Process Valid Items]

With Write Blocks

[Excel Load] → [Process] → [Excel Write] → [Report]

Example: Read data, process it, and write results to another Excel.

---

Troubleshooting

Problem: “No data found”

Symptoms: result_list is empty

Possible causes:

• Sheet is empty
• Only headers with no data
• Column names don’t match

Solutions:

1. Verify sheet has data besides headers
2. Open Excel and confirm there are rows with information
3. Check that names in column_keys match exactly

Problem: “Column not found”

Symptoms: Error indicating a column doesn’t exist

Solutions:

1. Open Excel and verify exact name in first row
2. Check capitalization
3. Verify there are no extra spaces: "Name " vs "Name"
4. If column has spaces, include them: "Invoice Number"

Problem: “File in use”

Symptoms: Cannot read file

Solutions:

1. Close Excel file if it’s open
2. Close other applications that might have the file open
3. Verify another process isn’t using the file
4. As last resort, restart the robot

Problem: Special characters display incorrectly

Symptoms: Accents or special characters look wrong

Solutions:

1. Save Excel as .xlsx (not .xls)
2. Verify file encoding
3. Use modern Excel to create the file
4. Avoid copy/paste from other sources with different encoding

---

Need more help?

If this guide didn’t solve your problem or you found an error in the documentation:

• Technical support: help@heptora.com
• Clearly describe the problem you encountered
• Include screenshots if possible
• Indicate which documentation steps you followed

Our support team will help you resolve any issue.

Related Resources

• Excel Write Block - Write data to Excel files
• Automation Blocks - General blocks guide
• Heptora Language - Language reference
• Python in Heptora - Using Python in custom blocks