Class ImportConfig

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Configuration for importing CSV data into database tables.

public class ImportConfig

Inheritance: object

ImportConfig

Derived: GeoJSONImportConfig

Remarks

ImportConfig controls how CSV files are parsed and mapped to database columns when using ImportData(Table, string, ImportConfig, bool). It handles:

CSV format settings (delimiters, quote characters, line terminators)
Date/time format parsing
Column mapping between CSV headers and database columns
Type conversions from string to database types
Identity column handling

ImportConfig provides two initialization modes:

ISO Format (default): Uses standard ISO date formats and comma delimiters
Culture Format: Uses current culture's date formats and list separators

Column mappings define how CSV columns (identified by header name) map to database columns. Mappings can be auto-generated from a Table or manually configured for custom scenarios.

Examples

Basic import with auto-generated mappings:

using (Manager manager = new Manager(connection))
{
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("my_schema");
    Table usersTable = schema["users"];

    // Create import config with automatic column mappings
    ImportConfig config = new ImportConfig(usersTable);

    // Import CSV file
    string csvFilePath = @"C:\data\users.csv";
    int rowsImported = manager.Import(config, usersTable, csvFilePath);

    Console.WriteLine($"Imported {rowsImported} rows");
}

// CSV file format (users.csv):
// id,username,email,created_at
// 1,john_doe,john@example.com,2025-01-07 10:30:00
// 2,jane_smith,jane@example.com,2025-01-07 11:45:00

Custom CSV format with manual mappings:

// Create config for tab-delimited file
ImportConfig config = new ImportConfig(DateFormatType.Iso);
config.FieldDelimiter = '\t';  // Tab-separated
config.QuoteCharacter = '\'';  // Single quotes
config.UseColumnHeaders = true;

// Manually map CSV columns to database columns
config.Mappings.AddMapping("ID", "user_id", DataType.Integer);
config.Mappings.AddMapping("Username", "username", DataType.VarChar);
config.Mappings.AddMapping("Email Address", "email", DataType.VarChar);
config.Mappings.AddMapping("Registration Date", "created_at", DataType.DateTime);

int rowsImported = manager.Import(config, usersTable, csvFilePath);

Import with custom date formats:

// Configure for European date format (dd/MM/yyyy)
ImportConfig config = new ImportConfig(DateFormatType.Culture);
config.DateFormat = "dd/MM/yyyy";
config.DateTimeFormat = "dd/MM/yyyy HH:mm:ss";
config.TimestampFormat = "dd/MM/yyyy HH:mm:ss zzz";

// Auto-generate mappings
config.AutoGenerateMappings(usersTable);

int rowsImported = manager.Import(config, usersTable, @"C:\data\european_dates.csv");

// CSV file format:
// id,username,created_at
// 1,john_doe,07/01/2025 10:30:00
// 2,jane_smith,07/01/2025 11:45:00

Import with identity columns:

Table productsTable = schema["products"];

// Keep identity values from CSV (don't auto-generate)
ImportConfig config = new ImportConfig(productsTable, keepIdentity: true);

// This will import the exact ID values from the CSV
int rowsImported = manager.Import(config, productsTable, @"C:\data\products.csv");

// CSV file:
// id,name,price
// 100,Widget,9.99
// 101,Gadget,19.99
// (IDs 100, 101 will be inserted as-is)

Selective column mapping:

// Start with auto-generated mappings
ImportConfig config = new ImportConfig(ordersTable);

// Remove columns we don't want to import
config.Mappings.RemoveMapping("internal_notes");
config.Mappings.RemoveMapping("temp_field");

// Add a custom mapping for a renamed column
config.Mappings.AddMapping("Order ID", "order_id", DataType.Integer);

int rowsImported = manager.Import(config, ordersTable, csvFilePath);

Handling CSV without headers:

ImportConfig config = new ImportConfig(DateFormatType.Iso);
config.UseColumnHeaders = false;  // No header row in CSV

// Manually define column mappings in order
config.Mappings.AddMapping("Column1", "id", DataType.Integer);
config.Mappings.AddMapping("Column2", "username", DataType.VarChar);
config.Mappings.AddMapping("Column3", "email", DataType.VarChar);

int rowsImported = manager.Import(config, usersTable, csvFilePath);

// CSV file (no headers):
// 1,john_doe,john@example.com
// 2,jane_smith,jane@example.com

Complex CSV format:

// Configure for pipe-delimited, double-quoted fields
ImportConfig config = new ImportConfig(productsTable);
config.FieldDelimiter = '|';
config.QuoteCharacter = '"';
config.EscapeCharacter = '\\';
config.LineTerminator = "\r\n";  // Windows line endings

int rowsImported = manager.Import(config, productsTable, csvFilePath);

// CSV file format:
// id|name|description
// 1|"Product A"|"Description with \"quotes\" inside"
// 2|"Product B"|"Another description"

Checking mapping configuration:

ImportConfig config = new ImportConfig(usersTable);

// Check if specific columns are mapped
bool hasUsername = config.Mappings.HeaderExists("username");
bool hasEmail = config.Mappings.ColumnExists("email");

// Get mapping details
string dbColumn = config.Mappings.GetColumn("username");
Type columnType = config.Mappings.GetColumnType("username");
DataType dataType = config.Mappings.GetDataType("username");

// List all mappings
foreach (string header in config.Mappings.Headers)
{
    string column = config.Mappings.GetColumn(header);
    DataType type = config.Mappings.GetDataType(header);
    Console.WriteLine($"{header} => {column} ({type})");
}

Constructors

ImportConfig(DateFormatType): Initializes a new ImportConfig with default settings for the specified date format style.

ImportConfig(Table): Creates an Import Config and auto generates column mappings from the passed in Table

ImportConfig(Table, bool): Creates an Import Config and auto generates column mappings from the passed in Table

ImportConfig(Table, DateFormatType, bool): Creates an Import Config and auto generates column mappings from the passed in Table

Properties

BlobColumns: Per-column blob handling configuration. Key is the database column name. Use SetBlobColumnBase64(string) or SetBlobColumnFile(string, string) to configure.

DateFormat: Gets or sets the format string used to parse DateOnly values in the CSV. Defaults to yyyy-MM-dd (ISO) or the current culture short date pattern.

DateTimeFormat: Gets or sets the format string used to parse DateTime values in the CSV. Defaults to yyyy-MM-dd HH:mm:ss (ISO) or the current culture full date/time pattern.

EscapeCharacter: Gets or sets the character used to escape special characters within quoted fields. Defaults to backslash (</code>).

FieldDelimiter: Gets or sets the character used to separate fields in the CSV input. Defaults to comma (,) for ISO format or the culture list separator for Culture format.

IntervalFormat: Gets or sets the format string used to parse TimeSpan interval values in the CSV. Defaults to the round-trip constant format c.

KeepIdentity: Gets or sets a value indicating whether auto-generated identity (auto-increment) column values in the CSV are preserved and inserted as-is rather than being regenerated by the database. Defaults to false.

LineTerminator: Gets or sets the string that terminates each CSV row. Defaults to NewLine.

Mappings: Gets or sets the column mapping configuration that defines how CSV headers map to database columns and their data types.

NumberSeparator: Gets or sets the thousands separator string to strip from numeric values before parsing (e.g. "," for values like 1,450). Defaults to an empty string, meaning no separator is stripped and numeric values must parse as-is.

QuoteCharacter: Gets or sets the character used to quote field values that contain the delimiter or line terminator. Defaults to double-quote (").

TimeFormat: Gets or sets the format string used to parse TimeOnly values in the CSV. Defaults to HH:mm:ss (ISO) or the current culture short time pattern.

TimestampFormat: Gets or sets the format string used to parse DateTimeOffset timestamp values in the CSV. Defaults to yyyy-MM-dd HH:mm:ss zzz (ISO) or the current culture RFC1123 pattern.

UpdateFullTextIndex: Gets or sets a value indicating whether full-text indexes are updated after the import completes. Defaults to true.

UseColumnHeaders: Gets or sets a value indicating whether the first row of the CSV file contains column headers. Defaults to true.

Methods

AutoGenerateMappings(Table): This will auto generate mappings based on all non-autogenerate columns

SetBlobColumnBase64(string): Configures a blob column to be imported from a Base64-encoded string in the CSV cell.