ETLBox ETLBox Try now!

Csv destination

This article contains example code that shows the usage of the CsvDestination component.

The Csv destination is your best choice if you want to create data file that are RFC-4180 compliant   . The CsvDestination (as well as the CsvSource) relies on the 3rd party library CsvHelper by Josh Close   . Some of the Configuration properties from this library are directly exposed.

Shared code

The following method is used in the subsequent examples, and prints the content of a csv file on the console output.

private void PrintOutputFile(string sourceFile) {
    Console.WriteLine($"Content of file '{Path.GetFileName(sourceFile)}'");
    Console.WriteLine("---");
    Console.WriteLine(File.ReadAllText(sourceFile));
    Console.WriteLine("---");
}

Write csv file - simple start

With POCO

Let’s start with a simple example - we write data into a csv file using a POCO (Plain old component object).

public class MyRow
{
    public string Value1 { get; set; }
    public decimal Value2 { get; set; }
    public int Id { get; set; }
}

string destFile = @"res/Examples/SimpleWithPOC.csv";
var source = new MemorySource<MyRow>();
source.Data = new List<MyRow>() {
    new MyRow() { Id = 1, Value1="Test1", Value2 = 1.1m},
    new MyRow() { Id = 2, Value2 = 1.2m},
    new MyRow() { Id = 3, Value1="Test,#3", Value2 = 1.3m},
};
var dest = new CsvDestination<MyRow>(destFile);
dest.ResourceType = ResourceType.File;

source.LinkTo(dest);
Network.Execute(source);
PrintOutputFile(destFile);

/* Output:
Content of file 'SimpleWithPOC.csv'
---
Value1,Value2,Id
Test1,1.1,1
,1.2,2
"Test,#3",1.3,3

---
*/

Configure underlying CsvWriter

This example shows how we can use the CsvHelper   Configuration to provide a different order of the header columns. Also, we can use the exposed configuration property to set up various other parameters, e.g. writing with a different delimiter or enabling quotes for strings.

Note

To use a different encoding, you need to use the Encoding property on the CsvSource directly. Do not use the Encoding property on the CsvHelper Configuration object.
public class MyRowWithConfig
{
    [CsvHelper.Configuration.Attributes.Index(1)]
    public string Value1 { get; set; }
    [CsvHelper.Configuration.Attributes.Index(2)]
    public decimal Value2 { get; set; }
    [CsvHelper.Configuration.Attributes.Index(0)]
    public int Id { get; set; }
}

string destFile = @"res/Examples/DifferentConfig.csv";

var source = new MemorySource<MyRowWithConfig>();
source.Data = new List<MyRowWithConfig>() {
    new MyRowWithConfig() { Id = 1, Value1="Test1", Value2 = 1.1m},
    new MyRowWithConfig() { Id = 2, Value2 = 1.2m},
    new MyRowWithConfig() { Id = 3, Value1="Test,#3", Value2 = 1.3m},
};
var dest = new CsvDestination<MyRowWithConfig>(destFile);
dest.ResourceType = ResourceType.File;
dest.Configuration =
    new CsvHelper.Configuration.CsvConfiguration(CultureInfo.InvariantCulture) {
        Delimiter = ";",
        ShouldQuote = rec => rec.FieldType == typeof(string),
        Quote = '"'
        //See also https://joshclose.github.io/CsvHelper/
    };
dest.Encoding = Encoding.UTF8;

source.LinkTo(dest);
Network.Execute(source);
PrintOutputFile(destFile);


/* Output:
Content of file 'DifferentConfig.csv'
---
"Id";"Value1";"Value2"
1;"Test1";1.1
2;"";1.2
3;"Test,#3";1.3

---
*/

Map header names to properties with class maps

If you are not allowed to add attributes to your object, you can use the class mapping to change the header names or the order in your csv file (and many more different options are available here).

public class MyRow
{
    public string Value1 { get; set; }
    public decimal Value2 { get; set; }
    public int Id { get; set; }
}

public class ModelClassMap : ClassMap<MyRow>
{
    public ModelClassMap() {
        Map(m => m.Id).Name("RowNr").Index(0);
        Map(m => m.Value1).Name("Col1").Index(1);
        Map(m => m.Value2).Name("Col2").Index(2);
    }
}

string destFile = @"res/Examples/WithClassMap.csv";

var source = new MemorySource<MyRow>();
source.Data = new List<MyRow>() {
    new MyRow() { Id = 1, Value1="Test1", Value2 = 1.1m},
    new MyRow() { Id = 2, Value2 = 1.2m},
    new MyRow() { Id = 3, Value1="Test,#3", Value2 = 1.3m},
};
var dest = new CsvDestination<MyRow>(destFile);
dest.CsvContext = context => context.RegisterClassMap<ModelClassMap>();

source.LinkTo(dest);
Network.Execute(source);
PrintOutputFile(destFile);


/* Output:
Content of file 'WithClassMap.csv'
---
RowNr,Col1,Col2
1,Test1,1.1
2,,1.2
3,"Test,#3",1.3

---
*/

Writing into multiple files

You can use the GetNextUri/HasNextUri pattern (provided on all streaming connectors) to create multiple output files.

public class MyRow
{
    public string Value1 { get; set; }
    public decimal Value2 { get; set; }
    public int Id { get; set; }
}
string filePrefix = @"res/Examples/multiple_files_";

var source = new MemorySource<MyRow>();
source.Data = new List<MyRow>();
for (int i = 1; i <= 8; i++)
    source.DataAsList.Add(new MyRow() { Id = i, Value1 = "Test" + i, Value2 = i * 0.1m });

var dest = new CsvDestination<MyRow>();
dest.HasNextUri = (smd, _) => smd.ProcessedRows == 3;
dest.GetNextUri = (smd, _) => filePrefix + smd.ProgressCount / 3 + ".csv";
dest.ResourceType = ResourceType.File;

source.LinkTo(dest);
Network.Execute(source);
PrintOutputFile(filePrefix + "0.csv");
PrintOutputFile(filePrefix + "1.csv");
PrintOutputFile(filePrefix + "2.csv");


/* Output:
Content of file 'multiple_files_0.csv'
---
Value1,Value2,Id
Test1,0.1,1
Test2,0.2,2
Test3,0.3,3

---
Content of file 'multiple_files_1.csv'
---
Value1,Value2,Id
Test4,0.4,4
Test5,0.5,5
Test6,0.6,6

---
Content of file 'multiple_files_2.csv'
---
Value1,Value2,Id
Test7,0.7,7
Test8,0.8,8

---
*/

Dynamic csv file creation

The following example shows how a dynamic ExpandoObject can be used to copy a sql server table into a csv file, without the need to define any strongly typed object first.

private void CreateTable(IConnectionManager connMan, string tableName) {
    DropTableTask.DropIfExists(connMan, tableName);
    TableDefinition td = new TableDefinition(tableName
        , new List<TableColumn>() {
        new TableColumn("Id", "INT", allowNulls: false),
        new TableColumn("Value1", "VARCHAR(100)", allowNulls: true),
        new TableColumn("Value2", "VARCHAR(10)", allowNulls: false)
    });
    CreateTableTask.Create(connMan, td);
    ObjectNameDescriptor TN = new ObjectNameDescriptor(tableName, connMan.QB, connMan.QE);
    SqlTask.ExecuteNonQuery(connMan, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'Test1', '1.1')");
    SqlTask.ExecuteNonQuery(connMan, $@"INSERT INTO {TN.QuotatedFullName} VALUES(2,'Test2', '1.2')");
    SqlTask.ExecuteNonQuery(connMan, $@"INSERT INTO {TN.QuotatedFullName} VALUES(3,'Test3', '1.3')");
}

string destFile = @"res/Examples/TableIntoCsv.csv";
var connMan = new SqlConnectionManager(ConnectionString);
CreateTable(connMan, "ExampleTable");
var source = new DbSource() {
    ConnectionManager = connMan,
    TableName = "ExampleTable"
};
var dest = new CsvDestination() {
    Uri = destFile
};

source.LinkTo(dest);
Network.Execute(source);

PrintOutputFile(destFile);

/* Output:
Content of file 'TableIntoCsv.csv'
---
Id,Value1,Value2
1,Test1,1.1
2,Test2,1.2
3,Test3,1.3

---
*/

Writing into Azure blob storage

All streaming connectors support writing data into Azure blob storage.

public class MyRow
{
    public string Value1 { get; set; }
    public decimal Value2 { get; set; }
    public int Id { get; set; }
}

string connectionString = AzureStorageHelper.RetrieveConnectionString();
string containerName = "csv-example";
AzureStorageHelper.DeleteAndCreateContainer(connectionString, containerName);

var source = new MemorySource<MyRow>();
source.Data = new List<MyRow>();
for (int i = 1; i <= 8; i++)
    source.DataAsList.Add(new MyRow() { Id = i, Value1 = "Test" + i, Value2 = i * 0.1m });

var dest = new CsvDestination<MyRow>("blob.csv");
dest.ResourceType = ResourceType.AzureBlob;
dest.AzureBlobStorage.ConnectionString = connectionString;
dest.AzureBlobStorage.ContainerName = containerName;

source.LinkTo(dest);
Network.Execute(source);

//Download blob again and check content
BlobContainerClient containerClient = new BlobContainerClient(connectionString, containerName);
var content =  containerClient.GetBlobClient("blob.csv").DownloadContent().Value.Content;
var file = Encoding.UTF8.GetString(content);
Console.WriteLine(file);

/* Output:
Value1,Value2,Id
Test1,0.1,1
Test2,0.2,2
Test3,0.3,3
Test4,0.4,4
Test5,0.5,5
Test6,0.6,6
Test7,0.7,7
Test8,0.8,8
*/

Utilizing your own stream

All streaming connector support that you provide your own stream. The following example shows how to use your own file stream to append data to an existing csv file.

public class MyRowWithConfig
{
    [CsvHelper.Configuration.Attributes.Index(1)]
    public string Value1 { get; set; }
    [CsvHelper.Configuration.Attributes.Index(2)]
    public decimal Value2 { get; set; }
    [CsvHelper.Configuration.Attributes.Index(0)]
    public int Id { get; set; }
}

string destFile = @"res/Examples/OwnStream.csv";
if (File.Exists(destFile)) File.Delete(destFile);
string existingCsvContent = "Id,Value1,Value2" + Environment.NewLine
    + "1,TestA,X" + Environment.NewLine
    + "2,TestB,X" + Environment.NewLine;
File.WriteAllText(destFile, existingCsvContent);

var source = new MemorySource<MyRowWithConfig>();
source.Data = new List<MyRowWithConfig>() {
    new MyRowWithConfig() { Id = 3, Value1="Test3", Value2 = 5.5m },
    new MyRowWithConfig() { Id = 4, Value1="Test4"},
};

FileStream fileStream = new FileStream(destFile, FileMode.Append, FileAccess.Write);
CsvDestination<MyRowWithConfig> dest = new CsvDestination<MyRowWithConfig>();
dest.CreateStreamWriter = _ => {
    return new StreamWriter(fileStream, Encoding.UTF8);
};
dest.Configuration.HasHeaderRecord = false;

source.LinkTo(dest);
Network.Execute(source);

PrintOutputFile(destFile);

/*Output:
Content of file 'OwnStream.csv'
---
Id,Value1,Value2
1,TestA,X
2,TestB,X
3,Test3,5.5
4,Test4,0

---
*/

Creating an empty csv file if no data arrives

By default, if no data arrives at a destination component, no action is performed. The following code example shows how an empty file can be generated when no data arrives at the CsvDestination.


public class MyRow
{
    public string Value1 { get; set; }
    public decimal Value2 { get; set; }
    public int Id { get; set; }
}

string destFile = @"res/Examples/EmptyFile.csv";
if (File.Exists(destFile)) File.Delete(destFile);

var source = new MemorySource<MyRow>();
source.Data = new List<MyRow>();

CsvDestination<MyRow> dest = new CsvDestination<MyRow>(destFile);
dest.OnCompletion = () => {
    if (dest.ProgressCount == 0) {
        using (StreamWriter sw = new StreamWriter(destFile))
        using (CsvHelper.CsvWriter cw = new CsvHelper.CsvWriter(sw, dest.Configuration)) {
            cw.WriteHeader<MyRow>();
        }
    }
};

source.LinkTo(dest);
Network.Execute(source);

PrintOutputFile(destFile);

/*Output:
Content of file 'EmptyFile.csv'
---
Value1,Value2,Id
---
*/

Removing columns when using dynamic objects

If you are using dynamic objects when writing into a csv file, by default all column are written into the output file. If you want to omit (or rename) particular columns, you can use the ColumnRename transformation.

string destFile = @"res/Examples/DynamicWithLessColumns.csv";

var source = new MemorySource();
source.Data = new List<ExpandoObject>();

dynamic r1 = new ExpandoObject();
r1.Id = 1; r1.Value = "Test1"; r1.Value2 = "A";
source.DataAsList.Add(r1);

dynamic r2 = new ExpandoObject();
r2.Id = 2; r2.Value = "Test1"; r2.Value2 = "B";
source.DataAsList.Add(r2);

var dest = new CsvDestination(destFile);
dest.ResourceType = ResourceType.File;

var cr = new ColumnRename();
cr.RenameColumns = new[] {
    new RenameColumn() { CurrentName = "Value", RemoveColumn = true }
};

source.LinkTo(cr);
cr.LinkTo(dest);
Network.Execute(source);
PrintOutputFile(destFile);


/* Output:
Content of file 'DynamicWithLessColumns.csv'
---
Id,Value2
1,A
2,B

---
*/
Edit this page on GitHub
Prev
Xml source
Next
Custom batch destination