Work with flat files using fluent type mapping, with support for ADO.NET.
Download using NuGet: FlatFiles
A lot of us still need to work with flat files (e.g. CSV or fixed-length) whether because we're interfacing with older systems or because we're running one-time migration scripts. It's a pain that there's nothing built into .NET for treating flat files like a database table.
FlatFiles makes it easy to read and write flat files in many different ways. It supports type mappers for directly reading and writing with data objects, using a style similar to Entity Framework Code First. You can also go back and forth between files and DataTables. It also supports the ability to expose a file using the IDataReader interface, for working with the low-level ADO.NET classes. If you really want to, you can read and write values with raw object arrays. FlatFiles also supports a large number of options for customizing how files are interpreted, to support the most common types of flat files.
FlatFiles also supports reading Excel files.
Using the type mappers, you can directly read file contents into your classes:
var mapper = SeparatedValueTypeMapper.Define<Customer>();
mapper.Property(c => c.CustomerId).ColumnName("customer_id");
mapper.Property(c => c.Name).ColumnName("name");
mapper.Property(c => c.Created).ColumnName("created").InputFormat("yyyyMMdd");
mapper.Property(c => c.AverageSales).ColumnName("avg_sales");
var customers = mapper.Read(@"C:\path\to\file.csv");
Writing to a file is just as easily:
mapper.Property(c => c.Created).OutputFormat("yyyyMMdd");
mapper.Property(c => c.AverageSales).OutputFormat("N2");
mapper.Write(@"C:\path\to\file2.csv", customers);
Note that the mapper assumes the order Property is called the first time for a particular property matches the order the columns appear in the file. Additional references to the property have no impact on the expected order.
Type mapping internally defines a schema, which defines the name, order and type of each column in the flat file. In order to use the other classes in FlatFiles, you must define the schema explicitly. For instance, this is how we would define a CSV file schema:
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int64Column("customer_id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created") { InputFormat = "yyyyMMdd", OutputFormat = "yyyyMMdd" })
.AddColumn(new DoubleColumn("avg_sales") { OutputFormat = "N2" });
Or, if the schema is for a fixed-length file:
FixedLengthSchema schema = new FixedLengthSchema();
schema.AddColumn(new Int64Column("customer_id"), 10)
.AddColumn(new StringColumn("name"), 255)
.AddColumn(new DateTimeColumn("created", 8) { InputFormat = "yyyyMMdd", OutputFormat = "yyyyMMdd" })
.AddColumn(new DoubleColumn("avg_sales", 10) { OutputFormat = "N2" });
The FixedLengthSchema class is the same as the SeparatedValueSchema class, except it associates a Window to each column. A Window records the Width of the column in the file. It also allows you to specify the Alignment (left or right) in cases where the value doesn't fill the entire width of the column (the default is left aligned). The FillCharacter property can be used to say what character is used as padding.
Some fixed-length files may have columns that are not used. The fixed-length schema doesn't provide a way to specify a starting index for a column. Simply define "ignored" columns for gaps in the input file.
Schemas can be defined for Excel files, too, using the ExcelSchema. The code is very similar to defining a schema for a separated value file.
The type mappers provide a GetSchema method to allow you to define schemas using a fluent syntax.
If you are working with delimited files, such as comma-separated or tab-separated files, you will want to use the SeparatedValueReader class. The constructor accepts a combination of a file name (or stream), a SeparatedValueSchema object and/or a SeparatedValueOptions object.
When parsing separated files, you can surround fields with double or single quotes. This way you can include the separator string within the field. Of course, this won't work if the separator is the quote character itself!
The SeparatedValueOptions class supports a Separator property for specifying the string that separates your fields. A comma (,) is the default separator.
The RecordSeparator property specifies what character sequence is used to separate records. By default, this is Environment.NewLine (\r\n). This is useful if you are working on files from other systems, such as Linux (\n) or Macintosh (\r).
The IsFirstRecordSchema property tells the reader to treat the first record in the file as the schema. Since the types of the fields cannot be determined from a file, they are assumed to be strings. If you provide the schema to the constructor, it will be used instead, and the first record will simply be skipped. By default, this property is set to false.
If you want to build a delimited file, you can use the SeparatedValueWriter class. It accepts the same schema and options arguments. If the SeparatedValueOptions's IsFirstRecordSchema property is set to true, the schema will be written to the file upon writing the first record.
If you are working with files whose fields are a fixed-length you will want to use the FixedLengthReader class. The constructor accepts a combination of a file name (or stream), a FixedLengthSchema object and/or a FixedLengthOptions object.
The FixedLengthOptions class supports a FillCharacter property to specify which character is used as a fill character in the columns. A space ( ) is the default.
It also supports a RecordSeparator property for specifying what value indicates the end of a record. By default, this is Environment.NewLine (\r\n). This is useful if you are working on files from other systems, such as Linux (\n) or Macintosh (\r).
If you want to build a fixed-length file, you can use the FixedLengthWriter class. It accepts the same schema and options arguments used to read files. If you want to control the alignment of the columns, you can specify the FixedAlignment for each column when defining the schema. This will control whether padding is put to the right or the left of the value.
If you are working with an Excel file, you will want to use the ExcelReader class. The constructor accepts a combination of a file name, an ExcelSchema object and an ExcelOptions object.
The ExcelOptions class is used to specify the name of the worksheet to read the data from. It also includes fields for specifying the row/column range. By default, the reader will try to read the entire worksheet.
The IsFirstRecordSchema property tells the reader to treat the first record in the file as the schema. If you provide a schema, the reader will simply skip the first row. Although, it probably makes more sense to simply skip the header in the case of Excel (using the StartingRow option). Otherwise, Excel will try to interpret the type of the column by looking at the values in the worksheet. If you don't provide a schema and set the IsFirstRecordSchema property to false, ExcelReader will simply return the row values in whatever way Excel interprets them.
If your schema's column types do not match what Excel interprets the column type, ExcelReader will do its best to convert to the desired value.
If you are using DataTables, you can read and write to a DataTable using the ReadFlatFile and WriteFlatFile extension methods. Just pass the corresponding reader or writer object.
DataTable customerTable = new DataTable("Customer");
using (IReader reader = new SeparatedValueReader(@"C:\path\to\file.csv", schema))
{
customerTable.ReadFlatFile(reader);
}
For low-level file reading, you can use the FlatFileReader class. It provides an IDataReader interface to the records in the file, making it compatible with other ADO.NET interfaces.
// The DataRead Approach
using (FlatFileReader reader = new FlatFileReader(new SeparatedValueReader(@"C:\path\to\file.csv", schema))
{
List<Customer> customers = new List<Customer>();
while (reader.Read())
{
Customer customer = new Customer();
customer.CustomerId = reader.GetInt32(0);
customer.Name = reader.GetString(1);
customer.Created = reader.GetDateTime(2);
customer.AverageSales = reader.GetDouble(3);
customers.Add(customer);
}
return customers;
}
If you are looking for a license, you won't find one. The software in this project is free, as in "free as air". Feel free to use my software anyway you like. Use it to build up your evil war machine, swindle old people out of their social security or crush the souls of the innocent.
I love to hear how people are using my code, so drop me a line. Feel free to contribute any enhancements or documentation you may come up with, but don't feel obligated. I just hope this code makes someone's life just a little bit easier.