public void SimpleDataNoHeader() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationDynamic"); //Act ExcelSource source = new ExcelSource("res/Excel/TwoColumnShiftedData.xlsx") { Range = new ExcelRange(3, 4), HasNoHeader = true }; RowTransformation trans = new RowTransformation(row => { dynamic r = row as dynamic; r.Col1 = r.Column1; r.Col2 = r.Column2; return(r); }); DbDestination dest = new DbDestination(Connection, "ExcelDestinationDynamic"); source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); //Assert dest2Columns.AssertTestData(); }
public void SimpleDataNoHeader() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationStringArray"); //Act ExcelSource <string[]> source = new ExcelSource <string[]>("res/Excel/TwoColumnData.xlsx") { HasNoHeader = true }; RowTransformation <string[], MyData> trans = new RowTransformation <string[], MyData>(row => { MyData result = new MyData(); result.Col1 = int.Parse(row[0]); result.Col2 = row[1]; return(result); }); DbDestination <MyData> dest = new DbDestination <MyData>(Connection, "ExcelDestinationStringArray"); source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); //Assert dest2Columns.AssertTestData(); }
public void Excel_DB() { TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>() { new TableColumn("Col1", "int", allowNulls: false), new TableColumn("Col2", "nvarchar(100)", allowNulls: true), new TableColumn("Col3", "decimal(10,2)", allowNulls: true) }); stagingTable.CreateTable(); ExcelSource <ExcelData> source = new ExcelSource <ExcelData>("src/DataFlow/ExcelDataFile.xlsx") { Range = new ExcelRange(2, 4, 5, 9), SheetName = "Sheet2" }; DBDestination <ExcelData> dest = new DBDestination <ExcelData>() { DestinationTableDefinition = stagingTable }; source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(5, RowCountTask.Count("test.Staging")); }
public void DataOnSheet2WithRange() { //Arrange FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination"); //Act ExcelSource <ExcelDataSheet2> source = new ExcelSource <ExcelDataSheet2>("res/Excel/DataOnSheet2.xlsx") { Range = new ExcelRange(2, 4, 5, 9), SheetName = "Sheet2" }; DBDestination <ExcelDataSheet2> dest = new DBDestination <ExcelDataSheet2>(Connection, "ExcelDestination"); source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert Assert.Equal(5, RowCountTask.Count(Connection, "ExcelDestination")); Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert1' AND Col3 = 5 AND Col4 = 1")); Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 IS NULL AND Col3 = 0 AND Col4 = 1.2")); Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 IS NULL AND Col3 = 7 AND Col4 = 1.234")); Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert4' AND Col3 = 8 AND Col4 = 1.2345")); Assert.Equal(1, RowCountTask.Count(Connection, "ExcelDestination", "Col2 = 'Wert5' AND Col3 = 9 AND Col4 = 2")); }
public void WithoutErrorLinking() { //Arrange ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnErrorLinking.xlsx"); MemoryDestination <MySimpleRow> dest = new MemoryDestination <MySimpleRow>(); //Act & Assert Assert.Throws <System.FormatException>(() => { source.LinkTo(dest); source.Execute(); dest.Wait(); }); }
public void SimpleDataWithHeader() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationDynamicWithHeader"); ExcelSource source = new ExcelSource("res/Excel/TwoColumnWithHeader.xlsx"); DbDestination dest = new DbDestination(Connection, "ExcelDestinationDynamicWithHeader"); //Act source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert dest2Columns.AssertTestData(); }
public void SimpleData() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationWithNameAttribute"); ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnWithHeader.xlsx"); DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(Connection, "ExcelDestinationWithNameAttribute"); //Act source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert dest2Columns.AssertTestData(); }
private IList <MyDataRow> LoadExcelIntoMemory(string filename) { MemoryDestination <MyDataRow> dest = new MemoryDestination <MyDataRow>(); ExcelSource <MyDataRow> source = new ExcelSource <MyDataRow>(filename) { Range = new ExcelRange(1, 3) }; source.LinkTo(dest); source.Execute(); dest.Wait(); return(dest.Data.ToList()); }
public void OnlyOneExcelColumn() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestination"); //Act ExcelSource <OneExcelColumn> source = new ExcelSource <OneExcelColumn>("res/Excel/TwoColumnData.xlsx"); DBDestination <OneExcelColumn> dest = new DBDestination <OneExcelColumn>(Connection, "ExcelDestination", 2); source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert Assert.Equal(3, RowCountTask.Count(Connection, "ExcelDestination", "Col1 = 0 AND Col2 LIKE 'Test%'")); }
public void SimpleData() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestination"); //Act ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnData.xlsx"); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(Connection, "ExcelDestination", 2); source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert dest2Columns.AssertTestData(); }
public void IgnoreBlankRows() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationBlankRows"); //Act ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnBlankRow.xlsx") { IgnoreBlankRows = true }; DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("ExcelDestinationBlankRows", Connection, 2); source.LinkTo(dest); source.Execute(); dest.Wait(); //Assert dest2Columns.AssertTestData(); }
public void WithObjectErrorLinking() { //Arrange TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelSourceErrorLinking"); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(SqlConnection, "ExcelSourceErrorLinking"); MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>(); //Act ExcelSource <MySimpleRow> source = new ExcelSource <MySimpleRow>("res/Excel/TwoColumnErrorLinking.xlsx"); source.LinkTo(dest); source.LinkErrorTo(errorDest); source.Execute(); dest.Wait(); errorDest.Wait(); //Assert dest2Columns.AssertTestData(); Assert.Collection <ETLBoxError>(errorDest.Data, d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText)) ); }
public void Exceding20Columns() { //Arrange FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination"); //Act ExcelSource <Excel21Cols> source = new ExcelSource <Excel21Cols>("res/Excel/MoreThan20Cols.xlsx") { Range = new ExcelRange(1, 2), }; MemoryDestination <Excel21Cols> dest = new MemoryDestination <Excel21Cols>(); source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.Collection <Excel21Cols>(dest.Data, r => Assert.True(r.Col1 == 1 && r.Col2 == "Test1" && r.N == "N" && r.V == "V"), r => Assert.True(r.Col1 == 2 && r.Col2 == "Test2" && r.N == "N" && r.V == "V"), r => Assert.True(r.Col1 == 3 && r.Col2 == "Test3" && r.N == "N" && r.V == "V") ); }