Search code examples
f#type-providersexcel-dnaf#-data

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

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.

Solution

  • 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