Automating CSV/Excel File Imports with Python Pandas in Bika

Guides & Tutorials
,
1

When Should You Use This Method?

While Bika provides one-click file imports, this tutorial is for you when:

  • Your Excel/CSV columns don’t match Bika’s database schema
  • Your files are stored on third-party platforms (requires URL access)
  • You need advanced data processing (filtering, transformations, cleaning)

You’ll learn to:

  1. Use Python pandas in Run Script Action for data manipulation
  2. Pass variables between Automation steps
  3. Batch create records with Loop Action + Create Record Action
  4. Implement dynamic fields in Manual Triggers
2 Likes
2

Try It Yourself First

I’ve prepared a ready-to-use template with sample data to help you experience this workflow :point_down::

How It Work

Let’s dissect the automation workflow:

Automation Workflow Diagram
Automation Workflow Diagram1920×1158 219 KB

Step 1: Initiate Import

  1. Paste your CSV/Excel URL in the dynamic fields panel
    (Use the mock data URL from template details for quick testing)

    :bulb: About Dynamic Fields
    These runtime parameters in the Manual Trigger act like environment variables - set them before execution to customize each automation run.

  2. Click “Run Now”

Step 2: Python Data Processing

def main():
    url = "{URL_FROM_TRIGGER}"  # Dynamic field injection
    df = pandas.read_csv(url, encoding='utf-8')

    # Convert to ISO 8601 UTC datetime
    df["sale_date"] = pandas.to_datetime(df["sale_date"]) \
        .dt.tz_localize("Asia/Shanghai") \
        .dt.tz_convert("UTC") \
        .dt.strftime("%Y-%m-%dT%H:%M:%SZ")  # Bika-compliant format

    # Enforce integer type for quantity
    df["quantity_sold"] = pandas.to_numeric(
        df["quantity_sold"], 
        errors="coerce"
    ).fillna(0).astype(int)
    
    return df.to_dict('records')

try:
    result = main()
except Exception as e:
    error_msg = f"CSV processing failed: {type(e).__name__}: {e}"

:bulb:​Key Implementation Details​​

  • ​​Dynamic Field Binding​​: The url variable uses double-quoted "{URL_FROM_TRIGGER}" syntax to inject parameters from the Manual Trigger. Use / to open variable picker.
  • ​​Data Type Enforcement​​:
    • to_datetime() + strftime() ensures ISO 8601 compliance (Bika’s required datetime format)
    • to_numeric() with coerce handles dirty numeric data
  • ​​Output Formatting​​: df.to_dict('records') converts DataFrame to Bika-compatible list of dictionaries (mandatory for Loop Action)

Step 3: Batch Record Creation

  1. ​​Loop Action​​: Processes the Python output list
  2. ​​Create Record Action​​: Maps transformed data to Bika fields

Need Help?

Found something unclear? Spot an issue? Have a brilliant improvement idea?
:speech_balloon: ​​Leave a comment below​

1 Like