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); }
private static SeparatedValueSchema getSchema() { var nullHandler = ConstantNullHandler.For("----"); SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("Name") { NullHandler = nullHandler }); schema.AddColumn(new DecimalColumn("Cost") { NullHandler = nullHandler, FormatProvider = CultureInfo.InvariantCulture }); schema.AddColumn(new SingleColumn("Available") { NullHandler = nullHandler }); schema.AddColumn(new StringColumn("Vendor") { NullHandler = nullHandler }); return(schema); }
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 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 TestAddColumn_DuplicateColumnName_Throws() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("Name")); Assert.ThrowsException <ArgumentException>(() => schema.AddColumn(new Int32Column("name"))); }
public void TestAddColumn_DuplicateColumnName_Throws() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("Name")); schema.AddColumn(new Int32Column("name")); }
private static SeparatedValueSchema getSchema() { var nullHandler = ConstantNullHandler.For("----"); SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("Name") { NullHandler = nullHandler }); schema.AddColumn(new DecimalColumn("Cost") { NullHandler = nullHandler, FormatProvider = CultureInfo.InvariantCulture }); schema.AddColumn(new SingleColumn("Available") { NullHandler = nullHandler }); schema.AddColumn(new StringColumn("Vendor") { NullHandler = nullHandler }); return schema; }
public void TestReadFlatFile_SchemaProvided_TypesUsed() { const string text = @"123,Bob,12/31/2012,3.14159"; DataTable table = new DataTable(); SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")) .AddColumn(new DoubleColumn("avg")); StringReader stringReader = new StringReader(text); IReader parser = new SeparatedValueReader(stringReader, schema); 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); DataRow row = table.Rows[0]; object[] expected = new object[] { 123, "Bob", new DateTime(2012, 12, 31), 3.14159 }; object[] values = row.ItemArray; CollectionAssert.AreEqual(expected, 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_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 SeparatedFileSchema BuildSchema(RawFileSchema rawSchema) { var separatedFileSchema = new SeparatedFileSchema(); var rowDefinitionList = rawSchema.Schema.RowDefinitions; separatedFileSchema.Delimeter = rawSchema.Delimeter; foreach (var rowDefinition in rowDefinitionList) { var columnDefinitions = rowDefinition.ColumnDefinitions; var rowSchema = new SeparatedValueSchema(); foreach (var columnDefinition in columnDefinitions) { var definition = GetColumnDefinition(columnDefinition); definition.NullHandler = ConstantNullHandler.For("NULL"); rowSchema.AddColumn(definition); } separatedFileSchema.SeparatedRecordSchemas.Add(new SeparatedRecordSchema { RecordIdentifier = rowDefinition.Identifier, SeparatedValueSchema = rowSchema }); } return(separatedFileSchema); }
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 void TestRead_WithSeparatedRecordFilter_SkipsRecordsMatchingCriteria() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")); 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); var parser = new SeparatedValueReader(stringReader, schema); parser.RecordRead += (sender, e) => { e.IsSkipped = e.Values.Length < 3; }; Assert.IsTrue(parser.Read(), "Could not read the first record."); object[] actual1 = parser.GetValues(); CollectionAssert.AreEqual(new object[] { 123, "Bob Smith", new DateTime(2017, 04, 21) }, actual1); Assert.IsTrue(parser.Read(), "Could not read the second record."); object[] actual2 = parser.GetValues(); CollectionAssert.AreEqual(new object[] { 234, "Jay Smith", new DateTime(2017, 05, 21) }, actual2); Assert.IsFalse(parser.Read(), "There should not be any more records."); }
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 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 TestParseValues_ParsesValues() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("first_name")) .AddColumn(new StringColumn("last_name")) .AddColumn(new DateTimeColumn("birth_date") { InputFormat = "yyyyMMdd" }) .AddColumn(new Int32Column("weight")); string[] values = new string[] { "bob", "smith", "20120123", "185" }; var recordContext = new RecordContext() { ExecutionContext = new SeparatedValueExecutionContext() { Schema = schema, Options = new SeparatedValueOptions() } }; object[] parsed = schema.ParseValues(recordContext, values); object[] expected = new object[] { "bob", "smith", new DateTime(2012, 1, 23), 185 }; CollectionAssert.AreEqual(expected, parsed); }
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 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 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 ShouldWriteSchemaIfExplicit() { StringWriter stringWriter = new StringWriter(); // Explicitly indicate that the first record is NOT the schema SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("Col1")); SeparatedValueWriter writer = new SeparatedValueWriter(stringWriter, schema, new SeparatedValueOptions() { IsFirstRecordSchema = false }); writer.WriteSchema(); // Explicitly write the schema writer.Write(new string[] { "a" }); StringReader stringReader = new StringReader(stringWriter.ToString()); var reader = new SeparatedValueReader(stringReader, new SeparatedValueOptions() { IsFirstRecordSchema = true }); var parsedSchema = reader.GetSchema(); Assert.Equal(schema.ColumnDefinitions.Count, parsedSchema.ColumnDefinitions.Count); Assert.Equal(schema.ColumnDefinitions[0].ColumnName, parsedSchema.ColumnDefinitions[0].ColumnName); Assert.True(reader.Read(), "The record was not retrieved after the schema."); Assert.False(reader.Read(), "Encountered more than the expected number of records."); }
public void TestRead_SkipRecord_NoParsingError() { const string text = "a,b,c"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("A")); schema.AddColumn(new DateTimeColumn("B")); schema.AddColumn(new GuidColumn("C")); StringReader stringReader = new StringReader(text); SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema); bool canRead = parser.Skip(); Assert.True(canRead, "Could not skip the record."); canRead = parser.Read(); Assert.False(canRead, "No more records should have been read."); }
public void ShouldRoundTrip() { const string message = @"Tom,Hanselman,2016-06-0426 Walking Ice,Ace "; StringReader stringReader = new StringReader(message); SeparatedValueSchema outerSchema = new SeparatedValueSchema(); outerSchema.AddColumn(new StringColumn("FirstName")); outerSchema.AddColumn(new StringColumn("LastName")); FixedLengthSchema innerSchema = new FixedLengthSchema(); innerSchema.AddColumn(new DateTimeColumn("StartDate") { InputFormat = "yyyy-MM-dd", OutputFormat = "yyyy-MM-dd" }, 10); innerSchema.AddColumn(new Int32Column("Age"), 2); innerSchema.AddColumn(new StringColumn("StageName"), new Window(20) { Alignment = FixedAlignment.RightAligned }); outerSchema.AddColumn(new FixedLengthComplexColumn("PlayerStats", innerSchema)); outerSchema.AddColumn(new StringColumn("Nickname")); SeparatedValueReader reader = new SeparatedValueReader(stringReader, outerSchema); Assert.True(reader.Read(), "A record should have been read."); object[] values = reader.GetValues(); Assert.Equal("Tom", values[0]); Assert.Equal("Hanselman", values[1]); Assert.IsType <object[]>(values[2]); object[] playerValues = (object[])values[2]; Assert.Equal(new DateTime(2016, 06, 04), playerValues[0]); Assert.Equal(26, playerValues[1]); Assert.Equal("Walking Ice", playerValues[2]); Assert.Equal("Ace", values[3]); StringWriter stringWriter = new StringWriter(); SeparatedValueWriter writer = new SeparatedValueWriter(stringWriter, outerSchema); writer.Write(values); string output = stringWriter.GetStringBuilder().ToString(); Assert.Equal(message, output); }
public void TestColumnDefinitions_WithColumns_CountEqualsColumnCount() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")).AddColumn(new StringColumn("name")).AddColumn(new DateTimeColumn("created")); ColumnCollection collection = schema.ColumnDefinitions; Assert.AreEqual(3, collection.Count, "The column collection count was wrong."); }
public void TestWriteFlatFile_MatchingSchema() { 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 bobRow = table.Rows.Add(new object[] { 1, "Bob", new DateTime(2018, 07, 16), 12.34m }); DataRow johnRow = table.Rows.Add(new object[] { 2, "John", new DateTime(2018, 07, 17), 23.45m }); DataRow susanRow = table.Rows.Add(new object[] { 3, "Susan", new DateTime(2018, 07, 18), 34.56m }); DataRow nullRow = table.Rows.Add(new object[] { 4, null, null, null }); var options = new SeparatedValueOptions() { IsFirstRecordSchema = true, RecordSeparator = "\r\n", FormatProvider = CultureInfo.InvariantCulture }; var stringWriter = new StringWriter(); var schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")); schema.AddColumn(new StringColumn("name")); schema.AddColumn(new DateTimeColumn("created") { OutputFormat = "MM/dd/yyyy" }); schema.AddColumn(new DecimalColumn("avg")); var csvWriter = new SeparatedValueWriter(stringWriter, schema, options); table.WriteFlatFile(csvWriter); string output = stringWriter.ToString(); Assert.AreEqual(@"id,name,created,avg 1,Bob,07/16/2018,12.34 2,John,07/17/2018,23.45 3,Susan,07/18/2018,34.56 4,,, ", output); }
public void TestColumnDefinitions_GetEnumerable_Explicit() { SeparatedValueSchema schema = new SeparatedValueSchema(); ColumnDefinition id = new Int32Column("id"); ColumnDefinition name = new StringColumn("name"); ColumnDefinition created = new DateTimeColumn("created"); schema.AddColumn(id).AddColumn(name).AddColumn(created); IEnumerable collection = schema.ColumnDefinitions; IEnumerator enumerator = collection.GetEnumerator(); }
public void TestFlatFileReader_IgnoreIgnoredColumns() { 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); using (var dataReader = new FlatFileDataReader(csvReader)) { Assert.AreEqual("A", dataReader.GetName(0)); Assert.AreEqual("C", dataReader.GetName(1)); Assert.AreEqual(0, dataReader.GetOrdinal("A")); Assert.AreEqual(-1, dataReader.GetOrdinal("B")); Assert.AreEqual(1, dataReader.GetOrdinal("C")); var schemaTable = dataReader.GetSchemaTable(); string[] columnNames = schemaTable.Rows.OfType <DataRow>() .Select(r => r.Field <string>("ColumnName")) .ToArray(); CollectionAssert.AreEqual(new[] { "A", "C" }, columnNames); Assert.IsTrue(dataReader.Read()); object[] values1 = dataReader.GetValues(); CollectionAssert.AreEqual(new[] { "1", "3" }, values1); Assert.IsTrue(dataReader.Read()); object[] values2 = dataReader.GetValues(); CollectionAssert.AreEqual(new[] { "4", "6" }, values2); Assert.IsFalse(dataReader.Read()); } }
public void TestWriteFlatFile_IgnoredColumns() { DataTable table = new DataTable(); DataColumn idColumn = table.Columns.Add("id", typeof(int)); table.Columns.Add("name", typeof(string)); table.Columns.Add("created", typeof(DateTime)); table.Columns.Add("avg", typeof(decimal)); table.Constraints.Add("PK_blah", idColumn, true); table.Rows.Add(new object[] { 1, "Bob", new DateTime(2018, 07, 16), 12.34m }); table.Rows.Add(new object[] { 2, "John", new DateTime(2018, 07, 17), 23.45m }); table.Rows.Add(new object[] { 3, "Susan", new DateTime(2018, 07, 18), 34.56m }); var options = new SeparatedValueOptions() { IsFirstRecordSchema = true, RecordSeparator = "\r\n", FormatProvider = CultureInfo.InvariantCulture }; var stringWriter = new StringWriter(); var schema = new SeparatedValueSchema(); schema.AddColumn(new IgnoredColumn("i0")); schema.AddColumn(new Int32Column("id")); schema.AddColumn(new IgnoredColumn("i1")); schema.AddColumn(new StringColumn("name")); schema.AddColumn(new IgnoredColumn("i2")); schema.AddColumn(new DateTimeColumn("created") { OutputFormat = "MM/dd/yyyy" }); schema.AddColumn(new IgnoredColumn("i3")); schema.AddColumn(new DecimalColumn("avg")); schema.AddColumn(new IgnoredColumn("i4")); var csvWriter = new SeparatedValueWriter(stringWriter, schema, options); table.WriteFlatFile(csvWriter); string output = stringWriter.ToString(); Assert.AreEqual(@"i0,id,i1,name,i2,created,i3,avg,i4 ,1,,Bob,,07/16/2018,,12.34, ,2,,John,,07/17/2018,,23.45, ,3,,Susan,,07/18/2018,,34.56, ", output); }
public void TestReadFlatFile_ColumnMissing_InsertsNulls() { // 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(string)); table.Constraints.Add("PK_blah", idColumn, true); DataRow row = table.Rows.Add(new object[] { 1, "Bob", new DateTime(2018, 07, 16), "12.34" }); row.AcceptChanges(); const string text = @"id,name,created 2,John,07/17/2018 3,Susan,07/18/2018"; 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")); IReader csvReader = new SeparatedValueReader(stringReader, schema, options); table.ReadFlatFile(csvReader); 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(3, table.Rows.Count); CollectionAssert.AreEqual(new object[] { 1, "Bob", new DateTime(2018, 07, 16), "12.34" }, table.Rows[0].ItemArray); CollectionAssert.AreEqual(new object[] { 2, "John", new DateTime(2018, 07, 17), DBNull.Value }, table.Rows[1].ItemArray); CollectionAssert.AreEqual(new object[] { 3, "Susan", new DateTime(2018, 07, 18), DBNull.Value }, table.Rows[2].ItemArray); }
public void TestRead_InvalidConversion_Throws() { const string text = "a"; StringReader stringReader = new StringReader(text); SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("First")); SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema); Assert.Throws <RecordProcessingException>(() => parser.Read()); }
public void TestReader_BadDecimal_ThrowsException() { const string data = "bad"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new DecimalColumn("value")); SeparatedValueReader reader = new SeparatedValueReader(new StringReader(data), schema); Assert.ThrowsException <RecordProcessingException>(() => reader.Read()); }
public void TestColumnDefinitions_GetEnumerable_Explicit() { SeparatedValueSchema schema = new SeparatedValueSchema(); ColumnDefinition id = new Int32Column("id"); ColumnDefinition name = new StringColumn("name"); ColumnDefinition created = new DateTimeColumn("created"); schema.AddColumn(id).AddColumn(name).AddColumn(created); IEnumerable collection = schema.ColumnDefinitions; IEnumerator enumerator = collection.GetEnumerator(); }
public void TestColumnDefinitions_FindByIndex() { SeparatedValueSchema schema = new SeparatedValueSchema(); ColumnDefinition id = new Int32Column("id"); ColumnDefinition name = new StringColumn("name"); ColumnDefinition created = new DateTimeColumn("created"); schema.AddColumn(id).AddColumn(name).AddColumn(created); ColumnCollection collection = schema.ColumnDefinitions; Assert.AreSame(id, collection[0], "The first column definition was wrong."); Assert.AreSame(name, collection[1], "The second column definition was wrong."); Assert.AreSame(created, collection[2], "The third column definition was wrong."); }
public void TestGetSchema_SchemaProvided_WrongNumberOfColumns_Throws() { const string text = @"123,Bob"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")); SeparatedValueReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), schema); parser.Read(); }
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 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 TestGetSchema_SchemaProvided_ParsesValues() { const string text = @"123,Bob,1/19/2013"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")); SeparatedValueReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), schema); Assert.IsTrue(parser.Read(), "The first record was skipped."); object[] actual = parser.GetValues(); object[] expected = new object[] { 123, "Bob", new DateTime(2013, 1, 19) }; CollectionAssert.AreEqual(expected, actual, "The values were not parsed as expected."); }
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 TestReadFlatFile_SchemaProvided_TypesUsed() { const string text = @"123,Bob,12/31/2012,3.14159"; DataTable table = new DataTable(); SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")) .AddColumn(new DoubleColumn("avg")); StringReader stringReader = new StringReader(text); IReader parser = new SeparatedValueReader(stringReader, schema); 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", new DateTime(2012, 12, 31), 3.14159 }; object[] values = row.ItemArray; Assert.Equal(expected, values); }
public void TestGetSchema_SchemaProvided_WrongNumberOfColumns_Throws() { const string text = @"123,Bob"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")); StringReader stringReader = new StringReader(text); SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema); Assert.Throws<FlatFileException>(() => parser.Read()); }
public void TestGetSchema_SchemaProvided_ParsesValues_Quoted() { const string text = "123,\"Bob\",1/19/2013"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")); StringReader stringReader = new StringReader(text); SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema); Assert.True(parser.Read(), "The first record was skipped."); object[] actual = parser.GetValues(); object[] expected = new object[] { 123, "Bob", new DateTime(2013, 1, 19) }; Assert.Equal(expected, actual); }
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 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_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 TestRead_SkipRecord_NoParsingError() { const string text = "a,b,c"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("A")); schema.AddColumn(new DateTimeColumn("B")); schema.AddColumn(new GuidColumn("C")); StringReader stringReader = new StringReader(text); SeparatedValueReader parser = new SeparatedValueReader(stringReader, schema); bool canRead = parser.Skip(); Assert.True(canRead, "Could not skip the record."); canRead = parser.Read(); Assert.False(canRead, "No more records should have been read."); }
public void TestAddColumn_DuplicateColumnName_Throws() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("Name")); schema.AddColumn(new Int32Column("name")); }
public void TestAddColumn_NullDefinition_Throws() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(null); }
public void TestColumnDefinitions_WithColumns_CountEqualsColumnCount() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")).AddColumn(new StringColumn("name")).AddColumn(new DateTimeColumn("created")); ColumnCollection collection = schema.ColumnDefinitions; Assert.AreEqual(3, collection.Count, "The column collection count was wrong."); }
public void TestParseValues_ParsesValues() { SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new StringColumn("first_name")) .AddColumn(new StringColumn("last_name")) .AddColumn(new DateTimeColumn("birth_date") { InputFormat = "yyyyMMdd" }) .AddColumn(new Int32Column("weight")); string[] values = new string[] { "bob", "smith", "20120123", "185" }; object[] parsed = schema.ParseValues(values); object[] expected = new object[] { "bob", "smith", new DateTime(2012, 1, 23), 185 }; CollectionAssert.AreEqual(expected, parsed, "The values were not parsed as expected."); }
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 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 TestGetSchema_SchemaProvided_WrongNumberOfColumns_Throws() { const string text = @"123,Bob"; SeparatedValueSchema schema = new SeparatedValueSchema(); schema.AddColumn(new Int32Column("id")) .AddColumn(new StringColumn("name")) .AddColumn(new DateTimeColumn("created")); SeparatedValueReader parser = new SeparatedValueReader(new MemoryStream(Encoding.Default.GetBytes(text)), schema); parser.Read(); }