CsvProvider throws OutOfMemoryException

Question

FAOCropsLivestock.csv contains more than 14 million row. In my .fs file I have declared

type FAO = CsvProvider<"c:\FAOCropsLivestock.csv">

and tried to work with follwoing code

FAO.GetSample().Rows.Where(fun x -> x.Country = country) |> ....
FAO.GetSample().Filter(fun x -> x.Country = country) |> ....

In both cases, exception was thrown.

I also have tried with follwoing code after loading the csv file in MSSQL Server

type Schema = SqlDataConnection<conStr>
let db = Schema.GetDataContext()
db.FAOCropsLivestock.Where(fun x-> x.Country = country) |> ....

it works. It also works if I issue query using OleDb connection, but it is slow.

How can I get a squence out of it using CsvProvider?

Have you tried creating a small .csv document with, for example, the first 100 lines of that big file? This should give the CSV provider something to work with without overwhelming it. I don't know if it'll work, but it might be able to parse the real file afterwards... Worth a try, I'd say.... — Mark Seemann
@MarkSeemann CsvProvider has Skip and Take functions to do so ... But it probably will take more time than that of OleDb. — user6283344
Every time you try to compile your code, a type provider will load in the sample document and attempt to infer types from it. If the sample document has 14 million rows, it may not be surprising that it runs out of memory. If you give it a small sample, it may actually be able to do its job. — Mark Seemann
See for example this answer that writes and reads 10 million lines to a text file using basic F# IO functionality stackoverflow.com/a/34229014/126014 — Mark Seemann
I will apply your technique in F#. I used StreamReader and StreamWriter in C# earlier for flat files. — user6283344

TheInnerLight TheInnerLight · Accepted Answer · 2016-11-28T20:45:22

If you refer to the bottom of the CSV Type Provider documentation, you will see a section on handling large datasets. As explained there, you can set CacheRows = false which will aid you when it comes to handling large datasets.

type FAO = CsvProvider<"c:\FAOCropsLivestock.csv", CacheRows = false>

You can then use standard sequence operations over the rows of the CSV as a sequence without loading the entire file into memory. e.g.

FAO.GetSample().Rows |> Seq.filter (fun x -> x.Country = country) |> ....

You should, however, take care to only enumerate the contents once.

CsvProvider throws OutOfMemoryException

1 Answers