F# Generate SQL from CSV

Question

I have recently been learning F# and functional programming. One application I have found very useful is generating SQL inserts for a data load from a CSV (or excel table) with some associated ID's.

The following code is my result, which I believe I will find very handy in the future. I thought others could also benefit from this and I welcome suggestions and other scripts which people have found invaluable in their collection:

// Returns some dataload SQL for area mapping.
open System
open System.IO

// Read and split CSV into lists
let map_ngo_area = File.ReadAllLines(@"P:\MY_TABLE.csv") 
                        |> Array.to_list
                        |> List.map(fun x -> (x.Split([|','|]) 
                                                |> Array.map(fun y -> y.Trim())) 
                                                |> Array.to_list)

// Output Formatting function
let format_sql_record = "INSERT INTO MyTable
     (ID, REF1_ID, REF2_ID, CreatedUser, CreatedDateTime, LastModifiedUser, LastModifiedDateTime)
     VALUES
     ( {0}, {1}, {2}, 'system', getDate(), 'system', getDate() )"

 // Generate the SQL for the given list.  
let generate_sql list = list |> List.mapi(fun index row -> 
                                            match row with
                                                | [ngo_id; area_id] -> String.Format(format_sql_record, ((int index)+1), ngo_id, area_id) |> printfn "%s"
                                                | _ -> printfn "")

// Main Execution
map_ngo_area |> generate_sql |> ignore

// End of program, pause to view console output.
System.Console.ReadKey() |> ignore

Any suggestions on improving my F# code or process? Comments also welcome, as I am fairly new at this paradigm and shifting thinking is not as forthcoming as I expected.

Thanks :)

Answer 1

Here are a few suggestions:

• Don't use List.mapi with functions that return unit, since there's not much you can do with the resulting unit list. You should use List.iteri instead, which will allow you to omit the |> ignore at the end of your main execution section.
• Going further, rather than having generate_sql print the generated lines one at a time, it might be better to generate a list of strings instead. Note that in this case, you would go back to using List.mapi since the function that you apply would return a result for each line.
• Use F#'s print formatting rather than String.Format. For instance rather than having format_sql_record be a string, have it be a function of type int->string->string->string: let format_sql_record = sprintf "INSERT INTO ... VALUES (%i, %s, %s, ...)"
• Depending on the version of F# that you're using, you should probably be using the renamed function Array.toList instead of Array.to_list, since that's the name that will be used in the final release of F#.

Answer 2

You can use pattern matching on arrays too:

let map_ngo_area = File.ReadAllLines(@"P:\MY_TABLE.csv") 
                    |> Array.to_list
                    |> List.map(fun x -> (x.Split([|','|]) 
                                            |> Array.map(fun y -> y.Trim())) 

let generate_sql list = list |> List.mapi(fun index row -> 
                                            match row with
                                            | [| ngo_id; area_id |] -> printfn ...
                                            | _ -> printfn "")