Class ImportConfig

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Configuration for importing CSV data into database tables.

public class ImportConfig

Inheritance: object

ImportConfig

Remarks

ImportConfig controls how CSV files are parsed and mapped to database columns when using ImportData(Table, string, ImportConfig). 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.

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.