ScanDataFile

Overview

ScanDataFileTask analyzes a delimited data file's structure and automatically infers column definitions (name, type, width, nullability) by scanning the file's contents. It creates and saves a DataFileSpec entity that can be reused for subsequent file imports with LoadDataFileTask.

Use this task when: You need to discover the schema of an unknown delimited file, create reusable file specifications for recurring imports, or validate file structure before loading data.

Don't use this task when: You already have a DataFileSpec configured and just need to load the file (use LoadDataFileTask), the file structure changes frequently making specs impractical, or you want to both scan and load in a single operation (use ProcessDataFileTask).

Parameters

Execution Flow

flowchart TD
    A[Start ScanDataFile] --> B[Create ReadDataFileSettings]
    B --> C[Create DelimitedStreamScanner]
    C --> D[Open file stream]
    D --> E{Read first row}
    E -->|Empty file| Z[Throw: Stream is empty]
    E -->|Success| F{HasHeader?}
    F -->|Yes| G[Use row values as column names]
    F -->|No| H[Generate Column1, Column2, etc.]
    G --> I[Initialize ColumnScanInfo for each column]
    H --> I
    I --> J{Read next data row}
    J -->|More rows| K[Update column scan info<br/>- Check if Boolean/Integer/Date/etc.<br/>- Track max width<br/>- Count nulls]
    K --> L{Log every 10,000 rows?}
    L -->|Yes| M[Log progress]
    L -->|No| J
    M --> J
    J -->|No more rows| N[Finalize column types based on scan results]
    N --> O[Determine type priority:<br/>Boolean > Integer > NumericString ><br/>Money > Measure > Date > DateTime > String]
    O --> P[Round up column widths to nearest 100]
    P --> Q[Create DataFileSpec with inferred columns]
    Q --> R[Save DataFileSpec to database]
    R --> S[End]

    style D fill:#e1f5ff
    style K fill:#e1f5ff
    style N fill:#fff4e1
    style R fill:#e1f5ff
    style Z fill:#ffe1e1

Processing Algorithm

Column Type Inference

The scanner reads every row in the file and tests each column value against multiple type parsers. A column can only be typed as a specific type if all non-null values in that column successfully parse as that type.

Type Priority (highest to lowest):

1. Boolean - All values are "true", "false", "1", "0", "yes", "no" (case-insensitive)
2. Integer - All values parse as whole numbers
3. NumericString - Values with leading zeros like "00123" (must be stored as strings)
4. Money - All values parse as currency (may include $, commas)
5. Measure - All values parse as decimal numbers
6. Date - All values parse as dates (no time component)
7. DateTime - All values parse as dates with time
8. String - Default fallback type

Width Calculation

Column widths are determined by the maximum observed width across all rows, then rounded up to the nearest 100 characters for database efficiency.

Example: If the longest value in a column is 127 characters, the width is set to 200.

Example Execution

For a file with 50,000 rows:

• Row 1 (header): Defines column names: PatientID, Amount, ServiceDate
• Rows 2-50,000: Scanner tests each value:
  • PatientID column: All values are integers → type = Integer
  • Amount column: All values parse as money ($123.45) → type = Money
  • ServiceDate column: All values parse as dates (2024-01-15) → type = Date
• Progress logs: Printed at rows 10,000, 20,000, 30,000, 40,000, 50,000
• Final result: DataFileSpec saved with 3 columns, each with inferred type and width

Usage Examples

Example 1: Basic CSV scan with default settings

var task = new ScanDataFileTask
{
    DataFileSpecKey = "PatientDemographics",
    FilePath = @"C:\Imports\patients.csv",
    TableName = "ImportPatientDemographics"
    // HasHeader defaults to true
    // ColumnSeparator defaults to ","
    // RowTerminator defaults to "\r\n"
};

Example 2: Pipe-delimited file without header

var task = new ScanDataFileTask
{
    DataFileSpecKey = "ClaimsData",
    FilePath = @"C:\Imports\claims.txt",
    TableName = "ImportClaims",
    ColumnSeparator = "|",
    HasHeader = false, // Columns will be named Column1, Column2, etc.
    MaxInvalidRows = 10 // Allow up to 10 invalid rows before failing
};

Example 3: S3 file with text qualification

var task = new ScanDataFileTask
{
    DataFileSpecKey = "VendorData",
    FilePath = "s3://mdclarity-imports/vendor_file.csv",
    TableName = "ImportVendorData",
    TextQualified = true, // Values are quoted: "value1","value2"
    ColumnSeparator = ",",
    RowTerminator = "\n" // Unix-style line endings
};

Example 4: Tab-delimited file from external system

var task = new ScanDataFileTask
{
    DataFileSpecKey = "ExternalCharges",
    FilePath = @"\\fileserver\imports\charges.tsv",
    TableName = "ImportCharges",
    ColumnSeparator = "\t", // Tab-separated values
    HasHeader = true,
    MaxInvalidRows = 0 // Fail immediately on invalid data
};

Related Tasks Comparison

When to use each task:

• ScanDataFileTask: Need reusable DataFileSpec for recurring imports with same structure
• LoadDataFileTask: Already have DataFileSpec, just need to load the data
• ProcessDataFileTask: One-time or ad-hoc file import where you want to scan and load together

Performance Considerations

Database Impact

• Reads: None (except checking for existing DataFileSpec to overwrite)
• Writes: Single INSERT or UPDATE to save the DataFileSpec entity
• Tables affected: DataFileSpec table only
• Locking: Minimal - brief write lock on DataFileSpec table

File Scanning Impact

• I/O: Reads the entire file sequentially to determine column types
• Memory: Lightweight - processes row-by-row without loading entire file into memory
• Network: For S3 files, streams data incrementally rather than downloading entire file first

Optimization Tips

Error Handling

The task uses a fail-fast approach for critical errors but allows configurable tolerance for invalid rows:

// From DelimitedStreamScanner.cs
if (!reader.ReadRow())
{
    throw new Exception($"Stream is empty.");
}

if (reader.DataCount == 0)
{
    throw new Exception($"Stream contained no data.");
}

Important error behaviors:

• Empty file: Throws exception immediately
• No data rows: Throws exception after scanning (only header found)
• Invalid rows: Task fails if invalid row count exceeds MaxInvalidRows parameter
• File not found: Throws exception from stream reader factory
• S3 access errors: Throws exception with AWS error details

Note: The task does not validate that inferred types are correct for your business logic - it only ensures the file is parseable. Always validate the saved DataFileSpec before using it for production imports.

Common Pitfalls

Code References

• Task Definition: MDClarityCore/Backend/Tasks/ScanDataFile/ScanDataFileTask.cs:9
• Execute Method: MDClarityCore/Backend/Tasks/ScanDataFile/ScanDataFileTask.cs:26
• Field Definitions: MDClarityCore/Backend/Tasks/ScanDataFile/ScanDataFileTask.cs:63
• DelimitedStreamScanner: MDClarityCore/Backend/DataImport/DelimitedStreamScanner.cs:13
• ScanStream Algorithm: MDClarityCore/Backend/DataImport/DelimitedStreamScanner.cs:91
• Type Inference Logic: MDClarityCore/Backend/DataImport/DelimitedStreamScanner.cs:195
• DataFileSpec Model: MDClarityCore/Models/DataImport/DataFileSpec.cs:7
• IDelimitedStreamScanner Interface: MDClarityCore/Backend/Common/Interfaces/IDelimitedStreamScanner.cs:10