Importing Data

Introduction

Velocity provides powerful data import capabilities through CSV files, allowing you to efficiently load data into your database tables. The import functionality is flexible, supporting various data types including geospatial data, handling null values, custom date formats, and configurable column mappings.

Basic Import Operation

At its simplest, importing data involves three components:

A Table object defining the target schema
A CSV file containing the data to import
An ImportConfig object specifying how to interpret the CSV

Simple Import Example

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Connections;

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "myapp"
};

using (var manager = new Manager(connection))
{
    // Load schema and get the table
    Schema schema = manager.LoadSchema("public");
    Table usersTable = schema["users"];

    // Import data with auto-generated configuration
    manager.ImportData(usersTable, "users.csv");
}

The ImportConfig Class

The ImportConfig class controls all aspects of how CSV data is interpreted and imported.

Properties

Property	Type	Description	Default (ISO)	Default (Culture)
`DateFormat`	`string`	Format string for Date columns	`"yyyy-MM-dd"`	Culture-specific
`DateTimeFormat`	`string`	Format string for DateTime columns	`"yyyy-MM-dd HH:mm:ss"`	Culture-specific
`TimestampFormat`	`string`	Format string for Timestamp columns	`"yyyy-MM-dd HH:mm:ss zzz"`	Culture-specific
`FieldDelimiter`	`char`	Character separating fields	`','`	Culture list separator
`QuoteCharacter`	`char`	Character for quoting fields	`'"'`	`'"'`
`EscapeCharacter`	`char`	Character for escaping special chars	`'\\'`	`'\\'`
`LineTerminator`	`string`	Line ending sequence	`Environment.NewLine`	`Environment.NewLine`
`UseColumnHeaders`	`bool`	Whether CSV has header row	`true`	`true`
`KeepIdentity`	`bool`	Import values for autogenerate columns	`false`	`false`
`Mappings`	`ImportConfigMappings`	Column mapping configuration	Empty	Empty

Constructor Options

You can create an ImportConfig using DateFormatType or auto-generate from a Table.

Default Constructor:

// Creates config with ISO date formats
var config = new ImportConfig();

With Date Format Type:

// ISO format (recommended for consistency)
var config = new ImportConfig(DateFormatType.Iso);

// Culture-specific format (uses current culture settings)
var config = new ImportConfig(DateFormatType.Culture);

Auto-Generate from Table:

// Automatically creates mappings for all non-autogenerated columns
var config = new ImportConfig(table);

// With date format type
var config = new ImportConfig(table, DateFormatType.Iso);

// With KeepIdentity flag
var config = new ImportConfig(table, keepIdentity: true);

// All options
var config = new ImportConfig(table, DateFormatType.Iso, keepIdentity: true);

Date and Time Formats

Velocity supports three temporal DataType values with configurable formats:

Date Formats

ISO Format (Recommended):

config.DateFormat = "yyyy-MM-dd";  // Default ISO format

CSV Example:

user_id,username,birthdate
1,john_doe,1990-05-15
2,jane_smith,1985-11-23

Custom Formats:

config.DateFormat = "MM/dd/yyyy";     // US format
config.DateFormat = "dd-MMM-yyyy";    // 15-Jan-1990
config.DateFormat = "yyyyMMdd";       // 19900515

DateTime Formats

ISO Format:

config.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";  // Default ISO format

CSV Example:

event_id,event_name,event_time
1,Meeting,2024-01-15 14:30:00
2,Conference,2024-02-20 09:00:00

Custom Formats:

config.DateTimeFormat = "yyyy-MM-dd hh:mm:ss tt";    // 12-hour with AM/PM
config.DateTimeFormat = "dd/MM/yyyy HH:mm";           // European format

Timestamp Formats (with Timezone)

ISO Format:

config.TimestampFormat = "yyyy-MM-dd HH:mm:ss zzz";  // Default ISO format

CSV Example:

timestamp_value
2007-01-01 00:00:00 +01:00
2010-05-15 08:00:00 -02:00
2015-11-16 14:52:18 +05:00

The zzz format specifier includes the timezone offset (e.g., +01:00, -05:00).

Custom Formats:

// With timezone abbreviation
config.TimestampFormat = "yyyy-MM-dd HH:mm:ss K";

// UTC format
config.TimestampFormat = "yyyy-MM-dd'T'HH:mm:ss'Z'";

Column Mappings

Column mappings define the relationship between CSV headers and database columns. Velocity provides both automatic and manual mapping capabilities.

Automatic Mapping (No Configuration Required)

The simplest approach is to let Velocity automatically create the ImportConfig for you. When you don't provide an ImportConfig parameter, Velocity automatically generates one based on the table schema:

Table table = schema["users"];

// ImportConfig is automatically created with mappings for all non-autogenerated columns
manager.ImportData(table, "users.csv");

This is equivalent to:

Table table = schema["users"];
ImportConfig config = new ImportConfig(table);
manager.ImportData(table, "users.csv", config);

What gets mapped automatically:

All columns where AutoGenerate = false
Columns are mapped by name (CSV header matches column name exactly)
Data types are inferred from the table schema
ISO date format is used by default (DateFormatType.Iso)

Use automatic creation when:

Your CSV headers exactly match your database column names
You're using standard ISO date/time formats
You don't need to preserve identity values (KeepIdentity = false)
You're using default delimiters (comma) and quote characters

When KeepIdentity = true:

var config = new ImportConfig(table, keepIdentity: true);

// Now autogenerated columns are ALSO included in the mappings
// This allows importing specific identity values
manager.ImportData(table, "data_with_ids.csv", config);

When to Provide an Explicit ImportConfig

You need to explicitly create and pass an ImportConfig when you need to customize any import behavior:

// Need to preserve identity values
var config = new ImportConfig(table, keepIdentity: true);
manager.ImportData(table, "users.csv", config);

// Need custom date formats
var config = new ImportConfig(table, DateFormatType.Culture);
config.DateFormat = "MM/dd/yyyy";
manager.ImportData(table, "users.csv", config);

// Need different delimiter
var config = new ImportConfig(table);
config.FieldDelimiter = ';';
manager.ImportData(table, "european_data.csv", config);

// CSV has no headers
var config = new ImportConfig(table);
config.UseColumnHeaders = false;
manager.ImportData(table, "data_no_headers.csv", config);

// CSV headers don't match column names (see Manual Mapping below)

Manual Mapping

When CSV headers don't match column names, or you need selective import, use manual mappings:

var config = new ImportConfig(DateFormatType.Iso);

// Add individual mappings: (csvHeader, columnName, dataType)
config.Mappings.AddMapping("User ID", "user_id", DataType.Long);
config.Mappings.AddMapping("User Name", "username", DataType.VarChar);
config.Mappings.AddMapping("Email Address", "email", DataType.VarChar);
config.Mappings.AddMapping("Join Date", "created_at", DataType.DateTime);

CSV with different headers:

User ID,User Name,Email Address,Join Date
1,john_doe,john@example.com,2024-01-15 10:00:00
2,jane_smith,jane@example.com,2024-01-16 11:30:00

Programmatic Mapping

Build mappings programmatically for complex scenarios:

var config = new ImportConfig();
config.DateFormat = "yyyy-MM-dd";
config.FieldDelimiter = ',';

foreach (Column column in table.Columns)
{
    // Skip autogenerated columns unless KeepIdentity is true
    if (!column.AutoGenerate || config.KeepIdentity)
    {
        config.Mappings.AddMapping(column.Name, column.Name, column.DataType);
    }
}

Mapping Operations

The ImportConfigMappings class provides methods like AddMapping, AddColumn, HeaderExists, ColumnExists, GetColumn, GetDataType, RemoveMapping, and Clear:

// Add a column directly from table definition
config.Mappings.AddColumn(column);

// Check if header exists
if (config.Mappings.HeaderExists("username"))
{
    // ...
}

// Check if column is mapped
if (config.Mappings.ColumnExists("user_id"))
{
    // ...
}

// Get column name for header
string columnName = config.Mappings.GetColumn("User Name");

// Get data type for header
DataType dataType = config.Mappings.GetDataType("User Name");

// Remove a mapping
config.Mappings.RemoveMapping("obsolete_header");

// Clear all mappings
config.Mappings.Clear();

Importing Geospatial Data

Velocity supports importing geospatial data using Well-Known Text (WKT) format in CSV files.

Supported Geometry Types

Both DataType.Geography and DataType.Geometry columns accept WKT format:

POINT - Single point location
LINESTRING - Connected line segments
POLYGON - Closed area
MULTIPOINT - Collection of points
MULTILINESTRING - Collection of lines
MULTIPOLYGON - Collection of polygons

Point Data Example

Table Definition:

Table locationsTable = schema.CreateTable("locations");
locationsTable.AddColumn("location_id", DataType.Long, autoGenerate: true, notNull: true);
locationsTable.AddColumn("name", DataType.VarChar, size: 100);
locationsTable.AddColumn("coordinates", DataType.Geography, notNull: true);
locationsTable.CreatePrimaryKey("pk_locations", "location_id");

// Create spatial index for performance
locationsTable.CreateSpatialIndex("spi_coordinates", "coordinates");

CSV File (locations.csv):

name,coordinates
SingleHop LLC,POINT(-91 38)
ZOHO,POINT(-94 38)
Google LLC,POINT(-100 35)
Amsterdam,POINT(-98 34)

Import Code:

var config = new ImportConfig(locationsTable);

using (var manager = new Manager(connection))
{
    manager.ImportData(locationsTable, "locations.csv", config);
}

Complex Geometry Example

CSV with Polygons:

region_id,region_name,boundary
1,Downtown,POLYGON((-122.4 37.8, -122.4 37.7, -122.3 37.7, -122.3 37.8, -122.4 37.8))
2,Airport,POLYGON((-122.5 37.6, -122.5 37.5, -122.4 37.5, -122.4 37.6, -122.5 37.6))

Import with Identity Values:

// Keep the identity values from CSV instead of auto-generating
var config = new ImportConfig(regionsTable, keepIdentity: true);

using (var manager = new Manager(connection))
{
    manager.ImportData(regionsTable, "regions.csv", config);
}

Geospatial Data Notes

WKT coordinates are specified as (longitude latitude) for geographic data
Point format: POINT(longitude latitude)
Always enclose complex WKT in quotes if it contains commas
Use spatial indexes for optimal query performance
Geometry types support various coordinate reference systems

Teradata Bulk Import: Staging Tables

Teradata uses a multi-phase staging strategy for bulk imports to work around driver limitations with large Clob and geospatial columns. Understanding this mechanism is important when tuning import performance or troubleshooting Teradata-specific issues.

How It Works

Phase 1 – Parallel staging inserts. The source data is split into N batches (controlled by NumberOfStagingTables, default 4). Each batch is inserted concurrently into its own temporary staging table ({table}_{N}_IMP). By default, Clob columns are retyped to VARCHAR(VarCharCastSize) and geospatial columns are retyped to VARCHAR(VarCharCastSize) holding WKT text. Either or both casts can be disabled — see Controlling Clob and Geospatial Casting below.
Phase 2 – Staging-to-target merge. A single INSERT INTO target SELECT ... FROM staging statement is executed for each staging table. If geospatial columns were cast to VARCHAR in Phase 1, the merge wraps them in SYSSPATIAL.ST_GeomFromText() to convert back to the native spatial type. This phase runs within an optional transaction controlled by ImportTransaction.
Phase 3 – Cleanup. All staging tables are dropped regardless of success or failure.

Controlling Clob and Geospatial Casting

Three properties on TeradataDatasourceConnection govern how Clob and geospatial columns are handled during import:

Property	Default	Effect
`ImportCastClob`	`true`	Clob columns in staging tables are retyped as `VARCHAR(VarCharCastSize)`
`ImportCastGeo`	`true`	Geometry/Geography columns in staging tables are retyped as `VARCHAR(VarCharCastSize)` (WKT text). The Phase 2 merge wraps these columns in `ST_GeomFromText()` to convert back to the native spatial type.
`VarCharCastSize`	`32000`	The VARCHAR length applied to every column that is cast — both Clob and geospatial

The two flags are independent and each only affects its own column type. The following matrix shows the resulting staging-table type for each combination:

`ImportCastGeo`	`ImportCastClob`	Geo/Geography staging type	Clob staging type	`VarCharCastSize` limit applies to
`true`	`true`	`VARCHAR(VarCharCastSize)`	`VARCHAR(VarCharCastSize)`	Both Geo and Clob columns
`true`	`false`	`VARCHAR(VarCharCastSize)`	Clob (native, no size limit)	Geo columns only
`false`	`true`	Native spatial type (no size limit)	`VARCHAR(VarCharCastSize)`	Clob columns only
`false`	`false`	Native spatial type (no size limit)	Clob (native, no size limit)	Neither — no VARCHAR casts applied

Note

When ImportCastGeo is false the geospatial columns remain as their native spatial type in the staging table and the Phase 2 merge passes them through without a ST_GeomFromText() call. This removes the VarCharCastSize constraint for geospatial data entirely but requires the Teradata driver to handle the native spatial type during the bulk load directly.

Choosing the right strategy for large data

WKT fits within VarCharCastSize — leave both flags at their defaults (true). The VARCHAR cast is the fastest bulk-load path for the Teradata driver.
WKT exceeds VarCharCastSize but you want to keep the cast — increase VarCharCastSize to a value larger than your longest WKT string:

var connection = new TeradataDatasourceConnection
{
    Hostname = "teradata.example.com",
    Port = 1025,
    Username = "dbc",
    Password = "password",
    Database = "myapp_db"
};

connection.VarCharCastSize = 65000;   // Increase for large polygons
connection.NumberOfStagingTables = 4;

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("myschema");
    Table regionsTable = schema["regions"];

    manager.ImportData(regionsTable, "large_polygons.csv");
}

WKT exceeds the maximum VARCHAR size the database supports — set ImportCastGeo = false to keep geospatial columns in their native spatial type and bypass the VARCHAR limit entirely:

connection.ImportCastGeo = false;     // Geo columns stay as native spatial — no VARCHAR size limit
connection.ImportCastClob = true;     // Clob columns can still be cast independently

Clob data exceeds VarCharCastSize — set ImportCastClob = false. Clob columns remain as native Clob (unbounded) in the staging table:

connection.ImportCastClob = false;    // Clob columns stay as native Clob — no VARCHAR size limit
connection.ImportCastGeo = true;      // Geo columns can still be cast independently

Transaction Control

By default (ImportTransaction = true) the staging-to-target merge in Phase 2 runs inside a single transaction. If the merge fails, all staging inserts for that batch are rolled back. Set ImportTransaction = false to skip the wrapping transaction when importing into already-empty tables or when external transaction management is preferred.

Note

ImportTransaction is a universal setting available on all database connection types, but its effect is most significant on Teradata because the merge phase operates across multiple staging tables in one pass.

Handling Autogenerated Columns (Identity)

By default, Velocity skips autogenerated columns during import, allowing the database to generate values automatically. The ImportData method respects the KeepIdentity setting in ImportConfig.

Default Behavior (KeepIdentity = false)

Table table = schema.CreateTable("products");
table.AddColumn("product_id", DataType.Long, autoGenerate: true, notNull: true);
table.AddColumn("product_name", DataType.VarChar, size: 100);
table.AddColumn("price", DataType.Decimal, precision: 10, scale: 2);

var config = new ImportConfig(table);  // KeepIdentity defaults to false

// CSV should NOT include product_id - it will be auto-generated

CSV File (products.csv):

product_name,price
Widget,19.99
Gadget,29.99

Importing Identity Values (KeepIdentity = true)

When you need to preserve specific identity values (e.g., migrating data, maintaining foreign key relationships):

var config = new ImportConfig(table, keepIdentity: true);

using (var manager = new Manager(connection))
{
    manager.ImportData(table, "products_with_ids.csv", config);

    // After import, new records will continue from the highest imported ID
    var record = new Record(table);
    record.SetFieldVarChar("product_name", "New Product");
    record.SetFieldDecimal("price", 39.99M);

    long newId = manager.AddRecord(record);
    // newId will be next available ID after imported data
}

CSV File (products_with_ids.csv):

product_id,product_name,price
100,Widget,19.99
101,Gadget,29.99
102,Doohickey,39.99

Database-Specific Behavior

Different databases handle identity preservation differently when using SqlServerDatasourceConnection, PostgreSqlDatasourceConnection, and other connection types:

SQL Server:

// Requires IDENTITY_INSERT to be ON
// Velocity handles this automatically when KeepIdentity = true

PostgreSQL:

// Sequence values are automatically adjusted after import

MySQL:

// AUTO_INCREMENT continues from highest imported value

Teradata:

// Once data is imported with KeepIdentity, the identity column behavior is maintained
// Subsequent AddRecord operations will respect the identity sequence

Files With and Without Headers

With Headers (Default)

var config = new ImportConfig(table);
config.UseColumnHeaders = true;  // Default value

manager.ImportData(table, "data_with_headers.csv", config);

CSV File:

user_id,username,email,created_at
1,john_doe,john@example.com,2024-01-15
2,jane_smith,jane@example.com,2024-01-16

Without Headers

When your CSV file has no header row, you must create mappings manually in the exact column order:

var config = new ImportConfig();
config.UseColumnHeaders = false;
config.DateFormat = "yyyy-MM-dd";
config.FieldDelimiter = ',';

// Add mappings in the EXACT order they appear in the CSV
config.Mappings.AddMapping("FacilityID", "FacilityID", DataType.Integer);
config.Mappings.AddMapping("Name", "Name", DataType.VarChar);
config.Mappings.AddMapping("Address", "Address", DataType.VarChar);
config.Mappings.AddMapping("City", "City", DataType.VarChar);
config.Mappings.AddMapping("Latitude", "Latitude", DataType.Float);
config.Mappings.AddMapping("Longitude", "Longitude", DataType.Float);

manager.ImportData(table, "data_no_headers.csv", config);

CSV File (no header row):

1001,Main Hospital,123 Main St,Springfield,42.123,-72.456
1002,City Clinic,456 Oak Ave,Riverside,41.234,-71.567
1003,County Medical,789 Elm Rd,Brookfield,40.345,-70.678

Important: When UseColumnHeaders = false, the mapping names (first parameter) are ignored, but you still must provide them. The order of AddMapping calls determines which CSV column maps to which database column.

Handling Null Values

Velocity automatically handles null values in CSV imports. Empty fields or fields containing database-specific null representations are imported as NULL.

Null Value Examples

CSV with Nulls:

user_id,username,email,phone,last_login
1,john_doe,john@example.com,555-1234,2024-01-15 10:00:00
2,jane_smith,jane@example.com,,
3,bob_jones,,555-5678,2024-01-14 15:30:00

In this example:

Row 2: phone and last_login are NULL (empty fields)
Row 3: email is NULL (empty field)

Import Code for Nullable Columns

Table table = schema.CreateTable("users");
table.AddColumn("user_id", DataType.Long, autoGenerate: true, notNull: true);
table.AddColumn("username", DataType.VarChar, size: 50, notNull: true);
table.AddColumn("email", DataType.VarChar, size: 100);      // Nullable
table.AddColumn("phone", DataType.VarChar, size: 20);       // Nullable
table.AddColumn("last_login", DataType.DateTime);           // Nullable

var config = new ImportConfig(table);

using (var manager = new Manager(connection))
{
    manager.ImportData(table, "users_with_nulls.csv", config);
}

All Data Types with Nulls

Velocity supports null values for all nullable column types:

CSV Example:

BooleanColumn,ByteColumn,IntegerColumn,LongColumn,FloatColumn,DoubleColumn,DecimalColumn,DateColumn,DateTimeColumn,TimestampColumn,VarCharColumn,GeometryColumn
TRUE,1,100,1000,10.5,100.5,1000.50,2024-01-01,2024-01-01 10:00:00,2024-01-01 10:00:00 +00:00,Sample,POINT(1 1)
FALSE,,,,,,,,,,No nulls here,POINT(2 2)
,2,200,2000,20.5,200.5,2000.50,,,,Has some nulls,

Important:

Columns defined with notNull: true cannot accept null values during import
Empty strings in VARCHAR/CHAR columns are treated as empty strings, not NULL
For explicit NULL in string columns, the field must be completely empty

Field Delimiters and Special Characters

Custom Delimiters

var config = new ImportConfig();

// Tab-delimited
config.FieldDelimiter = '\t';

// Pipe-delimited
config.FieldDelimiter = '|';

// Semicolon-delimited (common in European locales)
config.FieldDelimiter = ';';

Quoted Fields

Fields containing delimiters, quotes, or newlines should be quoted:

config.QuoteCharacter = '"';
config.EscapeCharacter = '\\';

CSV with Quoted Fields:

id,name,description
1,"Smith, John","This product includes a feature called ""Advanced Mode"""
2,Basic Widget,"Simple
Multi-line
Description"

Line Terminators

// Windows-style
config.LineTerminator = "\r\n";

// Unix-style
config.LineTerminator = "\n";

// Mac-style
config.LineTerminator = "\r";

// Use system default
config.LineTerminator = Environment.NewLine;

Complete Examples

Example 1: Basic Import with Automatic Configuration

This example demonstrates using Manager with SqlServerDatasourceConnection to load a Schema and import data.

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Connections;
using YndigoBlue.Velocity.Enums;

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "products_db"
};

using (var manager = new Manager(connection))
{
    // Load the schema
    Schema schema = manager.LoadSchema("dbo");
    Table productsTable = schema["products"];

    // Import with automatic configuration - ImportConfig is created automatically
    manager.ImportData(productsTable, "products.csv");

    Console.WriteLine("Import completed successfully");
}

Example 2: Import with Custom Date Formats

This example shows custom date formats with DateFormatType.

var connection = new MySqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 3306,
    Username = "root",
    Password = "password",
    Database = "events_db"
};

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("events_db");
    Table eventsTable = schema["events"];

    // Create config with custom date formats
    var config = new ImportConfig(eventsTable, DateFormatType.Iso);
    config.DateTimeFormat = "MM/dd/yyyy hh:mm tt";  // US format with AM/PM
    config.DateFormat = "MM/dd/yyyy";

    manager.ImportData(eventsTable, "events_us_format.csv", config);
}

Example 3: Import with Manual Column Mapping

This example uses manual mappings with AddMapping.

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("public");
    Table customersTable = schema["customers"];

    // Create config with manual mappings
    var config = new ImportConfig(DateFormatType.Iso);

    // CSV headers don't match database column names
    config.Mappings.AddMapping("Customer ID", "customer_id", DataType.Long);
    config.Mappings.AddMapping("Full Name", "customer_name", DataType.VarChar);
    config.Mappings.AddMapping("E-mail", "email", DataType.VarChar);
    config.Mappings.AddMapping("Registration", "registered_at", DataType.DateTime);

    manager.ImportData(customersTable, "customers_export.csv", config);
}

Example 4: Import Geospatial Data with Identity Values

This example demonstrates PostgreSqlDatasourceConnection with geospatial data using Schema and Table.

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "gis_db"
};

using (var manager = new Manager(connection))
{
    // Create schema with geospatial table
    Schema schema = Schema.NewSchema("locations");
    Table locationsTable = schema.CreateTable("points_of_interest");

    locationsTable.AddColumn("poi_id", DataType.Long, autoGenerate: true, notNull: true);
    locationsTable.AddColumn("name", DataType.VarChar, size: 200, notNull: true);
    locationsTable.AddColumn("location", DataType.Geography, notNull: true);
    locationsTable.CreatePrimaryKey("pk_poi", "poi_id");
    locationsTable.CreateSpatialIndex("spi_poi_location", "location");

    // Deploy schema
    manager.BuildSchema(schema, overwrite: true);

    // Import with identity preservation
    var config = new ImportConfig(locationsTable, keepIdentity: true);

    manager.ImportData(locationsTable, "poi_data.csv", config);

    Console.WriteLine("Geospatial data imported with preserved IDs");
}

CSV File (poi_data.csv):

poi_id,name,location
1,Golden Gate Bridge,POINT(-122.4783 37.8199)
2,Statue of Liberty,POINT(-74.0445 40.6892)
3,Space Needle,POINT(-122.3493 47.6205)
4,Willis Tower,POINT(-87.6359 41.8789)

Example 5: Import File Without Headers

This example shows importing CSV files without headers using LoadSchema.

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("analytics");
    Table metricsTable = schema["daily_metrics"];

    var config = new ImportConfig();
    config.UseColumnHeaders = false;
    config.DateFormat = "yyyy-MM-dd";
    config.FieldDelimiter = ',';

    // Define mappings in column order as they appear in CSV
    for (int i = 0; i < metricsTable.Columns.Count; i++)
    {
        Column column = metricsTable[i];
        if (!column.AutoGenerate)
        {
            config.Mappings.AddMapping(column.Name, column.Name, column.DataType);
        }
    }

    manager.ImportData(metricsTable, "metrics_no_headers.csv", config);
}

Example 6: Large File Import with Mixed Data Types

This example uses ImportConfig with timestamp formats.

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("analytics");
    Table sensorDataTable = schema["sensor_readings"];

    var config = new ImportConfig(sensorDataTable, DateFormatType.Iso);

    // Customize timestamp format
    config.TimestampFormat = "yyyy-MM-dd HH:mm:ss zzz";

    // Import large file
    manager.ImportData(sensorDataTable, "sensor_data_2024.csv", config);

    Console.WriteLine("Large dataset imported successfully");
}

CSV File (sensor_data_2024.csv):

sensor_id,reading_timestamp,temperature,humidity,location
S001,2024-01-01 00:00:00 +00:00,22.5,65.3,POINT(-122.4 37.8)
S001,2024-01-01 01:00:00 +00:00,22.3,66.1,POINT(-122.4 37.8)
S002,2024-01-01 00:00:00 +00:00,21.8,68.5,POINT(-118.2 34.1)
S002,2024-01-01 01:00:00 +00:00,21.9,67.9,POINT(-118.2 34.1)

Example 7: Import with Tab Delimiter and European Format

var connection = new SqliteDatasourceConnection
{
    FilePath = "./data/sales.db"
};

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("main");
    Table salesTable = schema["sales"];

    var config = new ImportConfig(salesTable, DateFormatType.Culture);
    config.FieldDelimiter = '\t';  // Tab-delimited
    config.DateFormat = "dd.MM.yyyy";
    config.FieldDelimiter = ';';  // Common in European CSV

    manager.ImportData(salesTable, "european_sales.csv", config);
}

CSV File (european_sales.csv):

sale_id;customer_name;sale_date;amount
1;Müller GmbH;15.01.2024;1234,56
2;Société Générale;16.01.2024;2345,67

Best Practices

1. Use ISO Date Formats for Portability

// Recommended: ISO format works across all systems
var config = new ImportConfig(table, DateFormatType.Iso);

ISO formats (DateFormatType.Iso) are unambiguous and work consistently across different database systems and locales.

2. Validate Data Before Import

// Check if file exists
if (!File.Exists(csvFilePath))
{
    throw new FileNotFoundException($"CSV file not found: {csvFilePath}");
}

// Verify file is not empty
var fileInfo = new FileInfo(csvFilePath);
if (fileInfo.Length == 0)
{
    throw new InvalidOperationException("CSV file is empty");
}

3. Use Automatic Configuration When Possible

Let ImportData create the ImportConfig automatically when possible:

// Best: Let Velocity create the ImportConfig automatically
manager.ImportData(table, "data.csv");

// Good: Explicit config when you need it
var config = new ImportConfig(table);
manager.ImportData(table, "data.csv", config);

// Avoid: Manual mapping when not necessary
var config = new ImportConfig();
foreach (var column in table.Columns)
{
    config.Mappings.AddMapping(...);  // Unnecessary work
}

4. Handle Large Files Appropriately

For very large CSV files, consider:

Importing during off-peak hours
Using database-specific bulk load features
Monitoring transaction log growth
Ensuring adequate disk space

5. Preserve Identity Values Only When Necessary

// Use KeepIdentity sparingly - only for migrations or specific requirements
var config = new ImportConfig(table, keepIdentity: true);

Most scenarios should allow the database to auto-generate identity values.

6. Test Imports on Sample Data First

// Test with a small subset first
using (var manager = new Manager(connection))
{
    // Import sample
    manager.ImportData(table, "sample_100_rows.csv", config);

    // Verify results
    var query = new Query(table).Limit(10);
    var results = manager.Retrieve(query);

    // If successful, import full dataset
    manager.ImportData(table, "full_dataset.csv", config);
}

7. Use Transactions for Data Integrity

The ImportData method wraps the import in a transaction by default (ImportTransaction = true), ensuring all-or-nothing behavior:

using (var manager = new Manager(connection))
{
    try
    {
        manager.ImportData(table, "data.csv", config);
        // All data imported successfully
    }
    catch (Exception ex)
    {
        // Transaction automatically rolled back
        Console.WriteLine($"Import failed: {ex.Message}");
    }
}

Set ImportTransaction = false on the connection when you are managing transactions externally or when the target table is guaranteed to be empty and a rollback is unnecessary. On Teradata this specifically controls whether the staging-to-target merge runs inside a transaction.

8. Document Your Import Configurations

Create reusable ImportConfig methods for consistency:

// Create reusable import configurations
public static class ImportConfigurations
{
    public static ImportConfig ForCustomerData(Table customersTable)
    {
        var config = new ImportConfig(customersTable, DateFormatType.Iso);
        config.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
        return config;
    }

    public static ImportConfig ForEuropeanSales(Table salesTable)
    {
        var config = new ImportConfig(salesTable);
        config.FieldDelimiter = ';';
        config.DateFormat = "dd.MM.yyyy";
        return config;
    }
}

Troubleshooting

Common Import Errors

Error: "Invalid Import Mappings"

Ensure all non-nullable columns are mapped
Verify column names match exactly (case-sensitive)
Check that data types are compatible

Error: "Value Set For Auto Generated Column"

Set KeepIdentity = true if you need to import identity values
Or remove the autogenerated column from your CSV

Error: "Date/Time Format Exception"

Verify DateFormat, DateTimeFormat, or TimestampFormat matches your CSV data
Check for timezone information in timestamp columns

Error: "Geometry Parse Error"

Ensure WKT format is correct (e.g., POINT(lon lat))
Quote complex geometries that contain commas
Verify coordinate order (longitude, then latitude for geographic data)

Error: "Null Value in NOT NULL Column"

Check for empty fields in required columns
Verify CSV data completeness
Ensure notNull: true columns always have values

Summary

Velocity's import functionality provides:

Zero-Configuration Imports - Simply call ImportData for standard imports
Flexible Configuration - Control every aspect of CSV interpretation when needed
Automatic Mapping - ImportConfig created automatically from table schema
Type Safety - Proper handling of all DataType values including spatial data
Format Support - Customizable DateFormatType formats and delimiters
Identity Control - Choose whether to preserve or generate identity values
Null Handling - Automatic null value detection and import
Transaction Safety - All-or-nothing import with automatic rollback on error

By following these guidelines and examples, you can efficiently import data from CSV files into any supported database system with confidence and consistency.

Table of Contents

Importing Data

Introduction

Basic Import Operation

Simple Import Example

The ImportConfig Class

Properties

Constructor Options

Date and Time Formats

Date Formats

DateTime Formats

Timestamp Formats (with Timezone)

Column Mappings

Automatic Mapping (No Configuration Required)

When to Provide an Explicit ImportConfig

Manual Mapping

Programmatic Mapping

Mapping Operations

Importing Geospatial Data

Supported Geometry Types

Point Data Example

Complex Geometry Example

Geospatial Data Notes

Teradata Bulk Import: Staging Tables

How It Works

Controlling Clob and Geospatial Casting

Note

Choosing the right strategy for large data

Transaction Control

Note

Handling Autogenerated Columns (Identity)

Default Behavior (KeepIdentity = false)

Importing Identity Values (KeepIdentity = true)

Database-Specific Behavior

Files With and Without Headers

With Headers (Default)

Without Headers

Handling Null Values

Null Value Examples

Import Code for Nullable Columns

All Data Types with Nulls

Field Delimiters and Special Characters

Custom Delimiters

Quoted Fields

Line Terminators

Complete Examples

Example 1: Basic Import with Automatic Configuration

Example 2: Import with Custom Date Formats

Example 3: Import with Manual Column Mapping

Example 4: Import Geospatial Data with Identity Values

Example 5: Import File Without Headers

Example 6: Large File Import with Mixed Data Types

Example 7: Import with Tab Delimiter and European Format

Best Practices

1. Use ISO Date Formats for Portability

2. Validate Data Before Import

3. Use Automatic Configuration When Possible

4. Handle Large Files Appropriately

5. Preserve Identity Values Only When Necessary

6. Test Imports on Sample Data First

7. Use Transactions for Data Integrity

8. Document Your Import Configurations

Troubleshooting

Common Import Errors

Summary