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")
);
}