Performance differences between using F# Data CSV Type Provider vs CSV Parser and Reader

Question

I am writing an F# addin UDF for Excel using ExcelDNA where ideally I would like to dynamically load in a CSV based on the content of another CSV.

I have been using at the type provider and csv parser in the F# Data library below: http://fsharp.github.io/FSharp.Data/library/CsvProvider.html

The type provider will not let me pass a variable as a string prior to compile time as it is not static (perhaps I am doing something wrong - please see code below which does not work).

let getcontractperiod = new CsvProvider<"P:/Datastructures/contract_variables.csv", ",">()
let contractperiodfilelocation = getcontractperiod.Filter(fun row -> row.ContractModelID="MyContractTest").Data
let closescc = [| yield! contractperiodfilelocation |> Seq.map (fun x -> x.FileLocation) |]


let onstructure = new CsvProvider<closescc.[0], ",">()

Invalid static argument to provided type. Expected an argument of kind 'string'.

However if I use the CSV Parser from the same library it works (however I lose the strongly typed aspects of the provider).

let getcontractperiod = new CsvProvider<"P:/Datastructures/contract_variables.csv", ",">()
let contractperiodfilelocation = getcontractperiod.Filter(fun row -> row.ContractModelID="MyContractTest").Data
let closescc = [| yield! contractperiodfilelocation |> Seq.map (fun x -> x.FileLocation) |]

let onstructure = CsvFile.Load(closescc.[0]).Cache()

What I would like to know is this:
Since this would be called multiple times from within Excel, if there would be a significant performance penalty to using the CsvParse method versus the CSV Type provider, particularly if I need to convert/cast anything in the csvparse method.

Note I would need to load in approximately 4 csvs for each calculation, the csvs are about 600-2000 lines long. I don't have the option of going to a database at this stage.

Thank you.

Why not try it and see. Also, I think you meant for one of the code blocks to be different. — John Palmer
I'm not sure how CSV type provider works, but in the case of SqlEntityConnection (database provider) it indeed needs a connection string up front to create the types, but then you can supply different connection string as an argument when creating the context. It works great, as long as two databases are the same. Is there no such option in CSV provider? — Patryk Ćwiek

Gustavo Guerra Gustavo Guerra · Accepted Answer · 2014-01-23T11:23:34

The static string argument you pass to CsvProvider is a template for it to infer the schema, but you can use a different value at runtime. So instead of this:

let sample = new CsvProvider<"Sample.csv">()

Do this:

let csv = CsvProvider<"Sample.csv">.Load("runtimeLocation.csv")

or this:

type CsvType = CsvProvider<"Sample.csv">
let csv = CsvType.Load("runtimeLocation.csv")

As for performance, the CsvProvider uses the same csv parser internally, so CsvFile should have similar performance characteristics, the difference is just on the strong typing

Performance differences between using F# Data CSV Type Provider vs CSV Parser and Reader

1 Answers