File Connections

File connections enable reading from and writing to file-based data sources like Excel spreadsheets and CSV files. They're essential for importing data, generating reports, and integrating with systems that exchange data via files.

Supported File Types

Type	Extensions	Read	Write
Excel	.xlsx, .xls	Yes	Yes
CSV	.csv	Yes	Yes
JSON	.json	Yes	Yes

Creating a File Connection

1. Navigate to Connections in the sidebar
2. Click New Connection
3. Select the file type (Excel, CSV, etc.)
4. Configure the connection settings
5. Test and save

Excel Connections

Configuration

Connection Settings:

Setting	Description
Name	Display name for the connection
File Location	Path or upload method
Default Sheet	Optional default sheet to use

File Location Options

Upload: Upload a file directly to the system:

• File stored securely
• Accessible to all flows in workspace
• Versioning supported

Cloud Storage: Connect to files in cloud storage:

• Azure Blob Storage
• AWS S3
• Google Cloud Storage
• SharePoint/OneDrive

Network Path: Access files on network shares:

• Requires worker access to network
• Use UNC paths

Entity Discovery

After configuration, discover available entities:

Discover Sheets:

1. Click Discover Entities
2. Available sheets are listed
3. Each sheet becomes an entity
4. Schema detected from headers

Sheet as Entity:

Entity	Description
Sheet1	First worksheet
SalesData	Named worksheet
Summary	Summary worksheet

Excel-Specific Options

Header Row:

• Row number containing column headers
• Default: 1 (first row)
• Adjust if headers are lower

Data Start Row:

• First row of actual data
• Default: 2 (after headers)
• Adjust for files with titles/descriptions

Column Types:

• Auto-detected from data
• Can be overridden per column
• Handles mixed types

Named Ranges:

• Named ranges appear as entities
• Use for specific data areas

CSV Connections

Configuration

Connection Settings:

Setting	Description
Name	Display name for the connection
File Path	Path to the CSV file
Delimiter	Character separating fields
Encoding	Character encoding (UTF-8, etc.)
Has Headers	Whether first row is headers

Delimiter Options

Common delimiters:

Delimiter	Character	Name
Comma	,	Standard CSV
Tab	\t	TSV
Semicolon	;	European CSV
Pipe	|	Pipe-delimited

Custom delimiter: Specify any character for unusual formats.

Encoding

Supported encodings:

• UTF-8 (recommended)
• UTF-16
• ISO-8859-1 (Latin-1)
• Windows-1252

Choosing encoding:

• Check file source
• UTF-8 for modern files
• Windows-1252 for legacy Windows files

Header Configuration

With headers:

• First row contains column names
• Columns named from headers

Without headers:

• System generates: Column1, Column2, etc.
• Or specify custom names

Schema Detection

CSV schema is auto-detected:

• Column count from first row
• Types inferred from data samples
• Override types if needed

JSON Connections

Configuration

Connection Settings:

Setting	Description
Name	Display name
File Path	Path to JSON file
Root Path	JSON path to data array

JSON Structure

Array at root:

[
  { "id": 1, "name": "Item 1" },
  { "id": 2, "name": "Item 2" }
]

Root Path: (empty)

Nested array:

{
  "status": "ok",
  "data": {
    "items": [...]
  }
}

Root Path: data.items

Nested Data

JSON can contain nested objects:

{
  "id": 1,
  "address": {
    "city": "Seattle",
    "state": "WA"
  }
}

Accessed as:

• address.city
• address.state

Using File Entities in Flows

Reading File Data

1. Add an Entity node
2. Select file connection
3. Choose entity (sheet/file)
4. Configure columns and filters

Example:

Connection: Sales Reports (Excel)
Entity: January2024
Columns: Date, Product, Amount, Region
Filter: Amount > 100

Writing to Files

Use output actions to write:

Excel output:

• Append rows to existing sheet
• Create new sheet
• Replace sheet contents

CSV output:

• Append to file
• Create new file
• Replace file contents

File Path Patterns

Static Paths

Fixed file location:

/data/reports/sales.xlsx

Dynamic Paths

Use parameters for dynamic paths:

/data/reports/${year}/${month}/sales.xlsx

From schedule trigger:

/data/daily/report_${runDate}.csv

Date-Based Patterns

Common patterns:

/archive/${YYYY}/${MM}/data_${YYYY}${MM}${DD}.xlsx
/reports/monthly_${YYYY}-${MM}.csv

Data Type Handling

Excel Types

Excel cells have types:

Excel Type	System Type
Number	Number
Text	String
Date	DateTime
Boolean	Boolean
Formula	Evaluated result

CSV Types

All CSV data is initially text:

• Numbers parsed automatically
• Dates require format hints
• Use Transform for explicit conversion

Type Conversion

When types need adjustment:

[File Entity] → [Transform: convert types] → [Continue]

Transform expressions:

Amount = TONUMBER(${AmountText})
OrderDate = PARSE_DATE(${DateString}, "MM/DD/YYYY")

Error Handling

File Not Found

Causes:

• Path incorrect
• File moved/deleted
• Permission issues

Solutions:

• Verify file path
• Check file exists
• Verify permissions

Format Errors

Causes:

• Wrong delimiter
• Encoding mismatch
• Corrupted file

Solutions:

• Check delimiter setting
• Try different encoding
• Validate file format

Schema Mismatch

Causes:

• Column names changed
• Columns added/removed
• Order changed

Solutions:

• Re-discover entities
• Update flow configuration
• Handle missing columns

Best Practices

File Organization

Organize files logically:

/data/
  /imports/
    /daily/
    /monthly/
  /exports/
    /reports/
    /archives/

Naming Conventions

Consistent file names:

sales_YYYYMMDD.xlsx
customers_export_YYYY-MM-DD.csv
report_monthly_YYYY-MM.xlsx

Version Control

Archive processed files:

/data/processed/YYYY/MM/

Keep originals: Don't modify source files during processing.

Data Validation

Validate file data:

1. Check expected columns exist
2. Validate data types
3. Check for required values
4. Handle unexpected formats

Performance Tips

Large Files

For large files:

• Use column selection (don't read all)
• Apply filters at source
• Process in batches if possible

Excel Formulas

Note: Formulas are evaluated:

• Values (not formulas) are read
• Calculation happens in Excel
• May need file to be opened/saved first

Memory Usage

Large files consume memory:

• Filter to reduce rows
• Select needed columns only
• Consider chunked processing

Troubleshooting

Empty Data

Possible causes:

1. Wrong sheet selected
2. Data area misconfigured
3. File is empty
4. Header row incorrect

Solutions:

• Open file to verify data
• Check entity configuration
• Verify header row setting

Wrong Columns

Possible causes:

1. Header row incorrect
2. File structure changed
3. Extra/missing columns

Solutions:

• Re-discover entities
• Check header row setting
• Update column selection

Encoding Issues

Symptoms: Strange characters, garbled text

Solutions:

• Try different encoding
• Check file source encoding
• Use UTF-8 when possible

Examples

Daily Sales Import

Connection:

Name: Daily Sales Files
Type: Excel
Location: /data/imports/sales/

Flow:

[Entity: Today's Sales File] → [Transform] → [Database Insert]

Customer Export

Connection:

Name: Export Files
Type: CSV
Delimiter: Comma

Flow:

[Database Query] → [Transform] → [CSV Export: customers_export.csv]

Report Generation

Connection:

Name: Reports
Type: Excel
Location: /reports/

Flow:

[Aggregate Data] → [Format Report] → [Excel Output: monthly_report.xlsx]

Next Steps

• API Connections - Connect to REST APIs
• Database Connections - Connect to databases
• Entity Node - Use file entities
• Building Flows - Complete workflow guide