Skip to main content

Spreadsheet

The Spreadsheet node enables reading from and writing to spreadsheet files in various formats, making it essential for processing logistics data that often comes in Excel, CSV, or other tabular formats. This node handles data import/export operations with flexible formatting options.

Overview​

The Spreadsheet node is essential when you need to:

  • Import data - Read shipment lists, inventory reports, or customer data from spreadsheets
  • Export reports - Generate Excel or CSV files for stakeholders
  • Data transformation - Convert between different spreadsheet formats
  • Batch processing - Handle large datasets from spreadsheet sources
  • Integration - Connect with systems that use spreadsheet-based data exchange
  • Reporting - Create formatted reports with multiple sheets and styling

Supported Formats​

Input Formats​

  • Excel (.xlsx, .xls) - Microsoft Excel files with multiple sheets
  • CSV/TXT (.csv, .txt) - Comma-separated and other delimited value files
  • OpenDocument (.ods) - LibreOffice/OpenOffice spreadsheets

Output Formats​

  • Excel (.xlsx) - Modern Excel format with formatting support
  • CSV (.csv) - Universal comma-separated format
  • OpenDocument (.ods) - LibreOffice/OpenOffice spreadsheets
  • HTML (.html) - Web-friendly table format for embedding in web pages or email
  • RTF (.rtf) - Rich Text Format for compatibility with word processors and document systems

Configuration​

Read Operations​

File Source​

  • File upload - Upload spreadsheet files directly to workflow
  • Previous node output - Use file data from earlier workflow steps

Sheet Selection​

  • Sheet name - Specify which sheet to read by name
  • Sheet index - Select sheet by position (0-based)
  • Dynamic selection - Use workflow data to determine sheet

Data Range​

  • Full sheet - Read all data from the selected sheet
  • Specific range - Define cell range (e.g., A1:D100)
  • Header row - Specify which row contains column headers
  • Data start row - Define where actual data begins

Write Operations​

Output Configuration​

  • File format - Choose output format (Excel, CSV, etc.)
  • File name - Set output file name (can be dynamic)
  • Sheet name - Name for Excel sheets

Data Formatting​

  • Column headers - Include/exclude header row

Data Processing​

Reading Data​

Header Handling​

  • Automatic detection - Detect header row automatically
  • Custom headers - Define column names manually
  • No headers - Treat all rows as data
  • Header mapping - Map spreadsheet columns to object properties

Writing Data​

Data Formatting​

  • Column ordering - Specify order of columns in output
  • Data transformation - Convert data before writing

Best Practices​

Performance Optimization​

  • Limit data ranges - Only read necessary cells/columns
  • Batch operations - Process multiple files efficiently
  • Memory management - Handle large files in chunks
  • Caching - Cache frequently accessed spreadsheet data

Data Quality​

  • Validation rules - Implement comprehensive data validation
  • Error handling - Plan for malformed or missing data
  • Data cleansing - Clean and standardize data during import
  • Backup originals - Preserve original files before processing

File Management​

  • Naming conventions - Use consistent, descriptive file names
  • Version control - Track file versions and changes
  • Access control - Secure sensitive spreadsheet data
  • Storage organization - Organize files in logical folder structures

Common Patterns​

Inventory Management​

Daily Process:
  Read inventory.xlsx → Validate stock levels → Update system → Generate alerts

Configuration:
  Operation: Read
  File: inventory.xlsx
  Sheet: "Current Stock"
  Header Row: 1
  Data Range: A2:F1000

Output: Array of inventory objects for processing

Shipment Tracking Import​

Import Process:
  Read tracking_updates.csv → Parse tracking data → Update database → Notify customers

Configuration:
  Operation: Read
  File: tracking_updates.csv
  Delimiter: ","
  Header Row: 1
  Encoding: UTF-8

Output: Processed tracking data for system updates

Financial Reporting Export​

Monthly Process:
  Collect invoice data → Generate Excel report → Format for stakeholders → Email distribution

Input: Array of invoice data
Configuration:
  Operation: Write
  Format: Excel (.xlsx)
  File Name: "monthly_invoices_{{currentDate}}.xlsx"
  Sheet Name: "Invoice Summary"
  Include Headers: true

Output: Formatted Excel report for stakeholders

Web Report Generation​

Dashboard Process:
  Collect shipment metrics → Generate HTML table → Embed in web dashboard

Input: Array of shipment metrics
Configuration:
  Operation: Write
  Format: HTML (.html)
  File Name: "shipment_dashboard.html"
  Include Headers: true

Output: HTML table for web integration

Document Integration​

Report Process:
  Generate logistics summary → Export as RTF → Import to document system

Input: Array of logistics data
Configuration:
  Operation: Write
  Format: RTF (.rtf)
  File Name: "logistics_summary.rtf"
  Include Headers: true

Output: RTF document for word processor compatibility

Data Migration​

Migration Process:
  Read legacy_data.xlsx → Transform format → Validate → Write new_format.csv → Import to new system

Read Configuration:
  File: legacy_system_export.xlsx
  Sheet: "Customer Data"
  
Write Configuration:
  Format: CSV (.csv)
  File Name: "migrated_customers.csv"

Output: Converted data for new system import

Troubleshooting​

Common Issues​

  • File format errors - Verify file format matches configuration
  • Encoding problems - Check character encoding for CSV files
  • Memory limitations - Process large files in smaller chunks
  • Permission errors - Ensure proper file access permissions

Debugging Tips​

  • Test with small files - Validate configuration with sample data
  • Check data types - Verify expected vs actual data types
  • Validate ranges - Ensure cell ranges are correct
  • Monitor memory usage - Watch for memory issues with large files

The Spreadsheet node provides comprehensive spreadsheet processing capabilities, enabling seamless integration with Excel, CSV, and other tabular data formats commonly used in logistics operations.