Process a CSV file starting at a predetermined line/row using LumenWorks parser

Question

I am using LumenWorks awesome CSV reader to process CSV files. Some files have over 1 million records.

What I want is to process the file in sections. E.g. I want to process 100,000 records first, validate the data and then send this records over an Internet connection. Once sent, I then reopen the file and continue from record 100,001. On and on till I finish processing the file. In my application I have already created the logic of keeping track of which record I am currently processing.

Does the LumenWorks parser support processing from a predetermined line in the CSV or it always has to start from the top? I see it has a buffer variable. Is there a way to use this buffer variable to achieve my goal?

my_csv = New CsvReader(New StreamReader(file_path), False, ",", buffer_variable)

Answer 1

Try to use CachedCSVReader instead of CSVReader and MoveTo(long recordnumber), MoveToStart etc. methods.

Answer 2

It seems the LumenWorks CSV Reader needs to start at the top - I needed to ignore the first n lines in a file, and attempted to pass a StreamReader that was at the correct position/row, but got a Key already exists Dictionary error when I attempted to get the FieldCount (there were no duplicates).

However, I have found some success by first reading pre-trimmed file into StringBuilder and then into a StringReader to allow the CSV Reader to read it. Your mileage may vary with huge files, but it does help to trim a file:

                using (StreamReader sr = new StreamReader(filePath))
                {
                    string line = sr.ReadLine(); 
                    StringBuilder sbCsv = new StringBuilder();

                    int lineNumber = 0;

                    do
                    {
                        lineNumber++;

                        // Ignore the start rows of the CSV file until we reach the header
                        if (lineNumber >= Constants.HeaderStartingRow)
                        {
                            // Place into StringBuilder
                            sbCsv.AppendLine(line);
                        }
                    }
                    while ((line = sr.ReadLine()) != null);

                    // Use a StringReader to read the trimmed CSV file into a CSV Reader
                    using (StringReader str = new StringReader(sbCsv.ToString()))
                    {
                        using (CsvReader csv = new CsvReader(str, true))
                        {
                            int fieldCount = csv.FieldCount;
                            string[] headers = csv.GetFieldHeaders();
                            while (csv.ReadNextRecord())
                            {
                                for (int i = 0; i < fieldCount; i++)
                                {
                                    // Do Work                                
                                }
                            }
                        }
                    }
                }

You might be able to adapt this solution to reading chunks of a file - e.g. as you read through the StreamReader, assign different "chunks" to a Collection of StringBuilder objects and also pre-pend the header row if you want it.