ETLBox ETLBox Try now!

Excel source

This article contains example code that shows the usage of the ExcelSource component.

The Excel source comes in handy when loading data from excel files.

Read excel file with same column names

Consider the following data in the Excel file Source.xlsx:

ColumnAColumnBColumnBColumnC
1AXTest
2BYTest
3CZTest

Please note that ColumnB exists twice as header name. So we need to configure the ExcelColumn in the ExcelSource to avoid getting an exception when reading the data.

With POCO

public class MyRow {
    [ExcelColumn(ColumnName = "ColumnA")]
    public int FirstColumn { get; set; }
    [ExcelColumn(ColumnIndex = 1)]
    public string SecondColumn { get; set; }
    [ExcelColumn(ColumnIndex = 2)]
    public string ThirdColumn { get; set; }
    public string ColumnC { get; set; }
}

string sourceFile = @"res/Examples/Source.xlsx";

ExcelSource<MyRow> source = new ExcelSource<MyRow>();
source.Uri = sourceFile;

MemoryDestination<MyRow> dest = new MemoryDestination<MyRow>();

source.LinkTo(dest);
Network.Execute(source);

foreach (MyRow col in dest.Data) {
    string dump = string.Join(",", col.GetType().GetProperties().Select(prop => prop.Name + ":" + prop.GetValue(col)).ToList());
    Console.WriteLine(dump);
}

/* Output:
FirstColumn:1,SecondColumn:A,ThirdColumn:X,ColumnC:Test
FirstColumn:2,SecondColumn:B,ThirdColumn:Y,ColumnC:Test
FirstColumn:3,SecondColumn:C,ThirdColumn:Z,ColumnC:Test
*/

With dynamic object

string sourceFile = @"res/Examples/Source.xlsx";

ExcelSource source = new ExcelSource();
source.Uri = sourceFile;
source.ExcelColumns = new ExcelColumn[] {
    new ExcelColumn() {ColumnName = "ColumnA", PropertyName = "FirstColumn" },
    new ExcelColumn() { ColumnIndex = 1, PropertyName = "SecondColumn" },
    new ExcelColumn() { ColumnIndex = 2, PropertyName = "ThirdColumn" },
};
MemoryDestination dest = new();

source.LinkTo(dest);

Network.Execute(source);

foreach (IDictionary<string, object> col in dest.Data) {
    string dump = string.Join(",", col.Select(col => col.Key + ":" + col.Value).ToList());
    Console.WriteLine(dump);
}

/* Output:
FirstColumn:1,SecondColumn:A,ThirdColumn:X,ColumnC:Test
FirstColumn:2,SecondColumn:B,ThirdColumn:Y,ColumnC:Test
FirstColumn:3,SecondColumn:C,ThirdColumn:Z,ColumnC:Test
*/
Edit this page on GitHub
Prev
Database source
Next
Json source