Generate reports for all invalid data - Csv file data type validation?

Question

The following code defined a schema of CSV file and it will be used to load a lot CSV files. However, there are some data error, for example, date not in correct format, missing data for required fields, etc. How to generate reports for the files using the schema for all the invalid data. And the Load will only be executed if there is no error.

type MyCsvType = CsvProvider<Schema = "A (int), B (string), C (date)", HasHeaders=true>
MyCsvType.Load(myFile) // Execute when there is no data error

A proposed error report example:

A
  Rows with missing values: 20, 40, 60, ...
  Rows with invalid values: 30(NaN), 31(Xyz), ....
B
C
  Rows with invalid values: 200 (2015Q1), ....

Tomas Petricek Tomas Petricek · Accepted Answer · 2015-05-29T21:04:35

There is no built-in support for this - one option you have is to explicitly make the types of the columns optional and then check for None values in the data set. This way, you can get a list of rows with missing or no data:

type MyCsvType = CsvProvider<Schema="A (int option), B (string option), C (date option)", HasHeaders=false>

let c = MyCsvType.Parse(""", hi, 1/1/2001
1, hi, foo""")

printfn "Rows with missing/invalid values for A:"
c.Rows |> Seq.iteri (fun i v ->
  match v.A with
  | Some _ -> ()
  | None -> printfn "  %d" i)

Unfortunately, I don't think there is a way to get the invalid value in case when the parsing fails. Please open an issue to discuss how this could be supported!

Generate reports for all invalid data - Csv file data type validation?

1 Answers