public void TestReadFlatFile_ClearsSchemaAndData()
{
// fill the table with some existing columns, constraints and data
DataTable table = new DataTable();
DataColumn column = table.Columns.Add("blah", typeof(int));
table.Columns.Add("bleh", typeof(string));
table.Constraints.Add("PK_blah", column, true);
DataRow row = table.Rows.Add(new object[] { 123, "dopey" });
row.AcceptChanges();
const string text = @"id,name,created,avg
123,Bob,12/31/2012,3.14159";
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
StringReader stringReader = new StringReader(text);
IReader parser = new SeparatedValueReader(stringReader, options);
table.ReadFlatFile(parser);
Assert.AreEqual(4, table.Columns.Count);
Assert.IsTrue(table.Columns.Contains("id"), "The ID column was not extracted.");
Assert.IsTrue(table.Columns.Contains("name"), "The name column was not extracted.");
Assert.IsTrue(table.Columns.Contains("created"), "The created column was not extracted.");
Assert.IsTrue(table.Columns.Contains("avg"), "The AVG column was not extracted.");
Assert.AreEqual(1, table.Rows.Count);
row = table.Rows[0];
object[] expected = new object[] { "123", "Bob", "12/31/2012", "3.14159" };
object[] values = row.ItemArray;
CollectionAssert.AreEqual(expected, values);
}
public void TestReadFlatFile_Merge_Upsert()
{
// fill the table with some existing columns, constraints and data
DataTable table = new DataTable();
DataColumn idColumn = table.Columns.Add("id", typeof(int));
DataColumn nameColumn = table.Columns.Add("name", typeof(string));
DataColumn createdColumn = table.Columns.Add("created", typeof(DateTime));
DataColumn avgColumn = table.Columns.Add("avg", typeof(decimal));
table.Constraints.Add("PK_blah", idColumn, true);
DataRow row = table.Rows.Add(new object[] { 1, "Bob", new DateTime(2018, 07, 16), 12.34m });
row.AcceptChanges();
row.SetField(avgColumn, 99.99m); // Change but do not accept
const string text = @"id,name,created
1,Robert,07/19/2018,78.90
2,John,07/17/2018,23.45
3,Susan,07/18/2018,34.56";
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
StringReader stringReader = new StringReader(text);
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"));
schema.AddColumn(new StringColumn("name"));
schema.AddColumn(new DateTimeColumn("created"));
schema.AddColumn(new DecimalColumn("avg"));
IReader csvReader = new SeparatedValueReader(stringReader, schema, options);
table.ReadFlatFile(csvReader, LoadOption.Upsert);
Assert.AreEqual(4, table.Columns.Count);
Assert.IsTrue(table.Columns.Contains("id"), "The ID column was not extracted.");
Assert.IsTrue(table.Columns.Contains("name"), "The name column was not extracted.");
Assert.IsTrue(table.Columns.Contains("created"), "The created column was not extracted.");
Assert.IsTrue(table.Columns.Contains("avg"), "The AVG column was not extracted.");
var additions = table.GetChanges(DataRowState.Added);
Assert.IsNotNull(additions);
Assert.AreEqual(2, additions.Rows.Count);
var deletions = table.GetChanges(DataRowState.Deleted);
Assert.IsNull(deletions);
var modifications = table.GetChanges(DataRowState.Modified);
Assert.IsNotNull(modifications);
Assert.AreEqual(1, modifications.Rows.Count);
Assert.AreEqual(3, table.Rows.Count);
CollectionAssert.AreEqual(new object[] { 1, "Robert", new DateTime(2018, 07, 19), 78.90m }, table.Rows[0].ItemArray);
CollectionAssert.AreEqual(new object[] { 2, "John", new DateTime(2018, 07, 17), 23.45m }, table.Rows[1].ItemArray);
CollectionAssert.AreEqual(new object[] { 3, "Susan", new DateTime(2018, 07, 18), 34.56m }, table.Rows[2].ItemArray);
}
public void TestReadFlatFile_IgnoredColumns2()
{
const string data =
@"A,B,C
1,2,3
4,5,6";
var schema = new SeparatedValueSchema();
schema.AddColumn(new StringColumn("A"));
schema.AddColumn(new IgnoredColumn("Ignored"));
schema.AddColumn(new StringColumn("C"));
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
var textReader = new StringReader(data);
var csvReader = new SeparatedValueReader(textReader, schema, options);
DataTable dataTable = new DataTable();
dataTable.ReadFlatFile(csvReader);
string[] columnNames = dataTable.Columns.OfType <DataColumn>()
.Select(r => r.ColumnName)
.ToArray();
CollectionAssert.AreEqual(new[] { "A", "C" }, columnNames);
Assert.AreEqual(2, dataTable.Rows.Count);
object[] values1 = dataTable.Rows[0].ItemArray;
CollectionAssert.AreEqual(new[] { "1", "3" }, values1);
object[] values2 = dataTable.Rows[1].ItemArray;
CollectionAssert.AreEqual(new[] { "4", "6" }, values2);
}
public IList <CommunityEmailRecipient> GetCommunityEmailRecipients(string filename)
{
try
{
var mapper = SeparatedValueTypeMapper.Define <CommunityEmailRecipient>();
mapper.Property(c => c.Community).ColumnName("community");
mapper.Property(c => c.To).ColumnName("to");
mapper.Property(c => c.Cc).ColumnName("cc");
mapper.Property(c => c.Bcc).ColumnName("bcc");
if (File.Exists(filename))
{
using (var reader = new StreamReader(File.OpenRead(filename)))
{
var options = new SeparatedValueOptions {
IsFirstRecordSchema = true
};
List <CommunityEmailRecipient> recipients = mapper.Read(reader, options).ToList();
_logger.LogDebug("Found {Count} community email recipient from {Filename}", recipients.Count, filename);
return(recipients);
}
}
_logger.LogWarning("Mapping {Filename} not found, returning empty result", filename);
return(Array.Empty <CommunityEmailRecipient>());
}
catch (Exception e)
{
_logger.LogError(e, "Failed to read file {Filename}, returning empty result", filename);
return(Array.Empty <CommunityEmailRecipient>());
}
}
public void TestRead_RecordWithCP1251Characters_ReturnsCorrectCharacters()
{
//---- Arrange -----------------------------------------------------
// Need to convert the string to target encoding because otherwise a string declared in VS will always be encoded as UTF-8
var text = Encoding.Convert(Encoding.UTF8, Encoding.GetEncoding(1251), Encoding.UTF8.GetBytes(@"123;Лучиано;1/17/2014"));
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id")).AddColumn(new StringColumn("name")).AddColumn(new DateTimeColumn("created"));
var options = new SeparatedValueOptions
{
IsFirstRecordSchema = false,
Separator = ";",
Encoding = Encoding.GetEncoding(1251)
};
var testee = new SeparatedValueReader(new MemoryStream(text), schema, options);
//---- Act ---------------------------------------------------------
var result = testee.Read();
//---- Assert ------------------------------------------------------
Assert.IsTrue(result, "Could not read the record.");
object[] expected = { 123, "Лучиано", new DateTime(2014, 1, 17) };
object[] actual = testee.GetValues();
CollectionAssert.AreEqual(expected, actual, "The wrong values were parsed.");
}
public void TestRead_WithSeparatedRecordFilter_SkipsRecordsMatchingCriteria()
{
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created"));
SeparatedValueOptions options = new SeparatedValueOptions()
{
PartitionedRecordFilter = (record) => record.Length < 3
};
const string text = @"123,Bob Smith,4/21/2017
This is not a real record
234,Jay Smith,5/21/2017";
StringReader stringReader = new StringReader(text);
IReader parser = new SeparatedValueReader(stringReader, schema, options);
Assert.True(parser.Read(), "Could not read the first record.");
object[] actual1 = parser.GetValues();
Assert.Equal(new object[] { 123, "Bob Smith", new DateTime(2017, 04, 21) }, actual1);
Assert.True(parser.Read(), "Could not read the second record.");
object[] actual2 = parser.GetValues();
Assert.Equal(new object[] { 234, "Jay Smith", new DateTime(2017, 05, 21) }, actual2);
Assert.False(parser.Read(), "There should not be any more records.");
}
public void TestGetValues_BlankLeadingSection_ReturnsNull()
{
using (MemoryStream stream = new MemoryStream())
{
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new StringColumn("leading"))
.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created")
{
InputFormat = "M/d/yyyy", OutputFormat = "M/d/yyyy"
});
object[] sources = new object[] { "", 123, "Bob", new DateTime(2013, 1, 19) };
StringWriter stringWriter = new StringWriter();
SeparatedValueWriter builder = new SeparatedValueWriter(stringWriter, schema, options);
builder.Write(sources);
StringReader stringReader = new StringReader(stringWriter.ToString());
SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema, options);
Assert.True(parser.Read(), "No records were found.");
object[] values = parser.GetValues();
Assert.Equal(schema.ColumnDefinitions.Count, values.Length);
Assert.Equal(null, values[0]);
Assert.Equal(sources[1], values[1]);
Assert.Equal(sources[2], values[2]);
Assert.Equal(sources[3], values[3]);
Assert.False(parser.Read(), "Too many records were found.");
}
}
public void TestTypeMapper_RoundtripWithNull()
{
var mapper = SeparatedValueTypeMapper.Define <Person>();
mapper.Property(p => p.Id).ColumnName("id");
mapper.Property(p => p.Name).ColumnName("name");
mapper.Property(p => p.Created).ColumnName("created").InputFormat("yyyyMMdd").OutputFormat("yyyyMMdd");
using (MemoryStream stream = new MemoryStream())
{
var bob = new Person()
{
Id = 123, Name = null, Created = new DateTime(2013, 1, 19)
};
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true, Separator = "\t"
};
mapper.Write(stream, options, new Person[] { bob });
stream.Position = 0; // go back to the beginning of the stream
var people = mapper.Read(stream, options);
Assert.AreEqual(1, people.Count(), "The wrong number of people were returned.");
var person = people.SingleOrDefault();
Assert.AreEqual(bob.Id, person.Id, "The ID value was not persisted.");
Assert.AreEqual(bob.Name, person.Name, "The Name value was not persisted.");
Assert.AreEqual(bob.Created, person.Created, "The Created value was not persisted.");
}
}
public void TestGetValues_BlankLeadingSection_ReturnsNull()
{
using (MemoryStream stream = new MemoryStream())
{
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new StringColumn("leading"))
.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created")
{
InputFormat = "M/d/yyyy", OutputFormat = "M/d/yyyy"
});
object[] sources = new object[] { "", 123, "Bob", new DateTime(2013, 1, 19) };
using (SeparatedValueWriter builder = new SeparatedValueWriter(stream, schema, options))
{
builder.Write(sources);
}
stream.Position = 0;
SeparatedValueReader parser = new SeparatedValueReader(stream, schema, options);
Assert.IsTrue(parser.Read(), "No records were found.");
object[] values = parser.GetValues();
Assert.AreEqual(schema.ColumnDefinitions.Count, values.Length, "The wrong number of values were read.");
Assert.AreEqual(null, values[0], "The first column was not interpreted as null.");
Assert.AreEqual(sources[1], values[1], "The second column was not parsed correctly.");
Assert.AreEqual(sources[2], values[2], "The third column was not parsed correctly.");
Assert.AreEqual(sources[3], values[3], "The forth column was not parsed correctly.");
Assert.IsFalse(parser.Read(), "Too many records were found.");
}
}
public IEnumerable <T> GetData(string filePath, string separator = ";")
{
using (StreamReader streamReader = new StreamReader(filePath))
{
SeparatedValueOptions options = new SeparatedValueOptions {
IsFirstRecordSchema = true, Separator = separator, Quote = '\''
};
var separatedValueSchema = new SeparatedValueSchema();
var properties = Type.GetProperties();
foreach (var property in properties)
{
var propertyName = property.Name;
var columnDefinition = GetColumnDefinition(property.PropertyType, propertyName);
separatedValueSchema.AddColumn(columnDefinition);
}
SeparatedValueReader separatedValueReader = new SeparatedValueReader(streamReader, separatedValueSchema, options);
List <T> values = new List <T>();
while (separatedValueReader.Read())
{
var rowValues = separatedValueReader.GetValues();
var tRow = new T();
var propertiesObjectValues = properties.Zip(rowValues, (a, b) => new { PropType = a, PropValue = b });
foreach (var propertiesObjectValue in propertiesObjectValues)
{
Type.GetProperty(propertiesObjectValue.PropType.Name).SetValue(tRow, propertiesObjectValue.PropValue);
}
values.Add(tRow);
}
return(values);
}
}
public void TestReadFlatFile_DataTableNull_Throws()
{
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created")
{
InputFormat = "MM/dd/yyyy"
})
.AddColumn(new DecimalColumn("avg"));
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
using (MemoryStream stream = new MemoryStream())
{
using (SeparatedValueWriter builder = new SeparatedValueWriter(stream, schema, options))
{
builder.Write(new object[] { 123, "Bob", new DateTime(2012, 12, 31), 3.14159m });
}
stream.Position = 0;
DataTable table = null;
IReader parser = new SeparatedValueReader(stream, options);
DataTableExtensions.ReadFlatFile(table, parser);
}
}
public void TestReadFlatFile_ClearsSchemaAndData()
{
// fill the table with some existing columns, constraints and data
DataTable table = new DataTable();
DataColumn column = table.Columns.Add("blah", typeof(int));
table.Columns.Add("bleh", typeof(string));
table.Constraints.Add("PK_blah", column, true);
DataRow row = table.Rows.Add(new object[] { 123, "dopey" });
row.AcceptChanges();
const string text = @"id,name,created,avg
123,Bob,12/31/2012,3.14159";
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
StringReader stringReader = new StringReader(text);
IReader parser = new SeparatedValueReader(stringReader, options);
table.ReadFlatFile(parser);
Assert.Equal(4, table.Columns.Count);
Assert.True(table.Columns.Contains("id"), "The ID column was not extracted.");
Assert.True(table.Columns.Contains("name"), "The name column was not extracted.");
Assert.True(table.Columns.Contains("created"), "The created column was not extracted.");
Assert.True(table.Columns.Contains("avg"), "The AVG column was not extracted.");
Assert.Equal(1, table.Rows.Count);
row = table.Rows[0];
object[] expected = new object[] { "123", "Bob", "12/31/2012", "3.14159" };
object[] values = row.ItemArray;
Assert.Equal(expected, values);
}
public void TestReadFlatFile_ExtractsSchema_PopulatesTable()
{
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created") { InputFormat = "MM/dd/yyyy", OutputFormat = "MM/dd/yyyy" })
.AddColumn(new DecimalColumn("avg"));
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
StringWriter stringWriter = new StringWriter();
SeparatedValueWriter builder = new SeparatedValueWriter(stringWriter, schema, options);
builder.Write(new object[] { 123, "Bob", new DateTime(2012, 12, 31), 3.14159m });
StringReader stringReader = new StringReader(stringWriter.ToString());
DataTable table = new DataTable();
IReader parser = new SeparatedValueReader(stringReader, options);
table.ReadFlatFile(parser);
Assert.Equal(4, table.Columns.Count);
Assert.True(table.Columns.Contains("id"), "The ID column was not extracted.");
Assert.True(table.Columns.Contains("name"), "The name column was not extracted.");
Assert.True(table.Columns.Contains("created"), "The created column was not extracted.");
Assert.True(table.Columns.Contains("avg"), "The AVG column was not extracted.");
Assert.Equal(1, table.Rows.Count);
DataRow row = table.Rows[0];
object[] expected = new object[] { "123", "Bob", "12/31/2012", "3.14159" };
object[] values = row.ItemArray;
Assert.Equal(expected, values);
}
public void TestTypeMapper_BadRecordColumn_SkipError()
{
const string data = @"1,2017-06-11,John Smith
2,2017-12-32,Tom Stallon
3,2017-08-13,Walter Kay";
var mapper = SeparatedValueTypeMapper.Define <Person>();
mapper.Property(x => x.Id);
mapper.Property(x => x.Created);
mapper.Property(x => x.Name);
StringReader stringReader = new StringReader(data);
List <int> errorRecords = new List <int>();
var options = new SeparatedValueOptions()
{
ErrorHandler = (sender, e) =>
{
errorRecords.Add(e.RecordNumber);
e.IsHandled = true;
}
};
var people = mapper.Read(stringReader, options).ToArray();
Assert.Equal(2, people.Count());
Assert.Equal(1, errorRecords.Count);
Assert.Equal(2, errorRecords[0]);
}
public void TestTypeWriter_AnonymousType()
{
var people = from id in Enumerable.Range(0, 1)
select new
{
Id = id,
Name = "Bob " + id,
Created = new DateTime(2013, 1, 19)
};
var mapper = SeparatedValueTypeMapper.DefineWriter(people);
mapper.Property(p => p.Id).ColumnName("id");
mapper.Property(p => p.Name).ColumnName("name");
mapper.Property(p => p.Created).ColumnName("created").InputFormat("yyyyMMdd").OutputFormat("yyyyMMdd");
using (MemoryStream stream = new MemoryStream())
{
var options = new SeparatedValueOptions() { IsFirstRecordSchema = true, Separator = "\t" };
mapper.Write(stream, options);
stream.Position = 0; // go back to the beginning of the stream
SeparatedValueSchema schema = mapper.GetSchema();
FlatFileReader reader = new FlatFileReader(new SeparatedValueReader(stream, schema, options));
Assert.IsTrue(reader.Read(), "The writer did not write the entities.");
int id = reader.GetInt32(0);
string name = reader.GetString(1);
DateTime created = reader.GetDateTime(2);
Assert.AreEqual(people.First().Id, id, "The ID value was not persisted.");
Assert.AreEqual(people.First().Name, name, "The Name value was not persisted.");
Assert.AreEqual(people.First().Created, created, "The Created value was not persisted.");
Assert.IsFalse(reader.Read(), "The writer wrote too many records.");
}
}
public void TestCtor_Options_Schema_TextNull_Throws()
{
string text = null;
SeparatedValueSchema schema = new SeparatedValueSchema();
SeparatedValueOptions options = new SeparatedValueOptions();
new SeparatedValueReader(text, schema, options);
}
public void TestCtor_Options_TextNull_Throws()
{
string text = null;
SeparatedValueOptions options = new SeparatedValueOptions();
new SeparatedValueReader(text, options);
}
public void TestCtor_Options_SchemaNull_Throws()
{
string text = String.Empty;
SeparatedValueSchema schema = null;
SeparatedValueOptions options = new SeparatedValueOptions();
new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), schema, options);
}
public void TestCtor_OptionsNull_Throws()
{
string text = "";
SeparatedValueOptions options = null;
new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), options);
}
public void ShouldPreserveLeadingAndTrailingWhitespaceIfConfigured_MultipleSpaces_TwoColumn()
{
string source = " a , \t\n b \t\n ";
StringReader stringReader = new StringReader(source);
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new StringColumn("a")
{
Trim = false
});
schema.AddColumn(new StringColumn("b")
{
Trim = false
});
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = false, RecordSeparator = "\r\n", PreserveWhiteSpace = true
};
SeparatedValueReader reader = new SeparatedValueReader(stringReader, schema, options);
object[][] expected = new object[][]
{
new object[] { " a ", " \t\n b \t\n " }
};
assertRecords(expected, reader);
}
public void TestTypeMapper_RoundtripWithNull()
{
var mapper = SeparatedValueTypeMapper.Define <Person>();
mapper.Property(p => p.Id).ColumnName("id");
mapper.Property(p => p.Name).ColumnName("name");
mapper.Property(p => p.Created).ColumnName("created").InputFormat("yyyyMMdd").OutputFormat("yyyyMMdd");
var bob = new Person()
{
Id = 123, Name = null, Created = new DateTime(2013, 1, 19)
};
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true, Separator = "\t"
};
StringWriter stringWriter = new StringWriter();
mapper.Write(stringWriter, new Person[] { bob }, options);
StringReader stringReader = new StringReader(stringWriter.ToString());
var people = mapper.Read(stringReader, options).ToArray();
Assert.Equal(1, people.Count());
var person = people.SingleOrDefault();
Assert.Equal(bob.Id, person.Id);
Assert.Equal(bob.Name, person.Name);
Assert.Equal(bob.Created, person.Created);
}
public void TestRead_ZeroLengthColumn()
{
//---- Arrange -----------------------------------------------------
var text = "104\t20\t1000\t00\tLausanne\tLausanne\tVD\t2\t\t0\t130\t5586\t19880301";
var options = new SeparatedValueOptions {
IsFirstRecordSchema = false, Separator = "\t"
};
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("OnrpId"))
.AddColumn(new Int32Column("Type"))
.AddColumn(new Int32Column("ZipCode"))
.AddColumn(new StringColumn("ZipCodeAddOn"))
.AddColumn(new StringColumn("TownShortName"))
.AddColumn(new StringColumn("TownOfficialName"))
.AddColumn(new StringColumn("CantonAbbreviation"))
.AddColumn(new Int16Column("MainLanguageCode"))
.AddColumn(new Int16Column("OtherLanguageCode"))
.AddColumn(new ByteColumn("HasSortfileData"))
.AddColumn(new Int32Column("LetterServiceOnrpId"))
.AddColumn(new Int32Column("MunicipalityId"))
.AddColumn(new StringColumn("ValidFrom"));
var testee = new SeparatedValueReader(new MemoryStream(Encoding.GetEncoding(1252).GetBytes(text)), options);
//---- Act ---------------------------------------------------------
var result = testee.Read();
//---- Assert ------------------------------------------------------
Assert.IsTrue(result);
Assert.AreEqual(schema.ColumnDefinitions.Count, testee.GetValues().Count());
}
public void TestCtor_Options_SchemaNull_Throws()
{
string text = String.Empty;
SeparatedValueSchema schema = null;
SeparatedValueOptions options = new SeparatedValueOptions();
new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), schema, options);
}
public void TestReader_WithSchema_SchemaNotCounted_WithFilter_LineCount()
{
var mapper = SeparatedValueTypeMapper.Define(() => new Person());
mapper.Property(x => x.Name);
var people = new[]
{
new Person()
{
Name = "Bob"
},
new Person()
{
Name = "Tom"
},
new Person()
{
Name = "Jane"
}
};
StringWriter writer = new StringWriter();
mapper.Write(writer, people, new SeparatedValueOptions()
{
IsFirstRecordSchema = true
});
string output = writer.ToString();
mapper.CustomMapping(new RecordNumberColumn("RecordNumber")
{
IncludeSchema = false, IncludeSkippedRecords = true
})
.WithReader((p, v) => p.RecordNumber = (int)v)
.WithWriter(p => p.RecordNumber);
StringReader stringReader = new StringReader(output);
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
var reader = mapper.GetReader(stringReader, options);
reader.RecordRead += (sender, e) =>
{
e.IsSkipped = e.Values.Length == 1 && e.Values[0] == "Tom";
};
var results = reader.ReadAll().ToArray();
Assert.AreEqual(2, results.Length);
Assert.AreEqual("Bob", results[0].Name);
Assert.AreEqual(1, results[0].RecordNumber);
Assert.AreEqual("Jane", results[1].Name);
Assert.AreEqual(3, results[1].RecordNumber);
}
public void TestGetSchema_NotExtracted_Throws()
{
string text = "a,b,c";
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = false
};
IReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), options);
parser.GetSchema();
}
public void TestGetSchema_Extracted_ReturnsColumnNames()
{
string text = "a,b,c";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
IReader parser = new SeparatedValueReader(stringReader, options);
ISchema schema = parser.GetSchema();
Assert.True(schema.ColumnDefinitions.All(d => d is StringColumn), "Not all of the columns were treated as strings.");
string[] actual = schema.ColumnDefinitions.Select(d => d.ColumnName).ToArray();
string[] expected = new string[] { "a", "b", "c" };
Assert.Equal(expected, actual);
}
public void ShouldNotFindRecordsInEmptyFile()
{
string source = String.Empty;
StringReader stringReader = new StringReader(source);
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = false
};
SeparatedValueReader reader = new SeparatedValueReader(stringReader, options);
Assert.False(reader.Read(), "No records should be read from an empty file.");
}
public void TestGetSchema_NotExtracted_Throws()
{
string text = "a,b,c";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = false
};
IReader parser = new SeparatedValueReader(stringReader, options);
Assert.Throws <InvalidOperationException>(() => parser.GetSchema());
}
public void TestGetSchema_FirstRecordSchema_WrongNumberOfColumns_Throws()
{
const string text = @"id,name,created
123,Bob,1/19/2013,Hello";
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
SeparatedValueReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), options);
parser.Read();
}
public void TestGetSchema_FirstRecordSchema_TooFewColumns_Throws()
{
const string text = @"id,name,created
123,Bob";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
SeparatedValueReader parser = new SeparatedValueReader(stringReader, options);
Assert.Throws <FlatFileException>(() => parser.Read());
}
public void ShouldThrowSyntaxExceptionIfQuoteFollowedByNonSeparator()
{
string source = "'a'b";
StringReader stringReader = new StringReader(source);
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = false,
Quote = '\''
};
SeparatedValueReader reader = new SeparatedValueReader(stringReader, options);
Assert.Throws <FlatFileException>(() => reader.Read());
}
public void ShouldThrowSyntaxExceptionIfQuoteFollowedByEOS()
{
string source = "'";
StringReader stringReader = new StringReader(source);
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = false,
Quote = '\''
};
SeparatedValueReader reader = new SeparatedValueReader(stringReader, options);
Assert.ThrowsException <RecordProcessingException>(() => reader.Read());
}
public void TestReader_WithSchema_WithIgnoredColumn_WithFilter_LogicalRecordsOnly()
{
var mapper = new SeparatedValueTypeMapper <Person>(() => new Person());
mapper.Property(x => x.Name);
mapper.Ignored();
var people = new[]
{
new Person()
{
Name = "Bob"
},
new Person()
{
Name = "Tom"
},
new Person()
{
Name = "Jane"
}
};
StringWriter writer = new StringWriter();
mapper.Write(writer, people, new SeparatedValueOptions()
{
IsFirstRecordSchema = true
});
string output = writer.ToString();
mapper.CustomProperty(x => x.RecordNumber, new RecordNumberColumn("RecordNumber"));
StringReader stringReader = new StringReader(output);
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
var reader = mapper.GetReader(stringReader, options);
reader.RecordRead += (sender, e) =>
{
e.IsSkipped = e.Values.Length >= 1 && e.Values[0] == "Tom";
};
var results = reader.ReadAll().ToArray();
Assert.AreEqual(2, results.Length);
Assert.AreEqual("Bob", results[0].Name);
Assert.AreEqual(1, results[0].RecordNumber);
Assert.AreEqual("Jane", results[1].Name);
Assert.AreEqual(2, results[1].RecordNumber);
}
public void TestGetSchema_FirstRecordSchema_TooManyColumns_IgnoresTrailing()
{
const string text = @"id,name,created
123,Bob,1/19/2013,Hello";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
SeparatedValueReader parser = new SeparatedValueReader(stringReader, options);
Assert.IsTrue(parser.Read(), "The record could not be read.");
Assert.AreEqual(parser.GetSchema().ColumnDefinitions.Count, parser.GetValues().Length);
}
public void TestReadFlatFile_DuplicateHeaderNames()
{
string data = @"ID,Description,Date,Time,Price,Date,Time,Price
""1"",""Net Profit"",""8/3/2020"",""9:58:48"",""$111.11"",""8/3/2020"",""10:41:10"",""$333.33""
""2"",""Net Loss"",""8/3/2020"",""14:41:10"",""$444.44"",""8/3/2020"",""16:29:08"",""$222.22""";
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("ID"));
schema.AddColumn(new StringColumn("Description"));
schema.AddColumn(new DateTimeColumn("PurchaseDate"));
schema.AddColumn(new TimeSpanColumn("PurchaseTime"));
schema.AddColumn(new DecimalColumn("PurchasePrice")
{
NumberStyles = NumberStyles.Currency
});
schema.AddColumn(new DateTimeColumn("SellDate"));
schema.AddColumn(new TimeSpanColumn("SellTime"));
schema.AddColumn(new DecimalColumn("SellPrice")
{
NumberStyles = NumberStyles.Currency
});
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true
};
var reader = new SeparatedValueReader(new StringReader(data), schema, options);
DataTable dataTable = new DataTable();
dataTable.ReadFlatFile(reader);
Assert.AreEqual(2, dataTable.Rows.Count);
var actualColumns = dataTable.Columns.OfType <DataColumn>().Select(x => x.ColumnName).ToArray();
CollectionAssert.AreEqual(new string[]
{
"ID", "Description", "PurchaseDate", "PurchaseTime", "PurchasePrice", "SellDate", "SellTime", "SellPrice"
}, actualColumns);
CollectionAssert.AreEqual(new object[]
{
1, "Net Profit", new DateTime(2020, 8, 3), new TimeSpan(9, 58, 48),
111.11m, new DateTime(2020, 8, 3), new TimeSpan(10, 41, 10), 333.33m
}, dataTable.Rows[0].ItemArray);
CollectionAssert.AreEqual(new object[]
{
2, "Net Loss", new DateTime(2020, 8, 3), new TimeSpan(14, 41, 10),
444.44m, new DateTime(2020, 8, 3), new TimeSpan(16, 29, 08), 222.22m
}, dataTable.Rows[1].ItemArray);
}
public void TestReader_WithSchema_SchemaNotCounted_WithFilter_LineCount()
{
var mapper = new SeparatedValueTypeMapper <Person>(() => new Person());
mapper.Property(x => x.Name);
var people = new[]
{
new Person()
{
Name = "Bob"
},
new Person()
{
Name = "Tom"
},
new Person()
{
Name = "Jane"
}
};
StringWriter writer = new StringWriter();
mapper.Write(writer, people, new SeparatedValueOptions()
{
IsFirstRecordSchema = true
});
string output = writer.ToString();
mapper.CustomProperty(x => x.RecordNumber, new RecordNumberColumn("RecordNumber")
{
IncludeSchema = false,
IncludeFilteredRecords = true
});
StringReader reader = new StringReader(output);
var options = new SeparatedValueOptions()
{
IsFirstRecordSchema = true,
PartitionedRecordFilter = (values) => values.Length == 1 && values[0] == "Tom"
};
var results = mapper.Read(reader, options).ToArray();
Assert.Equal(2, results.Length);
Assert.Equal("Bob", results[0].Name);
Assert.Equal(1, results[0].RecordNumber);
Assert.Equal("Jane", results[1].Name);
Assert.Equal(3, results[1].RecordNumber);
}
public void TestGetSchema_Extracted_ReturnsColumnNames()
{
string text = "a,b,c";
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
IReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), options);
ISchema schema = parser.GetSchema();
Assert.IsTrue(schema.ColumnDefinitions.All(d => d is StringColumn), "Not all of the columns were treated as strings.");
string[] actual = schema.ColumnDefinitions.Select(d => d.ColumnName).ToArray();
string[] expected = new string[] { "a", "b", "c" };
CollectionAssert.AreEqual(expected, actual, "The schema was not extracted as expected.");
}
public void TestGetSchema_NotExtracted_Throws()
{
string text = "a,b,c";
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = false };
IReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), options);
parser.GetSchema();
}
public void TestGetSchema_SchemaProvided_FirstRecordSchema_SkipsFirstRecord()
{
const string text = @"id,name,created";
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created"));
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
IReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), schema, options);
ISchema actual = parser.GetSchema();
Assert.AreSame(schema, actual, "The schema was passed did not take priority.");
Assert.IsFalse(parser.Read(), "The schema record was not skipped.");
}
public void TestGetValues_BlankTrailingSection_ReturnsNull()
{
using (MemoryStream stream = new MemoryStream())
{
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created") { InputFormat = "M/d/yyyy", OutputFormat = "M/d/yyyy" })
.AddColumn(new StringColumn("trailing"));
object[] sources = new object[] { 123, "Bob", new DateTime(2013, 1, 19), "" };
using (SeparatedValueWriter builder = new SeparatedValueWriter(stream, schema, options))
{
builder.Write(sources);
}
stream.Position = 0;
SeparatedValueReader parser = new SeparatedValueReader(stream, schema, options);
Assert.IsTrue(parser.Read(), "No records were found.");
object[] values = parser.GetValues();
Assert.AreEqual(schema.ColumnDefinitions.Count, values.Length, "The wrong number of values were read.");
Assert.AreEqual(sources[0], values[0], "The first column was not parsed correctly.");
Assert.AreEqual(sources[1], values[1], "The second column was not parsed correctly.");
Assert.AreEqual(sources[2], values[2], "The third column was not parsed correctly.");
Assert.AreEqual(null, values[3], "The forth column was not interpreted as null.");
Assert.IsFalse(parser.Read(), "Too many records were found.");
}
}
public void TestRead_RecordWithCP1252Characters_ReturnsCorrectCharacters()
{
//---- Arrange -----------------------------------------------------
// Need to convert the string to target encoding because otherwise a string declared in VS will always be encoded as UTF-8
var text = Encoding.Convert(Encoding.UTF8, Encoding.GetEncoding(1252), Encoding.UTF8.GetBytes(@"123;Müller;1/17/2014"));
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id")).AddColumn(new StringColumn("name")).AddColumn(new DateTimeColumn("created"));
var options = new SeparatedValueOptions
{
IsFirstRecordSchema = false,
Separator = ";" ,
Encoding = Encoding.GetEncoding(1252)
};
var testee = new SeparatedValueReader(new MemoryStream(text), schema, options);
//---- Act ---------------------------------------------------------
var result = testee.Read();
//---- Assert ------------------------------------------------------
Assert.IsTrue(result, "Could not read the record.");
object[] expected = { 123, "Müller", new DateTime(2014, 1, 17) };
object[] actual = testee.GetValues();
CollectionAssert.AreEqual(expected, actual, "The wrong values were parsed.");
}
public void TestTypeMapper_RoundtripWithNull()
{
var mapper = SeparatedValueTypeMapper.Define<Person>();
mapper.Property(p => p.Id).ColumnName("id");
mapper.Property(p => p.Name).ColumnName("name");
mapper.Property(p => p.Created).ColumnName("created").InputFormat("yyyyMMdd").OutputFormat("yyyyMMdd");
using (MemoryStream stream = new MemoryStream())
{
var bob = new Person() { Id = 123, Name = null, Created = new DateTime(2013, 1, 19) };
var options = new SeparatedValueOptions() { IsFirstRecordSchema = true, Separator = "\t" };
mapper.Write(stream, options, new Person[] { bob });
stream.Position = 0; // go back to the beginning of the stream
var people = mapper.Read(stream, options);
Assert.AreEqual(1, people.Count(), "The wrong number of people were returned.");
var person = people.SingleOrDefault();
Assert.AreEqual(bob.Id, person.Id, "The ID value was not persisted.");
Assert.AreEqual(bob.Name, person.Name, "The Name value was not persisted.");
Assert.AreEqual(bob.Created, person.Created, "The Created value was not persisted.");
}
}
public void TestGetSchema_NotExtracted_Throws()
{
string text = "a,b,c";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = false };
IReader parser = new SeparatedValueReader(stringReader, options);
Assert.Throws<InvalidOperationException>(() => parser.GetSchema());
}
public void TestGetSchema_SchemaProvided_FirstRecordSchema_SkipsFirstRecord()
{
const string text = @"id,name,created";
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created"));
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
IReader parser = new SeparatedValueReader(stringReader, schema, options);
ISchema actual = parser.GetSchema();
Assert.Same(schema, actual);
Assert.False(parser.Read(), "The schema record was not skipped.");
}
public void TestGetValues_BlankTrailingSection_ReturnsNull()
{
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
SeparatedValueSchema schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"))
.AddColumn(new StringColumn("name"))
.AddColumn(new DateTimeColumn("created") { InputFormat = "M/d/yyyy", OutputFormat = "M/d/yyyy" })
.AddColumn(new StringColumn("trailing"));
object[] sources = new object[] { 123, "Bob", new DateTime(2013, 1, 19), "" };
StringWriter stringWriter = new StringWriter();
SeparatedValueWriter builder = new SeparatedValueWriter(stringWriter, schema, options);
builder.Write(sources);
StringReader stringReader = new StringReader(stringWriter.ToString());
SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema, options);
Assert.True(parser.Read(), "No records were found.");
object[] values = parser.GetValues();
Assert.Equal(schema.ColumnDefinitions.Count, values.Length);
Assert.Equal(sources[0], values[0]);
Assert.Equal(sources[1], values[1]);
Assert.Equal(sources[2], values[2]);
Assert.Equal(null, values[3]);
Assert.False(parser.Read(), "Too many records were found.");
}
public void TestRead_EmbeddedQuote_ParsesCorrectly()
{
var text = @"123;Todd's Bait Shop;1/17/2014";
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("id"));
schema.AddColumn(new StringColumn("name"));
schema.AddColumn(new DateTimeColumn("created"));
var options = new SeparatedValueOptions
{
IsFirstRecordSchema = false,
Separator = ";"
};
StringReader stringReader = new StringReader(text);
var reader = new SeparatedValueReader(stringReader, schema, options);
var result = reader.Read();
Assert.True(result, "Could not read the record.");
object[] expected = { 123, "Todd's Bait Shop", new DateTime(2014, 1, 17) };
object[] actual = reader.GetValues();
Assert.Equal(expected, actual);
}
public void TestRead_ZeroLengthColumn()
{
//---- Arrange -----------------------------------------------------
var text = "104\t20\t1000\t00\tLausanne\tLausanne\tVD\t2\t\t0\t130\t5586\t19880301";
var options = new SeparatedValueOptions { IsFirstRecordSchema = false, Separator = "\t" };
var schema = new SeparatedValueSchema();
schema.AddColumn(new Int32Column("OnrpId"))
.AddColumn(new Int32Column("Type"))
.AddColumn(new Int32Column("ZipCode"))
.AddColumn(new StringColumn("ZipCodeAddOn"))
.AddColumn(new StringColumn("TownShortName"))
.AddColumn(new StringColumn("TownOfficialName"))
.AddColumn(new StringColumn("CantonAbbreviation"))
.AddColumn(new Int16Column("MainLanguageCode"))
.AddColumn(new Int16Column("OtherLanguageCode"))
.AddColumn(new ByteColumn("HasSortfileData"))
.AddColumn(new Int32Column("LetterServiceOnrpId"))
.AddColumn(new Int32Column("MunicipalityId"))
.AddColumn(new StringColumn("ValidFrom"));
StringReader stringReader = new StringReader(text);
var testee = new SeparatedValueReader(stringReader, options);
//---- Act ---------------------------------------------------------
var result = testee.Read();
//---- Assert ------------------------------------------------------
Assert.True(result);
Assert.Equal(schema.ColumnDefinitions.Count, testee.GetValues().Count());
}
public void TestTypeMapper_RoundtripWithNull()
{
var mapper = SeparatedValueTypeMapper.Define<Person>();
mapper.Property(p => p.Id).ColumnName("id");
mapper.Property(p => p.Name).ColumnName("name");
mapper.Property(p => p.Created).ColumnName("created").InputFormat("yyyyMMdd").OutputFormat("yyyyMMdd");
var bob = new Person() { Id = 123, Name = null, Created = new DateTime(2013, 1, 19) };
var options = new SeparatedValueOptions() { IsFirstRecordSchema = true, Separator = "\t" };
StringWriter stringWriter = new StringWriter();
mapper.Write(stringWriter, new Person[] { bob }, options);
StringReader stringReader = new StringReader(stringWriter.ToString());
var people = mapper.Read(stringReader, options).ToArray();
Assert.Equal(1, people.Count());
var person = people.SingleOrDefault();
Assert.Equal(bob.Id, person.Id);
Assert.Equal(bob.Name, person.Name);
Assert.Equal(bob.Created, person.Created);
}
public void TestGetSchema_FirstRecordSchema_TooFewColumns_Throws()
{
const string text = @"id,name,created
123,Bob";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
SeparatedValueReader parser = new SeparatedValueReader(stringReader, options);
Assert.Throws<FlatFileException>(() => parser.Read());
}
public void TestGetSchema_FirstRecordSchema_TooManyColumns_IgnoresTrailing()
{
const string text = @"id,name,created
123,Bob,1/19/2013,Hello";
StringReader stringReader = new StringReader(text);
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
SeparatedValueReader parser = new SeparatedValueReader(stringReader, options);
Assert.True(parser.Read(), "The record could not be read.");
Assert.Equal(parser.GetSchema().ColumnDefinitions.Count, parser.GetValues().Length);
;
}
public void TestGetSchema_FirstRecordSchema_WrongNumberOfColumns_Throws()
{
const string text = @"id,name,created
123,Bob,1/19/2013,Hello";
SeparatedValueOptions options = new SeparatedValueOptions() { IsFirstRecordSchema = true };
SeparatedValueReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), options);
parser.Read();
}
