Exporting Data

Introduction

Velocity provides flexible data export capabilities, allowing you to extract data from tables, views, and query results into CSV files. You can also export entire schemas or databases with both structure and data for backup, migration, or analysis purposes.

Basic Export Operation

At its simplest, exporting data requires just two parameters:

A data source (Table, View, or Query)
A file path for the output CSV

Simple Export Example

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Data.PostgreSql;

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"];

    // Export data - ExportConfig is created automatically
    manager.ExportData(usersTable, "users_export.csv");
}

The ExportConfig Class

The ExportConfig class controls how data is formatted when exported to CSV.

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
`EscapeCharacter`	`string`	Character sequence for escaping	`"\\\\"`	`"\\\\"`
`FieldDelimiter`	`char`	Character separating fields	`','`	Culture list separator
`ForceQualifier`	`bool`	Quote all fields (not just those with special chars)	`false`	`false`
`IntervalFormat`	`string`	Format string for Interval columns	`"c"`	`"c"`
`LineTerminator`	`string`	Line ending sequence	`Environment.NewLine`	`Environment.NewLine`
`QuoteCharacter`	`char`	Character for quoting fields	`'"'`	`'"'`
`TimeFormat`	`string`	Format string for Time columns	`"HH:mm:ss"`	Culture-specific
`TimestampFormat`	`string`	Format string for Timestamp columns	`"yyyy-MM-dd hh:mm:ss zzz"`	Culture-specific
`TrimChars`	`bool`	Trim leading/trailing whitespace from strings	`true`	`true`
`WriteHeaders`	`bool`	Include column names as first row	`true`	`true`

Constructor Options

Default Constructor:

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

With Date Format Type:

// ISO format (recommended for portability)
var config = new ExportConfig(DateFormatType.Iso);

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

The DateFormatType enum controls whether dates use ISO or culture-specific formatting.

Automatic Creation:

// If you don't provide an ExportConfig, one is created automatically with ISO defaults
manager.ExportData(table, "output.csv");  // Uses new ExportConfig()

If you don't provide an ExportConfig, the ExportData method creates one automatically with ISO defaults.

Exporting Tables

Export all data from a Table to CSV:

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

    // Simple export with defaults
    manager.ExportData(ordersTable, "orders.csv");
}

Resulting CSV:

order_id,customer_id,order_date,total_amount,status
1001,5001,2024-01-15,249.99,shipped
1002,5002,2024-01-16,125.50,pending
1003,5001,2024-01-17,899.00,shipped

Export with Custom Configuration

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

    var config = new ExportConfig(DateFormatType.Iso);
    config.FieldDelimiter  = ';';                        // Use semicolon instead of comma
    config.DateFormat      = "dd/MM/yyyy";
    config.DateTimeFormat  = "dd/MM/yyyy HH:mm:ss";
    config.IntervalFormat  = "c";
    config.TimeFormat      = "HH:mm:ss";
    config.TimestampFormat = "dd/MM/yyyy HH:mm:ss zzz";
    config.WriteHeaders    = false;                      // Omit header row

    manager.ExportData(ordersTable, "orders_no_headers.csv", config);
}

Resulting CSV (no headers):

1001;5001;15/01/2024;249.99;shipped
1002;5002;16/01/2024;125.50;pending
1003;5001;17/01/2024;899.00;shipped

Exporting Views

Export data from a database View:

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("analytics");
    View salesSummaryView = schema.Views["sales_summary"];

    // Export view data
    manager.ExportData(salesSummaryView, "sales_summary.csv");
}

Views are exported exactly like Tables—all visible columns are included in the output.

Exporting Query Results

One of the most powerful features is exporting the results of custom queries using Query:

Basic Query Export

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

    // Build a query
    Query query = new Query()
        .SelectAll(ordersTable)
        .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "shipped"));

    // Export query results
    manager.ExportData(query, "shipped_orders.csv");
}

Advanced Query Export with Joins

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

    // Build complex query with join
    var query = new Query()
        .Select([
            ordersTable["order_id"],
            ordersTable["order_date"],
            ordersTable["total_amount"],
            customersTable["customer_name"],
            customersTable["email"]
        ])
        .From(new Join(ordersTable, customersTable, "customer_id"));

    // Export joined data
    manager.ExportData(query, "orders_with_customers.csv");
}

Resulting CSV:

order_id,order_date,total_amount,customer_name,email
1001,2024-01-15,249.99,John Smith,john@example.com
1002,2024-01-16,125.50,Jane Doe,jane@example.com
1003,2024-01-17,899.00,John Smith,john@example.com

Filtered and Aggregated Exports

var countExpr = new Expression(new Aggregate(AggregateType.Count, ordersTable["order_id"]));

var query = new Query()
    .Select([
        customersTable["customer_name"],
        new Expression("total_orders", new Aggregate(AggregateType.Count, ordersTable["order_id"])),
        new Expression("total_spent",  new Aggregate(AggregateType.Sum,   ordersTable["total_amount"]))
    ])
    .From(new Join(ordersTable, customersTable, "customer_id"))
    .GroupBy(customersTable["customer_name"])
    .Having(new Criterion<int>(countExpr, ConditionalType.GreaterThan, 5));

manager.ExportData(query, "top_customers.csv");

Date and Time Formatting

Control how temporal data appears in the exported CSV. Velocity supports five temporal types, each with its own format string on ExportConfig:

Property	Applies to	ISO Default	Culture Default
`DateFormat`	`DataType.Date`	`"yyyy-MM-dd"`	`ShortDatePattern`
`DateTimeFormat`	`DataType.DateTime`	`"yyyy-MM-dd hh:mm:ss"`	`FullDateTimePattern`
`IntervalFormat`	`DataType.Interval`	`"c"`	`"c"`
`TimeFormat`	`DataType.Time`	`"HH:mm:ss"`	`ShortTimePattern`
`TimestampFormat`	`DataType.Timestamp`	`"yyyy-MM-dd hh:mm:ss zzz"`	`RFC1123Pattern`

ISO Format (Recommended)

var config = new ExportConfig(DateFormatType.Iso);
// DateFormat:      "yyyy-MM-dd"
// DateTimeFormat:  "yyyy-MM-dd hh:mm:ss"
// IntervalFormat:  "c"
// TimeFormat:      "HH:mm:ss"
// TimestampFormat: "yyyy-MM-dd hh:mm:ss zzz"

manager.ExportData(table, "data_iso.csv", config);

CSV Output:

event_id,event_name,event_date,start_time,duration,created_at,last_updated
1,Conference,2024-06-15,09:00:00,08:00:00,2024-01-15 09:30:00,2024-01-15 09:30:00 +00:00

Date

Controls the format of DataType.Date column values in the CSV output. ISO default is "yyyy-MM-dd".

Custom Formats:

config.DateFormat = "MM/dd/yyyy";     // US format:       06/15/2024
config.DateFormat = "dd/MM/yyyy";     // European format: 15/06/2024
config.DateFormat = "dd-MMM-yyyy";    // Abbreviated:     15-Jun-2024
config.DateFormat = "yyyyMMdd";       // Compact:         20240615

DateTime

Controls the format of DataType.DateTime column values (date and time without a timezone offset). ISO default is "yyyy-MM-dd hh:mm:ss". Culture mode uses the system's FullDateTimePattern.

Custom Formats:

config.DateTimeFormat = "MM/dd/yyyy HH:mm";        // US short:    06/15/2024 09:30
config.DateTimeFormat = "dd/MM/yyyy HH:mm:ss";     // European:    15/06/2024 09:30:00
config.DateTimeFormat = "yyyy-MM-dd'T'HH:mm:ss";   // ISO 8601:    2024-06-15T09:30:00

Interval

Controls the format of DataType.Interval column values, which represent a duration rather than a point in time. The default is "c" — the .NET constant (invariant) TimeSpan format — and is the same for both ISO and Culture modes.

The "c" format produces values in the form [-][d.]hh:mm:ss[.fffffff]:

Value	Meaning
`00:30:00`	30 minutes
`01:15:00`	1 hour 15 minutes
`1.02:03:04`	1 day, 2 hours, 3 minutes, 4 seconds
`-00:15:00`	Negative 15 minutes

IntervalFormat rarely needs to be changed as "c" is universally portable across all supported databases.

Time

Controls the format of DataType.Time column values in the CSV output. ISO default is "HH:mm:ss" (24-hour). Culture mode uses the system's ShortTimePattern.

Custom Formats:

config.TimeFormat = "hh:mm:ss tt";   // 12-hour with AM/PM: 09:00:00 AM
config.TimeFormat = "HH:mm";         // Hours and minutes only: 09:00

Timestamp

Controls the format of DataType.Timestamp column values, which include a timezone offset. ISO default is "yyyy-MM-dd hh:mm:ss zzz". Culture mode uses the system's RFC1123Pattern.

The zzz specifier outputs the timezone offset as +HH:mm or -HH:mm.

Custom Formats:

config.TimestampFormat = "yyyy-MM-dd HH:mm:ss K";     // Z or ±HH:mm offset
config.TimestampFormat = "yyyy-MM-dd'T'HH:mm:sszzz";  // ISO 8601

Culture-Specific Formatting

When using DateFormatType.Culture, each format property is derived from the current thread's CultureInfo rather than a fixed ISO string. This makes exported values immediately readable in the user's locale but reduces portability across systems with different regional settings.

var config = new ExportConfig(DateFormatType.Culture);
manager.ExportData(table, "data_culture.csv", config);

The culture source for each property is:

Property	Culture Source	en-US Example	en-GB Example
`DateFormat`	`DateTimeFormat.ShortDatePattern`	`M/d/yyyy`	`dd/MM/yyyy`
`DateTimeFormat`	`DateTimeFormat.FullDateTimePattern`	`dddd, MMMM d, yyyy h:mm:ss tt`	`dd MMMM yyyy HH:mm:ss`
`IntervalFormat`	(invariant)	`c`	`c`
`TimeFormat`	`DateTimeFormat.ShortTimePattern`	`h:mm tt`	`HH:mm`
`TimestampFormat`	`DateTimeFormat.RFC1123Pattern`	`ddd, dd MMM yyyy HH':'mm':'ss 'GMT'`	`ddd, dd MMM yyyy HH':'mm':'ss 'GMT'`

Note

IntervalFormat is always "c" regardless of culture. TimestampFormat uses the RFC 1123 pattern which is the same across all cultures.

CSV Output (en-US culture):

event_id,event_name,event_date,start_time,created_at,last_updated
1,Conference,6/15/2024,9:00 AM,"Saturday, June 15, 2024 9:30:00 AM","Sat, 15 Jun 2024 09:30:00 GMT"

Use DateFormatType.Iso for files intended for cross-system use, automated processing, or round-tripping back into Velocity.

Exporting Geospatial Data

Geospatial columns are exported in Well-Known Text (WKT) format:

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("locations");
    Table storesTable = schema["stores"];

    manager.ExportData(storesTable, "stores_with_locations.csv");
}

CSV Output:

store_id,store_name,address,location
1,Downtown Store,123 Main St,POINT(-122.4194 37.7749)
2,Airport Store,456 Airport Rd,POINT(-122.3748 37.6213)
3,Marina Store,789 Bay St,POINT(-122.4389 37.8025)

The WKT format is compatible with most GIS applications and can be re-imported using Velocity's import functionality.

Teradata: Clob and Geospatial Column Casting on Export

Teradata requires special handling for Clob and geospatial columns during export because the native types cannot be read directly into a .NET DataTable. Velocity converts these columns at the SQL level before reading results:

Geospatial columns are converted to WKT using Teradata's column.ST_AsText() method. When SelectCastClob is enabled (the default), the result is additionally wrapped in CAST(... AS VARCHAR(VarCharCastSize)) to produce a fixed-width string the driver can return.
Clob columns are cast to VARCHAR(VarCharCastSize) when SelectCastClob is enabled.

Both casts use the same VarCharCastSize value (default 32000). If your Clob data or geospatial WKT strings exceed this length, increase the value before exporting:

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

connection.VarCharCastSize = 65000;  // Increase for large Clobs or complex geometries
// connection.SelectCastClob = true; // Default — set to false only if columns are already VARCHAR

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

    manager.ExportData(regionsTable, "regions_export.csv");
}

Note

Setting SelectCastClob = false disables the VARCHAR cast for both Clob and geospatial columns on SELECT. Only do this if the columns have already been cast or if the underlying data fits within the driver's default handling.

Exporting Blob Data

By default, blob columns (DataType.Blob) are exported as empty CSV cells — writing raw binary bytes into a CSV would corrupt the file. Velocity provides two strategies for exporting blobs, configured per column on ExportConfig.

Base64 Encoding

Call SetBlobColumnBase64 to write the blob as an inline Base64 string in the CSV cell:

var config = new ExportConfig();
config.SetBlobColumnBase64("Data");

manager.ExportData(attachmentsTable, "attachments.csv", config);

Resulting CSV:

AttachmentID,Name,Data
1,logo.png,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg==
2,readme.txt,SGVsbG8gV29ybGQ=

File-Based Export

Call SetBlobColumnFile to write each blob to a separate file on disk. The path pattern may reference any other column in the row using {ColumnName} tokens. The resolved relative path is written into the CSV cell, and all files are written relative to the directory containing the CSV.

var config = new ExportConfig();
config.SetBlobColumnFile("Data", "files/{Category}/{Filename}");

manager.ExportData(documentsTable, "./export/documents.csv", config);

Resulting CSV:

DocumentID,Category,Filename,Data
1,reports,q1_summary.pdf,files/reports/q1_summary.pdf
2,images,product_shot.jpg,files/images/product_shot.jpg

The blob bytes for row 1 are written to ./export/files/reports/q1_summary.pdf. Any intermediate directories are created automatically.

Exporting Blobs from a Schema or Database

The same ExportConfig is used whether you are exporting a single table, a whole schema, or an entire database. Because different tables may have blob columns with the same name but requiring different handling, ExportConfig supports three scoping levels for blob configuration.

Global (applies to every table)

Use the one-argument overloads when all tables should handle blobs the same way:

var config = new ExportConfig();
config.SetBlobColumnBase64("Data");      // every table's "Data" column → Base64
config.SetBlobColumnBase64("Thumbnail"); // every table's "Thumbnail" column → Base64

manager.ExportSchema(schema, ConfigType.Json, "./export/myschema", config);

Table-Scoped (applies to a specific table)

Use the two-argument overloads when tables within the same schema need different handling for a column with the same name:

var config = new ExportConfig();
// "documents" table: write blobs as files
config.SetBlobColumnFile("documents", "Data", "blobs/{Category}/{Filename}");
// "thumbnails" table: write blobs as Base64 inline
config.SetBlobColumnBase64("thumbnails", "Data");

manager.ExportSchema(schema, ConfigType.Xml, "./export/myschema", config);

This is the preferred approach when exporting a schema that contains multiple tables with identically-named blob columns but different storage requirements.

Schema+Table-Scoped (applies to a specific schema and table)

Use the three-argument overloads when exporting an entire database where multiple schemas each contain a table with the same name:

var config = new ExportConfig();
// "hr" schema — documents table stores files
config.SetBlobColumnFile("hr", "documents", "Data", "hr-docs/{Category}/{Filename}");
// "sales" schema — documents table stores Base64 thumbnails
config.SetBlobColumnBase64("sales", "documents", "Data");

Database database = manager.LoadDatabase();
manager.ExportDatabase(database, ConfigType.Json, "./export/full_backup", config);

This is the most specific level and takes precedence over both the table-scoped and global configurations.

Resolution Order

When ExportConfig resolves the blob config for a column, it checks the three levels in order and uses the first match:

Schema + Table — SetBlobColumnBase64(schemaName, tableName, columnName)
Table — SetBlobColumnBase64(tableName, columnName)
Global — SetBlobColumnBase64(columnName)

If no configuration is found at any level, the column is exported as an empty cell.

Blob Export Reference

Method	Scope	Description
`SetBlobColumnBase64(column)`	Global	Any table with this column name → inline Base64
`SetBlobColumnFile(column, pattern)`	Global	Any table with this column name → file on disk
`SetBlobColumnBase64(table, column)`	Table	Named table only → inline Base64
`SetBlobColumnFile(table, column, pattern)`	Table	Named table only → file on disk
`SetBlobColumnBase64(schema, table, column)`	Schema+Table	Named schema+table only → inline Base64
`SetBlobColumnFile(schema, table, column, pattern)`	Schema+Table	Named schema+table only → file on disk

Note

Import and export blob configurations are intentionally separate. ImportConfig uses only global (column-name) scoping because import is always directed at a single, explicitly identified table. ExportConfig supports all three levels because schema and database exports span many tables.

Field Delimiters and Formatting

Custom Delimiters

var config = new ExportConfig();

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

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

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

manager.ExportData(table, "data.csv", config);

Quote Behavior

var config = new ExportConfig();

// Only quote fields containing special characters (default)
config.ForceQualifier = false;

// Quote all fields
config.ForceQualifier = true;

manager.ExportData(table, "data.csv", config);

Without ForceQualifier:

id,name,description
1,Widget,Simple product
2,Gadget,"Contains, comma"
3,Tool,Basic item

With ForceQualifier:

"id","name","description"
"1","Widget","Simple product"
"2","Gadget","Contains, comma"
"3","Tool","Basic item"

String Trimming

var config = new ExportConfig();

// Remove leading/trailing whitespace (default)
config.TrimChars = true;

// Preserve whitespace
config.TrimChars = false;

manager.ExportData(table, "data.csv", config);

Exporting Schemas and Databases

Velocity can export entire schemas or databases, including both structure (DDL) and data using the Manager class.

Export Single Schema

Export a Schema with its structure and all table data:

using (var manager = new Manager(connection))
{
    // Load the schema
    Schema schema = manager.LoadSchema("sales");

    // Export to a directory as JSON
    manager.ExportSchema(schema, ConfigType.Json, "./export/sales");
}

This creates:

schema.json - Schema structure (tables, columns, constraints, indexes)
customers.csv - Customer data
orders.csv - Order data
products.csv - Product data
(one CSV per table)

Export to Different Formats

// Export as XML
manager.ExportSchema(schema, ConfigType.Xml, "./export/sales_xml");

// Export as YAML
manager.ExportSchema(schema, ConfigType.Yaml, "./export/sales_yaml");

Export with Custom Data Format

var config = new ExportConfig(DateFormatType.Iso);
config.FieldDelimiter = '\t';
config.WriteHeaders = true;

manager.ExportSchema(schema, ConfigType.Json, "./export/sales", config);

Include Views in Schema Export

// Export schema including view definitions and data
manager.ExportSchema(schema, ConfigType.Json, "./export/sales", includeViews: true);

Export Entire Database

Export all schemas in a database:

using (var manager = new Manager(connection))
{
    // Load entire database
    Database database = manager.LoadDatabase();

    // Export everything as JSON
    manager.ExportDatabase(database, ConfigType.Json, "./export/full_backup");
}

This creates:

database.json - Database structure with all schemas
schema1/ directory
- table1.csv
- table2.csv
schema2/ directory
- table1.csv
- table2.csv

Selective Database Export

using (var manager = new Manager(connection))
{
    Database database = manager.LoadDatabase();

    var config = new ExportConfig(DateFormatType.Iso);

    // Export with views included
    manager.ExportDatabase(database, ConfigType.Xml, "./backup", config, includeViews: true);
}

Complete Examples

Example 1: Basic Table Export

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Data.SqlServer;

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

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

    // Simple export - uses default ExportConfig
    manager.ExportData(productsTable, "products_export.csv");

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

Example 2: Export with Custom Formatting

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

    // Create custom export configuration
    var config = new ExportConfig(DateFormatType.Iso);
    config.DateFormat      = "yyyy-MM-dd";
    config.DateTimeFormat  = "yyyy-MM-dd HH:mm:ss";
    config.FieldDelimiter  = '\t';                       // Tab-delimited
    config.ForceQualifier  = true;                       // Quote all fields
    config.IntervalFormat  = "c";
    config.TimeFormat      = "HH:mm:ss";
    config.TimestampFormat = "yyyy-MM-dd HH:mm:ss zzz";
    config.WriteHeaders    = true;

    manager.ExportData(ordersTable, "orders_formatted.csv", config);
}

Example 3: Export Filtered Data

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

    // Export only recent orders
    Query recentOrders = new Query()
        .SelectAll(ordersTable)
        .Where(new Criterion<DateTime>(ordersTable["order_date"], ConditionalType.GreaterThan, DateTime.Now.AddMonths(-3)))
        .OrderBy(ordersTable["order_date"], OrderClauseType.Descending);

    manager.ExportData(recentOrders, "recent_orders.csv");
}

Example 4: Export Aggregated Report

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    Table ordersTable = schema["orders"];
    Table productsTable = schema["products"];

    // Create sales report query
    var unitsExpr   = new Expression("units_sold", new Aggregate(AggregateType.Count, ordersTable["order_id"]));
    var revenueExpr = new Expression("revenue",    new Aggregate(AggregateType.Sum,   ordersTable["total_amount"]));

    var salesReport = new Query()
        .Select([
            productsTable["product_name"],
            unitsExpr,
            revenueExpr
        ])
        .From(new Join(ordersTable, productsTable, "product_id"))
        .GroupBy(productsTable["product_name"])
        .OrderBy(revenueExpr, OrderClauseType.Descending);

    var config = new ExportConfig();
    config.DateFormat      = "yyyy-MM-dd";
    config.DateTimeFormat  = "yyyy-MM-dd HH:mm:ss";
    config.IntervalFormat  = "c";
    config.TimeFormat      = "HH:mm:ss";
    config.TimestampFormat = "yyyy-MM-dd HH:mm:ss zzz";

    manager.ExportData(salesReport, "product_sales_report.csv", config);
}

Example 5: Export Geospatial Data

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("gis");
    Table locationsTable = schema["store_locations"];

    // Export with geospatial data in WKT format
    manager.ExportData(locationsTable, "stores_geo.csv");
}

Resulting CSV:

store_id,store_name,city,coordinates
1,Main Street Store,San Francisco,POINT(-122.4194 37.7749)
2,Downtown Branch,Oakland,POINT(-122.2711 37.8044)
3,Marina Location,Berkeley,POINT(-122.2728 37.8716)

Example 6: Export for European Systems

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

    // European CSV format (semicolon delimiter, custom dates)
    var config = new ExportConfig(DateFormatType.Iso);
    config.FieldDelimiter  = ';';
    config.DateFormat      = "dd.MM.yyyy";
    config.DateTimeFormat  = "dd.MM.yyyy HH:mm:ss";
    config.IntervalFormat  = "c";
    config.TimeFormat      = "HH:mm:ss";
    config.TimestampFormat = "dd.MM.yyyy HH:mm:ss zzz";

    manager.ExportData(salesTable, "sales_european.csv", config);
}

Resulting 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

Example 7: Full Database Backup

using (var manager = new Manager(connection))
{
    // Load entire database structure
    Database database = manager.LoadDatabase();

    // Export configuration
    var config = new ExportConfig(DateFormatType.Iso);
    config.WriteHeaders = true;

    // Export everything to a directory
    string backupPath = $"./backups/{DateTime.Now:yyyyMMdd_HHmmss}";
    Directory.CreateDirectory(backupPath);

    manager.ExportDatabase(database, ConfigType.Json, backupPath, config, includeViews: true);

    Console.WriteLine($"Database backed up to {backupPath}");
}

Example 8: Export Without Headers for Import to Legacy System

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("legacy");
    Table dataTable = schema["legacy_data"];

    var config = new ExportConfig();
    config.WriteHeaders    = false;             // No header row
    config.FieldDelimiter  = '|';               // Pipe delimiter
    config.DateFormat      = "yyyyMMdd";        // Compact date format
    config.DateTimeFormat  = "yyyyMMddHHmmss";
    config.IntervalFormat  = "c";
    config.TimeFormat      = "HHmmss";
    config.TimestampFormat = "yyyyMMddHHmmsszzz";
    config.TrimChars       = true;

    manager.ExportData(dataTable, "legacy_import.dat", config);
}

Export Methods Reference

Velocity provides several overloaded ExportData methods:

Export from Table

void ExportData(Table table, string dataFilePath, ExportConfig exportConfig = null)

Exports all data from a Table.

Export from View

void ExportData(View view, string dataFilePath, ExportConfig exportConfig = null)

Exports all data from a View.

Export from Query

void ExportData(Query query, string dataFilePath, ExportConfig exportConfig = null)

Exports the results of a Query. Note: The query is executed once to determine column types, then executed again to retrieve the data.

Export Schema

void ExportSchema(Schema schema, ConfigType configType, string dataPath,
                  ExportConfig exportConfig = null, bool includeViews = false)

Exports a Schema's structure and data to a directory.

Parameters:

schema - The Schema to export
configType - Format for schema file (Xml, Json, or Yaml)
dataPath - Directory path for output files
exportConfig - Optional ExportConfig for CSV formatting
includeViews - Whether to include view definitions and data

Export Database

void ExportDatabase(Database database, ConfigType configType, string dataPath,
                    ExportConfig exportConfig = null, bool includeViews = false)

Exports an entire database (all schemas) with structure and data.

Parameters:

database - The database to export
configType - Format for database file (Xml, Json, or Yaml)
dataPath - Directory path for output files
exportConfig - Optional ExportConfig for CSV formatting
includeViews - Whether to include view definitions and data

Best Practices

1. Use ISO Format for Portability

// ISO format works consistently across systems and locales
var config = new ExportConfig(DateFormatType.Iso);
manager.ExportData(table, "data.csv", config);

Use Iso format for maximum portability across different systems and locales.

2. Include Headers for Clarity

// Headers make the CSV self-documenting
var config = new ExportConfig();
config.WriteHeaders = true;  // Default, but be explicit

3. Use Queries for Selective Exports

// Export only what you need
var query = new Query()
    .Select([table["id"], table["name"], table["status"]])
    .From(table)
    .Where(new Criterion<bool>(table["active"], ConditionalType.Equals, true));

manager.ExportData(query, "active_users.csv");

Use Query to export only the data you need rather than entire tables.

4. Validate File Paths

string exportPath = "./exports";
if (!Directory.Exists(exportPath))
{
    Directory.CreateDirectory(exportPath);
}

string filePath = Path.Combine(exportPath, "data.csv");
manager.ExportData(table, filePath);

5. Handle Large Exports Appropriately

// For very large tables, consider exporting during off-peak hours
// or using queries to export in batches

// Example: Export by date range
for (int month = 1; month <= 12; month++)
{
    var startDate = new DateTime(2024, month, 1);
    var endDate = startDate.AddMonths(1);

    var query = new Query()
        .SelectAll(ordersTable)
        .Where(new Criterion<DateTime>(ordersTable["order_date"], ConditionalType.GreaterThanOrEqualTo, startDate))
        .Where(new Criterion<DateTime>(ordersTable["order_date"], ConditionalType.LessThan, endDate));

    manager.ExportData(query, $"orders_2024_{month:D2}.csv");
}

6. Use Consistent Formats for Data Exchange

// Create reusable export configurations
public static class ExportConfigurations
{
    public static ExportConfig StandardExport()
    {
        var config = new ExportConfig(DateFormatType.Iso);
        config.WriteHeaders = true;
        config.TrimChars = true;
        return config;
    }

    public static ExportConfig EuropeanExport()
    {
        var config = new ExportConfig();
        config.FieldDelimiter = ';';
        config.DateFormat = "dd.MM.yyyy";
        return config;
    }
}

// Use them consistently
manager.ExportData(table, "data.csv", ExportConfigurations.StandardExport());

7. Test Exports with Small Datasets First

// Test export with limited data (constrain rows with a WHERE clause)
var testQuery = new Query()
    .SelectAll(largeTable)
    .Where(new Criterion<int>(largeTable["id"], ConditionalType.LessThanOrEqualTo, 100));

manager.ExportData(testQuery, "test_export.csv");

// Verify the output, then export full dataset
manager.ExportData(largeTable, "full_export.csv");

8. Document Your Export Format

// Add a README or metadata file alongside exports
string readmePath = Path.Combine(exportPath, "README.txt");
File.WriteAllText(readmePath, @"
Export Information
==================
Date: {DateTime.Now:yyyy-MM-dd HH:mm:ss}
Format: CSV with headers
Delimiter: Comma
Date Format: yyyy-MM-dd
Encoding: UTF-8
");

Common Use Cases

Data Migration

// Export from source database
using (var sourceManager = new Manager(sourceConnection))
{
    Database database = sourceManager.LoadDatabase();
    sourceManager.ExportDatabase(database, ConfigType.Json, "./migration");
}

// Import to target database
using (var targetManager = new Manager(targetConnection))
{
    Database database = targetManager.ReadDatabaseFromFile("./migration/database.json");
    targetManager.BuildDatabase(database);
    // Then import CSV files using ImportData
}

Regular Backups

void PerformDatabaseBackup(IDatasourceConnection connection)
{
    using (var manager = new Manager(connection))
    {
        Database database = manager.LoadDatabase();

        string timestamp = DateTime.Now.ToString("yyyyMMdd_HHmmss");
        string backupPath = $"./backups/backup_{timestamp}";
        Directory.CreateDirectory(backupPath);

        manager.ExportDatabase(database, ConfigType.Json, backupPath, includeViews: true);

        Console.WriteLine($"Backup completed: {backupPath}");
    }
}

Reporting and Analytics

// Export daily sales report
var orderCountExpr   = new Expression("total_orders",  new Aggregate(AggregateType.Count, ordersTable["order_id"]));
var dailyRevenueExpr = new Expression("daily_revenue", new Aggregate(AggregateType.Sum,   ordersTable["total_amount"]));

var salesQuery = new Query()
    .Select([
        ordersTable["order_date"],
        orderCountExpr,
        dailyRevenueExpr
    ])
    .From(ordersTable)
    .Where(new Criterion<DateTime>(ordersTable["order_date"], ConditionalType.GreaterThanOrEqualTo, DateTime.Today.AddDays(-30)))
    .GroupBy(ordersTable["order_date"])
    .OrderBy(ordersTable["order_date"], OrderClauseType.Descending);

manager.ExportData(salesQuery, "daily_sales_report.csv");

Data Exchange

// Export for partner system
var config = new ExportConfig();
config.DateFormat      = "yyyyMMdd";
config.DateTimeFormat  = "yyyyMMddHHmmss";
config.FieldDelimiter  = '|';
config.ForceQualifier  = false;
config.IntervalFormat  = "c";
config.TimeFormat      = "HHmmss";
config.TimestampFormat = "yyyyMMddHHmmsszzz";
config.WriteHeaders    = false;

manager.ExportData(partnerDataTable, "partner_feed.dat", config);

Troubleshooting

Common Export Issues

Error: "File path is invalid"

Ensure the directory exists
Check write permissions
Use absolute paths or validate relative paths

Error: "Query must be executed first"

When exporting query results, Velocity executes the query twice (once to determine types, once to retrieve data)
Ensure your query is valid and can be executed

Issue: Special characters not displaying correctly

Ensure consistent encoding (UTF-8)
Check QuoteCharacter and EscapeCharacter settings
Verify ForceQualifier setting for fields with special characters

Issue: Dates not in expected format

Verify DateFormat, DateTimeFormat, and TimestampFormat settings
Consider using DateFormatType.Iso for consistency

Issue: Decimal numbers have wrong precision

CSV stores numbers as text; precision is preserved
Format is controlled by the database, not ExportConfig

Summary

Velocity's export functionality provides:

Flexible Sources - Export from Tables, Views, or Query results
Automatic Configuration - ExportConfig created automatically if not provided
Customizable Formatting - Control delimiters, dates, quoting, and more
Geospatial Support - WKT format for spatial data
Schema Export - Backup entire Schemas or databases with structure and data
Multiple Formats - Export schema definitions as XML, JSON, or YAML
Portability - ISO date formats work across all systems
Blob Support - Export binary columns as inline Base64 strings or external files, with global, table-scoped, and schema+table-scoped configuration to handle naming collisions across a database export

By following these guidelines and examples, you can efficiently export data from any Velocity-supported database for backups, migrations, reporting, or data exchange with confidence and consistency.

Table of Contents

Exporting Data

Introduction

Basic Export Operation

Simple Export Example

The ExportConfig Class

Properties

Constructor Options

Exporting Tables

Export with Custom Configuration

Exporting Views

Exporting Query Results

Basic Query Export

Advanced Query Export with Joins

Filtered and Aggregated Exports

Date and Time Formatting

ISO Format (Recommended)

Date

DateTime

Interval

Time

Timestamp

Culture-Specific Formatting

Note

Exporting Geospatial Data

Teradata: Clob and Geospatial Column Casting on Export

Note

Exporting Blob Data

Base64 Encoding

File-Based Export

Exporting Blobs from a Schema or Database

Global (applies to every table)

Table-Scoped (applies to a specific table)

Schema+Table-Scoped (applies to a specific schema and table)

Resolution Order

Blob Export Reference

Note

Field Delimiters and Formatting

Custom Delimiters

Quote Behavior

String Trimming

Exporting Schemas and Databases

Export Single Schema

Export to Different Formats

Export with Custom Data Format

Include Views in Schema Export

Export Entire Database

Selective Database Export

Complete Examples

Example 1: Basic Table Export

Example 2: Export with Custom Formatting

Example 3: Export Filtered Data

Example 4: Export Aggregated Report

Example 5: Export Geospatial Data

Example 6: Export for European Systems

Example 7: Full Database Backup

Example 8: Export Without Headers for Import to Legacy System

Export Methods Reference

Export from Table

Export from View

Export from Query

Export Schema

Export Database

Best Practices

1. Use ISO Format for Portability

2. Include Headers for Clarity

3. Use Queries for Selective Exports

4. Validate File Paths

5. Handle Large Exports Appropriately

6. Use Consistent Formats for Data Exchange

7. Test Exports with Small Datasets First

8. Document Your Export Format

Common Use Cases

Data Migration

Regular Backups

Reporting and Analytics

Data Exchange

Troubleshooting

Common Export Issues

Summary