Custom functionality similar to SqlDataReader.Read()

Question

I would like to create a functionality that works similar to SqlDataReader.Read()

I'm reading a flat-file from .txt/.csv and returning it as a datatable to my class handling business logic. This iterates through the rows of the datatable, and transforms the data, writing into a structured database. I use this structure for multiple import sources.

Large files though, work really, really slowly. It is taking me 2h to go through 30 MB of data, and I would like to get this down to 30 min. One step in this direction is to not read the entire file into a DataTable, but handle it line by line, and keep memory from getting klogged.

Something like this would be ideal: PSEUDOCODE.

FlatFileReader ffr = new FlatFileReader(); //Set FlatFileParameters
while(ffr.ReadRow(out DataTable parsedFlatFileRow))
{
     //...Business Logic for handling the parsedFlatFileRow
}

How can I implement a method that works like .ReadRow(out DataTable parsedFlatFileRow) ?

Is this the right direction?

foreach(obj in ff.lazyreading()){
    //Business Logic
} 

...

class FlatFileWrapper{

    public IEnumerable<obj> lazyreading(){
        while(FileReader.ReadLine()){ 
            yield return parsedFileLine; 
        }
    } 
}

FileHelpers may be a good option for you: filehelpers.sourceforge.net — Rui Jarimba
– Rui Jarimba, Commented Oct 30, 2013 at 9:44
You probably have not determined the cause for the bad performance yet. You gave guessed that it is memory usage, but I'm very suspicious of that. Profile the app, or pause the debugger 10 times to see where it stops most often. — usr
– usr, Commented Oct 30, 2013 at 9:51
No you're right - I didn't profile it yet. But memory usage is a known problem, on this SQL Server, and particularly has been noticed in connection with working through large files. So keeping memory use low is a priority in itself. — Rafael Emshoff
– Rafael Emshoff, Commented Oct 30, 2013 at 9:55

Rui Jarimba · Accepted Answer · 2013-10-30 13:35:00Z

1

As Tim already mentioned, File.ReadLines is what you need:

"When you use ReadLines, you can start enumerating the collection of strings before the whole collection is returned"

You can create a parser that uses that method, something like this:

// object you want to create from the file lines.
public class Foo
{
    // add properties here....
}

// Parser only responsibility is create the objects.
public class FooParser
{
    public IEnumerable<Foo> ParseFile(string filename)
    {
        if(!File.Exists(filename))
            throw new FileNotFoundException("Could not find file to parse", filename);

        foreach(string line in File.ReadLines(filename))
        {
            Foo foo = CreateFoo(line);

            yield return foo;
        }
    }

    private Foo CreateFoo(string line)
    {
        // parse line/create instance of Foo here

        return new Foo {
            // ......
        };
    }
}

Using the code:

var parser = new FooParser();

foreach (Foo foo in parser.ParseFile(filename))
{
     //...Business Logic for handling the parsedFlatFileRow
}

edited Oct 30, 2013 at 13:35

answered Oct 30, 2013 at 10:37

Rui Jarimba

18.5k11 gold badges64 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rafael Emshoff Over a year ago

thx, I think I understood the concept, will try it out and post results. And this allows me to do lazy loading of the lines in the file, with memory only having the data of the current line?

Rui Jarimba Over a year ago

Yes, check the link in my answer: The ReadLines and ReadAllLines methods differ as follows: When you use ReadLines, you can start enumerating the collection of strings before the whole collection is returned; when you use ReadAllLines, you must wait for the whole array of strings be returned before you can access the array. Therefore, when you are working with very large files, ReadLines can be more efficient

Tim Schmelter · Accepted Answer · 2013-10-30 09:43:45Z

0

You can use File.ReadLines which works similar to a StreamReader:

foreach(string line in File.ReadLines(path))
{
     //...Business Logic for handling the parsedFlatFileRow
}

answered Oct 30, 2013 at 9:43

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

7 Comments

Rafael Emshoff Over a year ago

I'd like to take the file-reading code into its own class. So I'm looking for a wrapper to the code you posted, which returns a Row from the File, and the next time the method is called, returns the next Row, until the entire file has been read... without loading the entire File into memory.

Rui Jarimba Over a year ago

@RafaelCichocki: File.ReadLines is what you need. From MSDN: When you use ReadLines, you can start enumerating the collection of strings before the whole collection is returned. See msdn.microsoft.com/en-us/library/dd383503.aspx

Tim Schmelter Over a year ago

@RafaelCichocki: You could wrap the importer in a class that wraps a StreamReader (remember to implement IDisposable to dispose it) and yield return to return your objects lazily. stackoverflow.com/a/286553/284240

Kris Vandermotten Over a year ago

@RafaelCichocki This is indeed the method you need. But you probably want to look a C# iterators, i.e. using the yield return keyword.

Rui Jarimba Over a year ago

@TimSchmelter: maybe you can edit your answer to include a full method instead of just a foreach loop, using the yield return keyword? I think it would be much easier for the OP to understand

|

Collectives™ on Stack Overflow

Custom functionality similar to SqlDataReader.Read()

2 Answers 2

2 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related