ChoETL is an open source ETL (extract, transform and load) framework for .NET. It is a code based library for extracting data from multiple sources, transforming, and loading into your very own data warehouse in .NET environment. You can have data in your data warehouse in no time.
Table of Contents
- Introduction
- Requirement
- "Hello World!" Sample
- Quick write - Data First Approach
- Code First Approach
- Configuration First Approach
- Writing All Records
- Write Records Manually
- Customize FixedLength Record
- Customize FixedLength Header
- Customize FixedLength Fields
- DefaultValue
- ChoFallbackValue
- Type Converters
- Validations
- Reserved
- Callback Mechanism
- BeginWrite
- EndWrite
- BeforeRecordWrite
- AfterRecordWrite
- RecordWriteError
- BeforeRecordFieldWrite
- AfterRecordFieldWrite
- RecordWriteFieldError
- Customization
- Using Dynamic Object
- Exceptions
- Tips
- Using MetadataType Annotation
- Configuration Choices
- Manual Configuration
- Auto Map Configuration
- Attaching MetadataType class
- ToText Helper Method
- Writing DataReader Helper Method
- Writing DataTable Helper Method
- Advanced Topics
- Override Converters Format Specs
- Currency Support
- Enum Support
- Boolean Support
- DateTime Support
ChoETL is an open source ETL (extract, transform and load) framework for .NET. It is a code based library for extracting data from multiple sources, transforming, and loading into your very own data warehouse in .NET environment. You can have data in your data warehouse in no time.
This article talks about using FixedLengthRWriter
component offered by ChoETL framework. It is a simple utility class to save FixedLength
data to a file.
NOTE: Corresponding FixedLengthReader article can be found here.
Features
- Follows
FixedLength
standard file rules. Gracefully handles data fields that contain commas and line breaks. - In addition to comma, most delimiting characters can be used, including tab delimited fields.
- Supports culture specific date, currency and number formats while generating files.
- Supports different character encoding.
- Provides fine control of date, currency, enum, boolean, number formats when writing files.
- Detailed and robust error handling, allowing you to quickly find and fix problems.
- Shorten your development time.
This framework library is written in C# using .NET 4.5 Framework.
- Open VS.NET 2013 or higher
- Create a sample VS.NET (.NET Framework 4.5) Console Application project
- Install ChoETL via Package Manager Console using Nuget Command:
Install-Package ChoETL
- Use the
ChoETL
namespace
Let's begin by looking into a simple example of generating the below FixedLength
file having 2 columns.
Listing 3.1 Sample FixedLength data file (RecordLength: 18 chars)
Id Name
00000001Mark
00000002Jason
There are a number of ways in which you can get the FixedLength
file be created with minimal setup.
This is the quick way to create FixedLength
file in no time. No typed POCO object is needed. The sample code below shows how to generate the above sample FixedLength
file using dynamic objects.
Listing 3.1.1 Write list of objects to FixedLength file
static void QuickWriteTest()
{
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 1;
rec1.Name = "Mark";
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 2;
rec2.Name = "Jason";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter("Emp.txt").
WithField("Id", 0, 8. fieldType: typeof(int)).
WithField("Name", 8, 10))
{
parser.Write(objs);
}
}
In the above sample, define the individual column's start index and size using fluent API, then we give the list of objects to FixedLengthWriter
at one pass to write them to FixedLength
file.
Listing 3.1.2 Write each object to FixedLength file
static void QuickWriteTest2()
{
using (var parser = new ChoFixedLengthWriter("Emp.txt").
WithField("Id", 0, 8, fieldType: typeof(int)).
WithField("Name", 8, 10))
{
dynamic rec1 = new ExpandoObject();
rec1.Id = 1;
rec1.Name = "Mark";
parser.Write(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 2;
rec2.Name = "Jason";
parser.Write(rec2);
}
}
In the above sample, we take control of constructing, passing each individual record to the FixedLengthWriter
to generate the FixedLength
file using Write
overload.
This is another way to generate FixedLength
file using typed POCO class. First, define a simple POCO class to match the underlying FixedLength
file layout.
Listing 3.2.1 Simple POCO entity class
public partial class EmployeeRecSimple
{
public int Id { get; set; }
public string Name { get; set; }
}
In the above, the POCO class defines two properties matching the sample FixedLength
file template.
Listing 3.2.2 Saving to FixedLength file
List<EmployeeRecSimple> objs = new List<EmployeeRecSimple>();
EmployeeRecSimple rec1 = new EmployeeRecSimple();
rec1.Id = 1;
rec1.Name = "Mark";
objs.Add(rec1);
EmployeeRecSimple rec2 = new EmployeeRecSimple();
rec2.Id = 2;
rec2.Name = "Jason";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter<EmployeeRecSimple>("Emp.txt").
WithField("Id", 0, 8, fieldType: typeof(int)).
WithField("Name", 8, 10))
{
parser.Write(objs);
}
The above sample shows how to create FixedLength
file from typed POCO class objects.
In this model, we define the FixedLength
configuration with all the necessary parameters along with FixedLength
columns required to generate the sample FixedLength
file.
Listing 3.3.1 Define FixedLength configuration
ChoFixedLengthRecordConfiguration config = new ChoFixedLengthRecordConfiguration();
config.FixedLengthRecordFieldConfigurations.Add
(new ChoFixedLengthRecordFieldConfiguration("Id", 0, 8, fieldType: typeof(int)));
config.FixedLengthRecordFieldConfigurations.Add
(new ChoFixedLengthRecordFieldConfiguration("Name", 8, 10));
In the above, the class defines two FixedLength
properties matching the sample FixedLength
file template.
Listing 3.3.2 Generate FixedLength file without POCO object
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 1;
rec1.Name = "Mark";
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 2;
rec2.Name = "Tom";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter("Emp.txt", config))
{
parser.Write(objs);
}
The above sample code shows how to generate FixedLength
file from a list of dynamic objects using predefined FixedLength
configuration setup. In the FixedLengthWriter
constructor, we specified the FixedLength
configuration object to obey the FixedLength
layout schema while creating the file. If there is any mismatch in the name or count of FixedLength
columns, it will be reported as an error and stops the writing process.
Listing 3.3.3 Saving FixedLength file with POCO object
List<EmployeeRecSimple> objs = new List<EmployeeRecSimple>();
EmployeeRecSimple rec1 = new EmployeeRecSimple();
rec1.Id = 1;
rec1.Name = "Mark";
objs.Add(rec1);
EmployeeRecSimple rec2 = new EmployeeRecSimple();
rec2.Id = 2;
rec2.Name = "Jason";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter<EmployeeRecSimple>
("Emp.txt", config))
{
parser.Write(objs);
}
The above sample code shows how to generate FixedLength
file from a list of POCO objects with FixedLength
configuration object. In the FixedLengthWriter
constructor, we specified the FixedLength
configuration configuration object.
3.4. Code First with Declarative Configuration
This is the combined approach to define POCO entity class along with attaching FixedLength
configuration parameters declaratively. id
is a required column and name
is an optional value column with default value "XXXX
". If name
is not present, it will take the default value.
Listing 3.4.1 Define POCO Object
public class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
[Required]
public int? Id
{
get;
set;
}
[ChoFixedLengthRecordField(8, 10)]
[DefaultValue("XXXX")]
public string Name
{
get;
set;
}
public override string ToString()
{
return "{0}. {1}.".FormatString(Id, Name);
}
}
The code above illustrates about defining POCO object with necessary attributes required to generate FixedLength
file. First thing defines property for each record field with ChoFixedLengthRecordFieldAttribute
to qualify for FixedLength
record mapping. Each property must specify StartIndex
and Size
of the field in order to be mapped to FixedLength
column. StartIndex
is 0
based. Id
is a required property. We decorated it with RequiredAttribute
. Name
is given a default value using DefaultValueAttribute
. It means that if the Name
value is not set in the object, FixedLengthWriter
uses the default value 'XXXX
' to the file.
It is very simple and ready to save FixedLength
data in no time.
Listing 3.4.2 Saving FixedLength file with POCO object
List<EmployeeRec> objs = new List<EmployeeRec>();
EmployeeRec rec1 = new EmployeeRec();
rec1.Id = 10;
rec1.Name = "Mark";
objs.Add(rec1);
EmployeeRec rec2 = new EmployeeRec();
rec2.Id = 200;
rec2.Name = "Lou";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter<EmployeeRec>("Emp.txt"))
{
parser.Write(objs);
}
We start by creating a new instance of ChoFixedLengthWriter
object. That's all. All the heavy lifting of generating FixedLength
data from the objects is done by the writer under the hood.
By default, FixedLengthWriter
discovers and uses default configuration parameters while saving FixedLength
file. These can be overridable according to your needs. The following sections will give you in-depth details about each configuration attribute.
It is as easy as setting up POCO object match up with FixedLength
file structure, construct the list of objects and pass it to FixedLengthWriter
's Write
method. This will write the entire list of objects into FixedLength
file in one single call.
Listing 4.1 Write to FixedLength File
List<EmployeeRec> objs = new List<EmployeeRec>();
...
using (var parser = new ChoFixedLengthWriter<EmployeeRec>("Emp.txt"))
{
parser.Write(objs);
}
or:
Listing 4.2 Writer to FixedLength file stream
List<EmployeeRec> objs = new List<EmployeeRec>();
...
using (var tx = File.OpenWrite("Emp.txt"))
{
using (var parser = new ChoFixedLengthWriter<EmployeeRec>(tx))
{
parser.Write(objs);
}
}
This model keeps your code elegant, clean, easy to read and maintain.
This is an alternative way to write each and individual record to FixedLength
file in case when the POCO objects are constructed in a disconnected way.
Listing 5.1 Writing to FixedLength file
var writer = new ChoFixedLengthWriter<EmployeeRec>("Emp.txt");
EmployeeRec rec1 = new EmployeeRec();
rec1.Id = 10;
rec1.Name = "Mark";
writer.Write(rec1);
EmployeeRec rec2 = new EmployeeRec();
rec1.Id = 11;
rec1.Name = "Top";
writer.Write(rec2);
Using ChoFixedLengthRecordObjectAttribute
, you can customize the POCO entity object declaratively.
Listing 6.1 Customizing POCO object for each record
[ChoFixedLengthRecordObject(Encoding = "Encoding.UTF32",
ErrorMode = ChoErrorMode.IgnoreAndContinue, IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All)]
public class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
public string Name { get; set; }
}
Here are the available attributes to carry out customization of FixedLength
load operation on a file:
EOLDelimiter
- The value used to separate FixedLength
rows. Default is \r\n
(NewLine) Culture
- The culture info used to read and write IgnoreEmptyLine
- N/A Comments
- N/A QuoteChar
- The value used to escape fields that contain a delimiter, quote, or line ending QuoteAllFields
- A flag that tells the writer whether all fields written should have quotes around them; regardless of whether the field contains anything that should be escaped Encoding
- The encoding of the FixedLength
file HasExcelSeperator
- N/A ColumnCountStrict
- This flag indicates if an exception should be thrown if FixedLength
field configuration mismatch with the data object members ColumnOrderStrict
- N/A BufferSize
- The size of the internal buffer that is used when reader is from the StreamWriter
ErrorMode
- This flag indicates if an exception should be thrown if writing and an expected field is failed to write. This can be overridden per property. Possible values are:
IgnoreAndContinue
- Ignore the error, record will be skipped and continue with next. ReportAndContinue
- Report the error to POCO entity if it is of IChoNotifyRecordWrite
type ThrowAndStop
- Throw the error and stop the execution
IgnoreFieldValueMode
- N/A ObjectValidationMode
- A flag to let the reader know about the type of validation to be performed with record object. Possible values are:
Off
- No object validation performed MemberLevel
- Validation performed before each FixedLength
property gets written to the file ObjectLevel
- Validation performed before all the POCO properties are written to the file
By attaching ChoFixedLengthFileHeaderAttribute
to POCO entity object declaratively, you can influence the writer to generate FixedLength
header when creating FixedLength
file.
Listing 6.1 Customizing POCO object for file header
[ChoFixedLengthFileHeader]
public class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
public string Name { get; set; }
}
Here are the available members to add some customization to it according to your need.
FillChar
- Padding character used when size of the FixedLength
column header is short of the column size (ChoFixedLengthRecordFieldAttribute.Size
or ChoFixedLengthRecordFieldConfiguration.Size
). Default is '\0
', padding will be off Justification
- Column header alignment. Default is Left
TrimOption
- N/A Truncate
- This flag tells that the writer to truncate the FixedLength
column header value if it is over the column size. Default is false
For each FixedLength
column, you can specify the mapping in POCO entity property using ChoFixedLengthRecordFieldAttribute
.
Listing 6.1 Customizing POCO Object for FixedLength Columns
[ChoFixedLengthFileHeader]
public class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
public string Name { get; set; }
}
Here are the available members to add some customization to it for each property:
StartIndex
- The zero-based starting character position of a column in the line FieldName
- FixedLength
Column name header. If not specified, POCO object property name will be used as column header FillChar
- Padding character used when size of the FixedLength
column value is short of the column size. Default is '\0
', padding will be off FieldValueJustification
- Column value alignment. Default is Left
FieldValueTrimOption
- N/A Truncate
- This flag tells the writer to truncate the FixedLength
column value if it is over the column size. Default is false
Size
- Size of FixedLength
column value QuoteField
- A flag that tells the writer that the FixedLength
column value is surrounded by quotes. ErrorMode
- This flag indicates if an exception should be thrown if writing and an expected field failed to convert and write. Possible values are:
IgnoreAndContinue
- Ignore the error and continue to load other properties of the record ReportAndContinue
- Report the error to POCO entity if it is of IChoRecord
type ThrowAndStop
- Throw the error and stop the execution
IgnoreFieldValueMode
- N/A
Any POCO entity property can be specified with default value using System.ComponentModel.DefaultValueAttribute
. It is the value used to write when the FixedLength
value is null
(controlled via IgnoreFieldValueMode
).
Any POCO entity property can be specified with fallback value using ChoETL.ChoFallbackValueAttribute
. It is the value used and set to the property when the FixedLength
value failed to convert as text. Fallback
value only set when ErrorMode
is either IgnoreAndContinue
or ReportAndContinue
.
Most of the primitive types are automatically converted to string
/text and save them to FixedLength
file. If the value of the FixedLength
fields aren't automatically converted into the text value, you can specify a custom / built-in .NET converters to convert the value to text. These can be either IValueConverter
or TypeConverter
converters.
The methods to use to convert/format property values to text are IValueConverter.ConvertBack()
or TypeConvert.ConvertTo()
.
Listing 8.3.1 Specifying type converters
[ChoFixedLengthFileHeader]
public class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
[ChoTypeConverter(typeof(IntConverter))]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
public string Name { get; set; }
}
Listing 8.3.2 IntConverter implementation
public class IntConverter : IValueConverter
{
public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
{
return value;
}
public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
{
int intValue = (int)value;
return intValue.ToString("D4");
}
}
In the example above, we defined custom IntConverter
class. And showed how to format 'Id
' FixedLength
property with leading zeros.
FixedLengthWriter
leverages both System.ComponentModel.DataAnnotations and Validation Block
validation attributes to specify validation rules for individual fields of POCO entity. Refer to the MSDN site for a list of available DataAnnotations
validation attributes.
Listing 8.4.1 Using validation attributes in POCO entity
[ChoFixedLengthFileHeader]
[ChoFixedLengthRecordObject(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public partial class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
[ChoFallbackValue(1)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10)]
[Required]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
}
In the example above, used Range
validation attribute for Id
property. Required
validation attribute to Name
property. FixedLengthWriter
performs validation on them before saving the data to file when Configuration.ObjectValidationMode
is set to ChoObjectValidationMode.MemberLevel
or ChoObjectValidationMode.ObjectLevel
.
In some cases, you may want to take control and perform manual self validation within the POCO entity class. This can be achieved by inheriting POCO object from IChoValidatable
interface.
Listing 8.4.2 Manual validation on POCO entity
[ChoFixedLengthFileHeader]
[ChoFixedLengthRecordObject
(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public partial class EmployeeRec : IChoValidatable
{
[ChoFixedLengthRecordField(0, 8)]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
[ChoFallbackValue(1)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10)]
[Required]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
public bool TryValidate
(object target, ICollection<ValidationResult> validationResults)
{
return true;
}
public bool TryValidateFor
(object target, string memberName, ICollection<ValidationResult> validationResults)
{
return true;
}
public void Validate(object target)
{
}
public void ValidateFor(object target, string memberName)
{
}
}
The sample above shows how to implement custom self-validation in POCO object.
IChoValidatable
interface exposes the below methods:
TryValidate
- Validate entire object, return true
if all validation passed. Otherwise, return false
. Validate
- Validate entire object, throw exception if validation is not passed. TryValidateFor
- Validate specific property of the object, return true
if all validation passed. Otherwise, return false
. ValidateFor
- Validate specific property of the object, throw exception if validation is not passed.
FixedLengthWriter
offers industry standard FixedLength
data file generation out of the box to handle most of the needs. If the generation process is not handling any of your needs, you can use the callback mechanism offered by FixedLengthWriter
to handle such situations. In order to participate in the callback mechanism, Either POCO entity object or DataAnnotation
's MetadataType
type object must be inherited by IChoNotifyRecordWrite
interface.
Tip: Any exceptions raised out of these interface methods will be ignored.
IChoNotifyRecordWrite
exposes the below methods:
BeginWrite
- Invoked at the begin of the FixedLength
file write EndWrite
- Invoked at the end of the FixedLength
file write BeforeRecordWrite
- Raised before the FixedLength
record write AfterRecordWrite
- Raised after FixedLength
record write RecordWriteError
- Raised when FixedLength
record errors out while writing BeforeRecordFieldWrite
- Raised before FixedLength
column value write AfterRecordFieldWrite
- Raised after FixedLength
column value write RecordFieldWriteError
- Raised when FixedLength
column value errors out while writing
Listing 10.1 Direct POCO callback mechanism implementation
[ChoFixedLengthFileHeader]
[ChoFixedLengthRecordObject
(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public partial class EmployeeRec : IChoNotifyrRecordWrite
{
[ChoFixedLengthRecordField(0, 8)]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
[ChoFallbackValue(1)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
public bool AfterRecordFieldWrite(object target, int index, string propName, object value)
{
throw new NotImplementedException();
}
public bool AfterRecordWrite(object target, int index, object source)
{
throw new NotImplementedException();
}
public bool BeforeRecordFieldWrite(object target, int index, string propName, ref object value)
{
throw new NotImplementedException();
}
public bool BeforeRecordWrite(object target, int index, ref object source)
{
throw new NotImplementedException();
}
public bool BeginWrite(object source)
{
throw new NotImplementedException();
}
public void EndWrite(object source)
{
throw new NotImplementedException();
}
public bool RecordFieldWriteError
(object target, int index, string propName, object value, Exception ex)
{
throw new NotImplementedException();
}
public bool RecordWriteError(object target, int index, object source, Exception ex)
{
throw new NotImplementedException();
}
}
Listing 10.2 MetaDataType based callback mechanism implementation
[ChoFixedLengthFileHeader]
[ChoFixedLengthRecordObject
(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.IgnoreAndContinue,
IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false)]
public class EmployeeRecMeta : IChoNotifyRecordWrite
{
[ChoFixedLengthRecordField(0, 8)]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
[ChoFallbackValue(1)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8. 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
public bool AfterRecordFieldWrite(object target, int index, string propName, object value)
{
throw new NotImplementedException();
}
public bool AfterRecordWrite(object target, int index, object source)
{
throw new NotImplementedException();
}
public bool BeforeRecordFieldWrite(object target, int index, string propName, ref object value)
{
throw new NotImplementedException();
}
public bool BeforeRecordWrite(object target, int index, ref object source)
{
throw new NotImplementedException();
}
public bool BeginWrite(object source)
{
throw new NotImplementedException();
}
public void EndWrite(object source)
{
throw new NotImplementedException();
}
public bool RecordFieldWriteError
(object target, int index, string propName, object value, Exception ex)
{
throw new NotImplementedException();
}
public bool RecordWriteError(object target, int index, object source, Exception ex)
{
throw new NotImplementedException();
}
}
[MetadataType(typeof(EmployeeRecMeta))]
public partial class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, int.MaxValue, ErrorMessage = "Id must be > 0.")]
[ChoFallbackValue(1)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[Required]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
}
This callback invoked once at the beginning of the FixedLength
file write. source
is the FixedLength
file stream object. In here, you have a chance to inspect the stream
, return true
to continue the FixedLength
generation. Return false
to stop the generation.
Listing 10.1.1 BeginWrite Callback Sample
public bool BeginWrite(object source)
{
StreamReader sr = source as StreamReader;
return true;
}
This callback invoked once at the end of the FixedLength
file generation. source
is the FixedLength
file stream object. In here, you have a chance to inspect the stream, do any post steps to be performed on the stream.
Listing 10.2.1 EndWrite Callback Sample
public void EndWrite(object source)
{
StreamReader sr = source as StreamReader;
}
This callback invoked before each POCO record object is written to FixedLength
file. target
is the instance of the POCO record object. index
is the line index in the file. source
is the FixedLength
record line. In here, you have a chance to inspect the POCO object, and generate the FixedLength
record line if needed.
Tip: If you want to skip the record from writing, set the source to null.
Tip: If you want to take control of FixedLength record line generation, set the source to valid FixedLength record line text.
Return true
to continue the load process, otherwise return false
to stop the process.
Listing 10.3.1 BeforeRecordWrite Callback Sample
public bool BeforeRecordWrite(object target, int index, ref object source)
{
source = "1,Raj";
return true;
}
This callback invoked after each POCO record object is written to FixedLength
file. target
is the instance of the POCO record object. index
is the line index in the file. source
is the FixedLength
record line. In here, you have a chance to do any post step operation with the record line.
Return true
to continue the load process, otherwise return false
to stop the process.
Listing 10.4.1 AfterRecordWrite Callback Sample
public bool AfterRecordWrite(object target, int index, object source)
{
string line = source as string;
return true;
}
This callback is invoked if error is encountered while writing POCO record object. target
is the instance of the POCO record object. index
is the line index in the file. source
is the FixedLength
record line. ex
is the exception object. In here, you have a chance to handle the exception. This method is invoked only when Configuration.ErrorMode
is ReportAndContinue
.
Return true
to continue the load process, otherwise return false
to stop the process.
Listing 10.5.1 RecordWriteError Callback Sample
public bool RecordLoadError(object target, int index, object source, Exception ex)
{
string line = source as string;
return true;
}
This callback is invoked before each FixedLength
record column is written to FixedLength
file. target
is the instance of the POCO record object. index
is the line index in the file. propName
is the FixedLength
record property name. value
is the FixedLength
column value. In here, you have a chance to inspect the FixedLength
record property value and perform any custom validations, etc.
Return true
to continue the load process, otherwise return false
to stop the process.
Listing 10.6.1 BeforeRecordFieldWrite Callback Sample
public bool BeforeRecordFieldWrite(object target, int index, string propName, ref object value)
{
return true;
}
This callback is invoked after each FixedLength
record column value is written to FixedLength
file. target
is the instance of the POCO record object. index
is the line index in the file. propName
is the FixedLength
record property name. value
is the FixedLength
column value. Any post field operation can be performed here, like computing other properties, validations, etc.
Return true
to continue the load process, otherwise return false
to stop the process.
Listing 10.7.1 AfterRecordFieldWrite Callback Sample
public bool AfterRecordFieldWrite(object target, int index, string propName, object value)
{
return true;
}
This callback is invoked when error is encountered while writing FixedLength
record column value. target
is the instance of the POCO record object. index
is the line index in the file. propName
is the FixedLength
record property name. value
is the FixedLength
column value. ex
is the exception object. In here, you have a chance to handle the exception. This method is invoked only after the below two sequences of steps performed by the FixedLengthReader
:
FixedLengthWriter
looks for FallbackValue
value of each FixedLength
property. If present, it tries to use it to write. - If the
FallbackValue
value is not present and the Configuration.ErrorMode
is specified as ReportAndContinue
., this callback will be executed.
Return true
to continue the load process, otherwise return false
to stop the process.
Listing 10.8.1 RecordFieldWriteError Callback Sample
public bool RecordFieldWriteError(object target, int index, string propName, object value, Exception ex)
{
return true;
}
FixedLengthWriter
automatically detects and loads the configuration settings from POCO entity. At runtime, you can customize and tweak these parameters before FixedLength
generation. FixedLengthWriter
exposes Configuration
property, it is of ChoFixedLengthRecordConfiguration
object. Using this property, you can perform the customization.
Listing 11.1 Customizing FixedLengthWriter at run-time
class Program
{
static void Main(string[] args)
{
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 1;
rec1.Name = "Mark";
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 2;
rec2.Name = "Jason";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter("Emp.txt"))
{
parser.Configuration.ColumnCountStrict = true;
parser.Write(objs);
}
}
}
So far, the article explained about using FixedLengthWriter
with POCO object. FixedLengthWriter
also supports generating FixedLength
file without POCO entity objects It leverages .NET dynamic feature. The sample below shows how to generate FixedLength
stream using dynamic objects. The FixedLength
schema is determined from first object. If there is a mismatch found in the dynamic objects member values, an error will be raised and stop the generation process.
The sample below shows it:
Listing 12.1 Generating FixedLength file from dynamic objects
class Program
{
static void Main(string[] args)
{
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 1;
rec1.Name = "Mark";
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 2;
rec2.Name = "Jason";
objs.Add(rec2);
using (var parser = new ChoFixedLengthWriter("Emp.txt"))
{
parser.Configuration.ColumnCountStrict = true;
parser.Write(objs);
}
}
}
FixedLengthReader
throws different types of exceptions in different situations:
ChoParserException
- FixedLength
file is bad and parser not able to recover ChoRecordConfigurationException
- Any invalid configuration settings are specified, this exception will be raised ChoMissingRecordFieldException
- A property is missing for a FixedLength
column, this exception will be raised
If the POCO object property contains a new line/single quote/field separator, it must be specified QuoteField
as true
to handle this situation gracefully. Otherwise, an error will be raised and stop the processing.
Listing 14.1.1 Multiline column values in FixedLength file
[ChoFixedLengthFileHeader]
[ChoFixedLengthRecordObject]
public class EmployeeRec
{
[ChoFixedLengthRecordField(0, 8)]
[Required]
[ChoFallbackValue(100)]
[Range(100, 10000)]
public int? Id
{
get;
set;
}
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[DefaultValue("XXXX")]
public string Name
{
get;
set;
}
public override string ToString()
{
return "{0}. {1}.".FormatString(Id, Name);
}
}
Cinchoo ETL works better with data annotation's MetadataType
model. It is way to attach MetaData
class to data model class. In this associated class, you provide additional metadata information that is not in the data model. Its role is to add attribute to a class without having to modify this one. You can add this attribute that takes a single parameter to a class that will have all the attributes. This is useful when the POCO classes are auto generated (by Entity Framework, MVC, etc.) by an automatic tools. This is why second class comes into play. You can add new stuff without touching the generated file. Also, this promotes modularization by separating the concerns into multiple classes.
For more information about it, please search MSDN.
Listing 15.1 MetadataType annotation usage sample
[MetadataType(typeof(EmployeeRecMeta))]
public class EmployeeRec
{
public int Id { get; set; }
public string Name { get; set; }
}
[ChoFixedLengthFileHeader]
[ChoFixedLengthRecordObject
(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.ThrowAndStop,
IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false,
ObjectValidationMode = ChoObjectValidationMode.MemberLevel)]
public class EmployeeRecMeta : IChoNotifyRecordWrite, IChoValidatable
{
[ChoFixedLengthRecordField(0, 8, ErrorMode = ChoErrorMode.ReportAndContinue )]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, 1, ErrorMessage = "Id must be > 0.")]
[ChoFallbackValue(1)]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, QuoteField = true)]
[StringLength(1)]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
public bool AfterRecordFieldWrite(object target, int index, string propName, object value)
{
throw new NotImplementedException();
}
public bool AfterRecordWrite(object target, int index, object source)
{
throw new NotImplementedException();
}
public bool BeforeRecordFieldWrite
(object target, int index, string propName, ref object value)
{
throw new NotImplementedException();
}
public bool BeforeRecordWrite(object target, int index, ref object source)
{
throw new NotImplementedException();
}
public bool BeginWrite(object source)
{
throw new NotImplementedException();
}
public void EndWrite(object source)
{
throw new NotImplementedException();
}
public bool RecordFieldWriteError
(object target, int index, string propName, object value, Exception ex)
{
throw new NotImplementedException();
}
public bool RecordWriteError(object target, int index, object source, Exception ex)
{
throw new NotImplementedException();
}
public bool TryValidate(object target, ICollection<ValidationResult> validationResults)
{
return true;
}
public bool TryValidateFor
(object target, string memberName,
ICollection<ValidationResult> validationResults)
{
return true;
}
public void Validate(object target)
{
}
public void ValidateFor(object target, string memberName)
{
}
}
In the above EmployeeRec
is the data class. It contains only domain specific properties and operations. Mark it as a very simple class to look at it.
We separate the validation, callback mechanism, configuration, etc. into metadata type class, EmployeeRecMeta
.
If the POCO entity class is an auto-generated class or exposed via library or it is a sealed
class, it limits you to attach FixedLength
schema definition to it declaratively. In such case, you can choose one of the options below to specify FixedLength
layout configuration:
- Manual Configuration
- Auto Map Configuration
- Attaching
MetadataType
class
I'm going to show you how to configure the below POCO entity class on each approach.
Listing 16.1 Sealed POCO entity class
public sealed class EmployeeRec
{
public int Id { get; set; }
public string Name { get; set; }
}
Define a brand new configuration object from scratch and add all the necessary FixedLength
fields to the ChoFixedLengthConfiguration.FixedLengthRecordFieldConfigurations
collection property. This option gives you greater flexibility to control the configuration of FixedLength
parsing. But the downside is that possibility of making mistakes and hard to manage them if the FixedLength
file layout is large.
Listing 16.1.1 Manual Configuration
ChoFixedLengthRecordConfiguration config = new ChoFixedLengthRecordConfiguration();
config.FixedLengthFileHeaderConfiguration.HasHeaderRecord = true;
config.ThrowAndStopOnMissingField = true;
config.FixedLengthRecordFieldConfigurations.Add
(new ChoFixedLengthRecordFieldConfiguration("Id", 0, 8, fieldType: typeof(int)));
config.FixedLengthRecordFieldConfigurations.Add
(new ChoFixedLengthRecordFieldConfiguration("Name", 8, 10));
This is an alternative approach and very less error-prone method to auto map the FixedLength
columns for the POCO entity class.
First, define a schema class for EmployeeRec
POCO entity class as below.
Listing 16.2.1 Auto Map class
public class EmployeeRecMap
{
[ChoFixedLengthRecordField(0, 8, FieldName = "id")]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, FieldName = "Name")]
public string Name { get; set; }
}
Then you can use it to auto map FixedLength
columns by using ChoFixedLengthRecordConfiguration.MapRecordFields
method.
Listing 16.2.2 Using Auto Map configuration
ChoFixedLengthRecordConfiguration config = new ChoFixedLengthRecordConfiguration();
config.MapRecordFields<EmployeeRecMap>();
foreach (var e in new ChoFixedLengthReader<EmployeeRec>("Emp.txt", config))
Console.WriteLine(e.ToString());
This is one other approach to attach MetadataType
class for POCO entity object. The previous approach simply cares about auto mapping of FixedLength
columns only. Other configuration properties like property converters, parser parameters, default/fallback values, etc. are not considered.
This model accounts for everything by defining MetadataType
class and specifying the FixedLength
configuration parameters declaratively. This is useful when your POCO entity is sealed
and not partial class. Also, it is one of the favorable and less error-prone approaches to configure FixedLength
parsing of POCO entity.
Listing 16.3.1 Define MetadataType class
[ChoFixedLengthFileHeader()]
[ChoFixedLengthRecordObject
(Encoding = "Encoding.UTF32", ErrorMode = ChoErrorMode.ReportAndContinue,
IgnoreFieldValueMode = ChoIgnoreFieldValueMode.All, ThrowAndStopOnMissingField = false,
ObjectValidationMode = ChoObjectValidationMode.MemberLevel)]
public class EmployeeRecMeta : IChoNotifyRecordWrite, IChoValidatable
{
[ChoFixedLengthRecordField
(0, 8, FieldName = "id", ErrorMode = ChoErrorMode.ReportAndContinue )]
[ChoTypeConverter(typeof(IntConverter))]
[Range(1, 1, ErrorMessage = "Id must be > 0.")]
public int Id { get; set; }
[ChoFixedLengthRecordField(8, 10, FieldName = "Name", QuoteField = true)]
[StringLength(1)]
[DefaultValue("ZZZ")]
[ChoFallbackValue("XXX")]
public string Name { get; set; }
public bool AfterRecordFieldWrite(object target, int index, string propName, object value)
{
throw new NotImplementedException();
}
public bool AfterRecordWrite(object target, int index, object source)
{
throw new NotImplementedException();
}
public bool BeforeRecordFieldWrite
(object target, int index, string propName, ref object value)
{
throw new NotImplementedException();
}
public bool BeforeRecordWrite(object target, int index, ref object source)
{
throw new NotImplementedException();
}
public bool BeginWrite(object source)
{
throw new NotImplementedException();
}
public void EndWrite(object source)
{
throw new NotImplementedException();
}
public bool RecordFieldWriteError
(object target, int index, string propName, object value, Exception ex)
{
throw new NotImplementedException();
}
public bool RecordWriteError(object target, int index, object source, Exception ex)
{
throw new NotImplementedException();
}
public bool TryValidate
(object target, ICollection<ValidationResult> validationResults)
{
return true;
}
public bool TryValidateFor
(object target, string memberName,
ICollection<ValidationResult> validationResults)
{
return true;
}
public void Validate(object target)
{
}
public void ValidateFor(object target, string memberName)
{
}
}
Listing 16.3.2 Attaching MetadataType class
ChoMetadataObjectCache.Default.Attach<EmployeeRec>(new EmployeeRecMeta());
using (var tx = File.OpenWrite("Emp.txt"))
{
using (var parser = new ChoFixedLengthWriter<EmployeeRec>(tx))
{
parser.Write(objs);
}
}
This is a little nifty helper method to generate FixedLength
formatted output from a list of objects. It helps you to run and play with different options to see the FixedLength
output quickly in a test environment.
static void ToTextTest()
{
List<EmployeeRec> objs = new List<EmployeeRec>();
EmployeeRec rec1 = new EmployeeRec();
rec1.Id = 10;
rec1.Name = "Mark";
objs.Add(rec1);
EmployeeRec rec2 = new EmployeeRec();
rec2.Id = 200;
rec2.Name = "Lou";
objs.Add(rec2);
Console.WriteLine(ChoFixedLengthWriter.ToText(objs));
}
This helper method lets you create FixedLength
file / stream from ADO.NET DataReader
.
static void WriteDataReaderTest()
{
string connString = @"Data Source=(localdb)\v11.0;Initial Catalog=TestDb;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Members", conn);
IDataReader dr = cmd.ExecuteReader();
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser = new ChoFixedLengthWriter(writer, config))
{
parser.Write(dr);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
This helper method lets you create FixedLength
file / stream from ADO.NET DataTable
.
static void WriteDataTableTest()
{
string connString = @"Data Source=(localdb)\v11.0;Initial Catalog=TestDb;Integrated Security=True";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Members", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser = new ChoFixedLengthWriter(writer, config))
{
parser.Write(dt);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
Cinchoo ETL automatically parses and converts each FixedLength
column values to the corresponding FixedLength
column's underlying data type seamlessly. Most of the basic .NET types are handled automatically without any setup needed.
This is achieved through two key settings in the ETL system:
ChoFixedLengthRecordConfiguration.CultureInfo
- Represents information about a specific culture including the names of the culture, the writing system, and the calendar used, as well as access to culture-specific objects that provide information for common operations, such as formatting dates and sorting string
s. Default is 'en-US
'. ChoTypeConverterFormatSpec
- It is global format specifier class that holds all the intrinsic .NET types formatting specs.
In this section, I'm going to talk about changing the default format specs for each .NET intrinsic data types according to parsing needs.
ChoTypeConverterFormatSpec
is a singleton class, the instance is exposed via 'Instance
' static
member. It is thread local, which means that there will be a separate instance copy kept on each thread.
There are 2 sets of format specs members given to each intrinsic type, one for loading and another one for writing the value, except for Boolean
, Enum
, DataTime
types. These types have only one member for both loading and writing operations.
Specifying each intrinsic data type format specs through ChoTypeConverterFormatSpec
will impact system wide, i.e., Setting ChoTypeConverterFormatSpec.IntNumberStyle = NumberStyles.AllowParentheses
will impact all integer members of FixedLength
objects to allow parentheses. If you want to override this behavior and take control of specific FixedLength
data member to handle its own unique parsing of FixedLength
value from global system wide setting, it can be done by specifying TypeConverter
at the FixedLength
field member level. Refer to section 13.4 for more information.
Listing 20.1.1 ChoTypeConverterFormatSpec Members
public class ChoTypeConverterFormatSpec
{
public static readonly ThreadLocal<ChoTypeConverterFormatSpec> Instance =
new ThreadLocal<ChoTypeConverterFormatSpec>(() => new ChoTypeConverterFormatSpec());
public string DateTimeFormat { get; set; }
public ChoBooleanFormatSpec BooleanFormat { get; set; }
public ChoEnumFormatSpec EnumFormat { get; set; }
public NumberStyles? CurrencyNumberStyle { get; set; }
public string CurrencyFormat { get; set; }
public NumberStyles? BigIntegerNumberStyle { get; set; }
public string BigIntegerFormat { get; set; }
public NumberStyles? ByteNumberStyle { get; set; }
public string ByteFormat { get; set; }
public NumberStyles? SByteNumberStyle { get; set; }
public string SByteFormat { get; set; }
public NumberStyles? DecimalNumberStyle { get; set; }
public string DecimalFormat { get; set; }
public NumberStyles? DoubleNumberStyle { get; set; }
public string DoubleFormat { get; set; }
public NumberStyles? FloatNumberStyle { get; set; }
public string FloatFormat { get; set; }
public string IntFormat { get; set; }
public NumberStyles? IntNumberStyle { get; set; }
public string UIntFormat { get; set; }
public NumberStyles? UIntNumberStyle { get; set; }
public NumberStyles? LongNumberStyle { get; set; }
public string LongFormat { get; set; }
public NumberStyles? ULongNumberStyle { get; set; }
public string ULongFormat { get; set; }
public NumberStyles? ShortNumberStyle { get; set; }
public string ShortFormat { get; set; }
public NumberStyles? UShortNumberStyle { get; set; }
public string UShortFormat { get; set; }
}
The sample below shows how to load FixedLength
data stream having 'se-SE
' (Swedish) culture specific data using FixedLengthReader
. Also, the input feed comes with 'EmployeeNo
' values containing parentheses. In order to make the load successful, we have to set the ChoTypeConverterFormatSpec.IntNumberStyle
to NumberStyles.AllowParenthesis
.
Listing 20.1.2 Using ChoTypeConverterFormatSpec in code
static void FormatSpecDynamicTest()
{
ChoTypeConverterFormatSpec.Instance.DateTimeFormat = "d";
ChoTypeConverterFormatSpec.Instance.BooleanFormat = ChoBooleanFormatSpec.YOrN;
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 10;
rec1.Name = "Mark";
rec1.JoinedDate = new DateTime(2001, 2, 2);
rec1.IsActive = true;
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 200;
rec2.Name = "Lou";
rec2.JoinedDate = new DateTime(1990, 10, 23);
rec2.IsActive = false;
objs.Add(rec2);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser = new ChoFixedLengthWriter(writer).WithFirstLineHeader())
{
parser.Write(objs);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
Cinchoo ETL provides ChoCurrency
object to read and write currency values in FixedLength
files. ChoCurrency
is a wrapper class to hold the currency value in decimal type along with support of serializing them in text format during FixedLength
load.
Listing 20.2.1 Using Currency members in dynamic model
static void CurrencyDynamicTest()
{
ChoTypeConverterFormatSpec.Instance.CurrencyFormat = "C2";
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 10;
rec1.Name = "Mark";
rec1.JoinedDate = new DateTime(2001, 2, 2);
rec1.IsActive = true;
rec1.Salary = new ChoCurrency(100000);
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 200;
rec2.Name = "Lou";
rec2.JoinedDate = new DateTime(1990, 10, 23);
rec2.IsActive = false;
rec2.Salary = new ChoCurrency(150000);
objs.Add(rec2);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser =
new ChoFixedLengthWriter(writer).WithFirstLineHeader().QuoteAllFields())
{
parser.Write(objs);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
The sample above shows how to output currency values using dynamic object model. As the currency output will have thousand comma separator, this will fail to generate FixedLength
file. To overcome this issue, we specify the writer to quote all fields.
PS: The format of the currency value is figured by FixedLengthReader through ChoRecordConfiguration.Culture and ChoTypeConverterFormatSpec.CurrencyFormat.
The sample below shows how to use ChoCurrency FixedLength
field in POCO entity class.
Listing 20.2.2 Using Currency members in POCO model
public class EmployeeRecWithCurrency
{
public int Id { get; set; }
public string Name { get; set; }
public ChoCurrency Salary { get; set; }
}
static void CurrencyPOCOTest()
{
List<EmployeeRecWithCurrency> objs = new List<EmployeeRecWithCurrency>();
EmployeeRecWithCurrency rec1 = new EmployeeRecWithCurrency();
rec1.Id = 10;
rec1.Name = "Mark";
rec1.Salary = new ChoCurrency(100000);
objs.Add(rec1);
EmployeeRecWithCurrency rec2 = new EmployeeRecWithCurrency();
rec2.Id = 200;
rec2.Name = "Lou";
rec2.Salary = new ChoCurrency(150000);
objs.Add(rec2);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser =
new ChoFixedLengthWriter<EmployeeRecWithCurrency>(writer).WithFirstLineHeader().
WithField("Id", 0, 5).
WithField("Name", 5, 20).
WithField("Salary", 25, 10))
{
parser.Write(objs);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
Cinchoo ETL implicitly handles parsing/writing of enum
column values from FixedLength
files. If you want to fine control the parsing of these values, you can specify them globally via ChoTypeConverterFormatSpec.EnumFormat
. Default is ChoEnumFormatSpec.Value
.
FYI, changing this value will impact system wide.
There are 3 possible values that can be used:
ChoEnumFormatSpec.Value
- Enum
value is used for parsing ChoEnumFormatSpec.Name
- Enum
key name is used for parsing ChoEnumFormatSpec.Description
- If each enum
key is decorated with DescriptionAttribute
, its value will be used for parsing
Listing 20.3.1 Specifying Enum format specs during parsing
public enum EmployeeType
{
[Description("Full Time Employee")]
Permanent = 0,
[Description("Temporary Employee")]
Temporary = 1,
[Description("Contract Employee")]
Contract = 2
}
static void EnumTest()
{
ChoTypeConverterFormatSpec.Instance.EnumFormat = ChoEnumFormatSpec.Description;
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 10;
rec1.Name = "Mark";
rec1.JoinedDate = new DateTime(2001, 2, 2);
rec1.IsActive = true;
rec1.Salary = new ChoCurrency(100000);
rec1.Status = EmployeeType.Permanent;
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 200;
rec2.Name = "Lou";
rec2.JoinedDate = new DateTime(1990, 10, 23);
rec2.IsActive = false;
rec2.Salary = new ChoCurrency(150000);
rec2.Status = EmployeeType.Contract;
objs.Add(rec2);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser = new ChoFixedLengthWriter(writer).WithFirstLineHeader().
WithField("Id", 0, 5).
WithField("Name", 5, 20).
WithField("JoinedDate", 25, 10).
WithField("IsActive", 35, 1).
WithField("Salary", 36, 10).
WithField("Status", 46, 10)
)
{
parser.Write(objs);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
Cinchoo ETL implicitly handles parsing/writing of boolean FixedLength
column values from FixedLength
files. If you want to fine control the parsing of these values, you can specify them globally via ChoTypeConverterFormatSpec.BooleanFormat
. Default value is ChoBooleanFormatSpec.ZeroOrOne
.
FYI, changing this value will impact system wide.
There are 4 possible values that can be used:
ChoBooleanFormatSpec.ZeroOrOne
- '0
' for false
. '1
' for true
. ChoBooleanFormatSpec.YOrN
- 'Y
' for true
, 'N
' for false
. ChoBooleanFormatSpec.TrueOrFalse
- 'True
' for true
, 'False
' for false
. ChoBooleanFormatSpec.YesOrNo
- 'Yes
' for true
, 'No
' for false
.
Listing 20.4.1 Specifying boolean format specs during parsing
static void BoolTest()
{
ChoTypeConverterFormatSpec.Instance.BooleanFormat = ChoBooleanFormatSpec.YOrN;
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 10;
rec1.Name = "Mark";
rec1.JoinedDate = new DateTime(2001, 2, 2);
rec1.IsActive = true;
rec1.Salary = new ChoCurrency(100000);
rec1.Status = EmployeeType.Permanent;
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 200;
rec2.Name = "Lou";
rec2.JoinedDate = new DateTime(1990, 10, 23);
rec2.IsActive = false;
rec2.Salary = new ChoCurrency(150000);
rec2.Status = EmployeeType.Contract;
objs.Add(rec2);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser =
new ChoFixedLengthWriter(writer).WithFirstLineHeader().WithField("Id", 0, 5).
WithField("Name", 5, 20).
WithField("JoinedDate", 25, 10).
WithField("IsActive", 35, 1).
WithField("Salary", 36, 10).
WithField("Status", 46, 10)
)
{
parser.Write(objs);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
Cinchoo ETL implicitly handles parsing/writing of datetime FixedLength
column values from FixedLength
files using system Culture
or custom set culture. If you want to fine control the parsing of these values, you can specify them globally via ChoTypeConverterFormatSpec.DateTimeFormat
. Default value is 'd
'.
FYI, changing this value will impact system wide.
You can use any valid standard or custom datetime .NET format specification to parse the datetime FixedLength
values from the file.
Listing 20.5.1 Specifying datetime format specs during parsing
static void DateTimeDynamicTest()
{
ChoTypeConverterFormatSpec.Instance.DateTimeFormat = "MMM dd, yyyy";
List<ExpandoObject> objs = new List<ExpandoObject>();
dynamic rec1 = new ExpandoObject();
rec1.Id = 10;
rec1.Name = "Mark";
rec1.JoinedDate = new DateTime(2001, 2, 2);
rec1.IsActive = true;
rec1.Salary = new ChoCurrency(100000);
objs.Add(rec1);
dynamic rec2 = new ExpandoObject();
rec2.Id = 200;
rec2.Name = "Lou";
rec2.JoinedDate = new DateTime(1990, 10, 23);
rec2.IsActive = false;
rec2.Salary = new ChoCurrency(150000);
objs.Add(rec2);
using (var stream = new MemoryStream())
using (var reader = new StreamReader(stream))
using (var writer = new StreamWriter(stream))
using (var parser = new ChoFixedLengthWriter(writer).WithFirstLineHeader().
WithField("Name", 5, 20).
WithField("JoinedDate", 25, 10).
WithField("IsActive", 35, 1).
WithField("Salary", 36, 10).
WithField("Status", 46, 10)
)
{
parser.Write(objs);
writer.Flush();
stream.Position = 0;
Console.WriteLine(reader.ReadToEnd());
}
}
The sample above shows how to generate custom datetime
values to FixedLength
file.
Note: As the datetime
values contain FixedLength
separator, we instruct the writer to quote all fields.
For more information about Cinchoo ETL, please visit the other CodeProject articles: