ETL project, handling XML, CSV, and text files

Question

I have completed an ETL project to collect, parse and load files. I decided to make it clean OOP way using interfaces and abstract, but have some questions below.

Sub Main() Dim collectionOfParsers As New List(Of EtlParser) Dim xmlparser1 As New XmlParser Dim xmlparser2 As New XmlParser Dim xmlparser3 As New XmlParser Dim txtparser1 As New TxtParser Dim txtparser2 As New TxtParser collectionOfParsers.Add(xmlparser1) collectionOfParsers.Add(xmlparser2) collectionOfParsers.Add(xmlparser3) collectionOfParsers.Add(txtparser1) collectionOfParsers.Add(txtparser2) For Each parser As EtlParser In collectionOfParsers parser.SaySomething() Dim canOpenFiles = TryCast(parser, ICanOpenFiles) If (canOpenFiles IsNot Nothing) Then canOpenFiles.OpenFiles() End If Dim canReadFiles = TryCast(parser, ICanReadFiles) If (canReadFiles IsNot Nothing) Then canReadFiles.Readfiles() End If Dim canTransFiles = TryCast(parser, ICanTransformFiles) If (canTransFiles IsNot Nothing) Then canTransFiles.TransformFile() End If Dim canSaveFiles = TryCast(parser, ICanSaveFiles) If (canSaveFiles IsNot Nothing) Then canSaveFiles.Savefiles() End If Next End Sub Public MustInherit Class Etl End Class Public MustInherit Class EtlParser : Inherits Etl Protected Sub CanParse() Console.WriteLine("Yes") End Sub Protected Overridable Sub SaySomething() Console.WriteLine("EtlParser say something") End Sub Protected MustOverride Sub CanParseFormat() End Class Public Interface ICanOpenFiles Sub OpenFiles() End Interface Public Interface ICanReadFiles Sub Readfiles() End Interface Public Interface ICanSaveFiles Sub Savefiles() End Interface Public Interface ICanTransformFiles Sub TransformFile() End Interface Public Class XmlParser : Inherits EtlParser Implements ICanOpenFiles, ICanReadFiles, ICanTransformFiles, ICanSaveFiles Public Sub OpenFiles() Implements ICanOpenFiles.OpenFiles Throw New NotImplementedException() End Sub Public Sub Readfiles() Implements ICanReadFiles.Readfiles Throw New NotImplementedException() End Sub Public Sub TransformFile() Implements ICanTransformFiles.TransformFile Throw New NotImplementedException() End Sub Public Sub Savefiles() Implements ICanSaveFiles.Savefiles Throw New NotImplementedException() End Sub Protected Overrides Sub CanParseFormat() Throw New NotImplementedException() End Sub Protected Overrides Sub SaySomething() 'MyBase.SaySomething() Console.WriteLine("XmlParser say something") End Sub End Class Public Class CsvParser : Inherits EtlParser Implements ICanOpenFiles, ICanReadFiles, ICanTransformFiles, ICanSaveFiles Public Sub OpenFiles() Implements ICanOpenFiles.OpenFiles Throw New NotImplementedException() End Sub Public Sub Readfiles() Implements ICanReadFiles.Readfiles Throw New NotImplementedException() End Sub Public Sub TransformFile() Implements ICanTransformFiles.TransformFile Throw New NotImplementedException() End Sub Public Sub Savefiles() Implements ICanSaveFiles.Savefiles Throw New NotImplementedException() End Sub Protected Overrides Sub CanParseFormat() Throw New NotImplementedException() End Sub Protected Overrides Sub SaySomething() 'MyBase.SaySomething() Console.WriteLine("CsvParser say something") End Sub End Class 

Q1: Once i collect the files from network drive (this will be done by Collector later on). What is your opinion should i make xmlparser class to handle many files or just one? If the second option then as you can see i created already many xmlparser instances (1 instance per each file), however i am not sure here maybe should i have xmlparser prepared for all files and then call it just once?

Q2: Regarding the for each loop i parametrized common type as EtlParser to pass diffrent specific parsers (is it ok by the way?). Can you explain me how it's possible specific parser within the loop is seen as passed object type - for instance i passed XmlParser and within i see it as well - i thought that when passing specific parser e.g XmlParser through parameter (his parent - EtlParser) it becomes EtlParser and i have to cast it again to XmlParser again inside loop. Would like to understand that.

Q3: As long as i know definition of interfaces e.g "Need to provide common functionality to unrelated classes" what in my example code is real benefit as all of my specific parsers uses the same interfaces at the end? All can open, read, transform and save...

Q4: As you see i have 3 specific parser classes: CsvParser, XmlParser, TxtParser inheriting from their base EtlParser class. Wouldn't it be better to make one parser class and instead make interface IXml, ITxt, ICsv which will be implemented? At this moment i think what i have is proper.

Q5: Why in the Main method i cannot do: parser.SaySomething() However when i look at parser item it shows exactly correct type.

Q6: Any ideas, advices to my current code besides?

Answer 1

Q1: It takes nanoseconds to create an object and milliseconds to access a file; i.e. roughly one million times longer! Don't try to optimize things that will have absolutely no noticeable effect at the expense of clarity!

Q2: Since XmlParser has no methods specific to XmlParser (i.e. existing only in XmlParser), there is no advantage in casting the object to it. But since the base class EtlParser does not implement the interfaces, you must cast the object to these interfaces (what you are doing).

Q3, Q4, Q6: This is one possible approach. I will suggest you another one.

Q5: SaySomething() is Protected, which means that it is only visible within the class defining it and its descendants. Make it Public.

---

Critics: Your interface makes operations like opening files public. The caller then must know whether this operations is available and call it. But this is a technical implementation detail which should be kept private. A public interface should concentrate on the desired high level logic. I.E. read data, transform data and maybe write data.

Suggestion: I would choose a more flexible approach allowing you to compose parsers from single components (like Lego bricks). Define this set of interfaces:

Public Interface IDataSource(Of T) Function Read() As IEnumerable(Of T) End Interface Public Interface ITransformer(Of TSource, TResult) Function Transform(ByVal source As IEnumerable(Of TSource)) As IEnumerable(Of TResult) End Interface Public Interface IDataSink(Of T) Sub Write(ByVal data As IEnumerable(Of T)) End Interface 

The idea is to implement these interfaces by different classes. You would have one class for an XML-data-source, one for a file-data-source, one for a transformation, etc.

A data source can be a text-file an XML-file a database or be a dummy data source for test purposes. It is the data source’s responsibility to open, read and close files etc. You don't need separate interfaces for all these operations.

Note that file names and connection strings can be passed as constructor parameters and don't need to be specified in the interfaces.

Define classes serving as transport vehicle for single data records like RawData, PreProcessedData, RefinedData used as generic type arguments for the interfaces. You will probably choose names for these classes that are better suited for your specific problem.

You can even chain several transformations like this:

read >>(RawData)>> transform 1 >>(PreProcessedData)>> transform 2 >>(RefinedData)>> write 

One advantage of this approach is that you can apply the same transformations to different types of data sources (having the same TSource) and store the result into different types of destinations (having the same TResult).

Note: Iterators (Visual Basic) will help you to implement these interfaces.

---

Let's make a very simple example. We have a CSV-File with a name column and two number columns. We want to transform this file into another one containing the name column and one number column containing the sum of the two numbers.

Input file:

Joe,3,4
Mike,6,2
Sue,10,3

Expected output file:

Joe,7
Mike,8
Sue,13

We need two data classes

Public Class InputData Public Property Name As String Public Property X As Integer Public Property Y As Integer End Class Public Class OutputData Public Property Name As String Public Property Sum As Integer End Class 

A reader

Public Class ExampleCsvReader Implements IDataSource(Of InputData) Private m_filename As String Public Sub New(ByVal filename As String) m_filename = filename End Sub Public Iterator Function Read() As IEnumerable(Of InputData) _ Implements IDataSource(Of InputData).Read For Each line As String In File.ReadLines(m_filename) Dim parts = line.Split(","c) If parts.Length = 3 Then Yield New InputData With {.Name = parts(0), _ .X = CInt(parts(1)), .Y = CInt(parts(2))} End If Next End Function End Class 

A transformer

Public Class ExampleTransformer Implements ITransformer(Of InputData, OutputData) Public Iterator Function Transform(source As IEnumerable(Of InputData)) _ As IEnumerable(Of OutputData) _ Implements ITransformer(Of InputData, OutputData).Transform For Each record As InputData In source Yield New OutputData With {.Name = record.Name, .Sum = record.X + record.Y} Next End Function End Class 

A writer

Public Class ExampleCsvWriter Implements IDataSink(Of OutputData) Private m_filename As String Public Sub New(ByVal filename As String) m_filename = filename End Sub Public Sub Write(data As IEnumerable(Of OutputData)) _ Implements IDataSink(Of OutputData).Write Using sw As StreamWriter = File.CreateText(m_filename) For Each record As OutputData In data sw.WriteLine($"{record.Name},{record.Sum}") Next End Using End Sub End Class 

And finally we can stitch the parts together

Dim reader = New ExampleCsvReader(inputFile) Dim transformer = New ExampleTransformer() Dim writer = New ExampleCsvWriter(outputFile) Dim inputData = reader.Read() Dim outputData = transformer.Transform(inputData) writer.Write(outputData) 

Generic solution: This approach also lets you also realize a more generic solution. You are free to create generic readers that for instance return data in a dictionary. The data type could be a Dictionary(Of String, Object) for instance, storing property name/value pairs. A reader could implement a IDataSource(Of Dictionary(Of String, Object)), for instance.

VB specific: The Yield statement is like a Return statement that returns a value, but unlike the latter, it does not exit the function and continues its execution to return the next value of the enumeration, and so on, until the end of the function is reached.

Besides iterators I also used Object Initializers, String Interpolation (Point 12.), Using Statement.