I want to take some data from an SQL query and produce a CSV file. I can do that, no problem, but the data sometimes comes in a type that I don't want and so I have to convert it. For example, a number that really is an integer might have a decimal in the raw SQL output and I have to convert it to an integer. I can do that, no problem, but I have to know what is happening and code for it in the program, which means that every time I want to do this operation of converting SQL output to a CSV file I have to write a separate program. I would like to generalize this operation and write just one program. I have an idea, as shown in the following sample, but I can't make it work. Basically, I have a table of column names and data types, and for each row of SQL data, I convert each column based on the data type in the table. I thought that one "to" function on one line of code would do the trick. I could take a more brute-force approach but I wonder if there is some simple trick I am missing.
Thank you. Sample follows (a bit long I'm afraid).
R E B O L [
Title: "Generalized data format test"
]
;; Column headers and data types for each field
COLUMNS: [
["PID" integer!]
["DATE" string!]
]
COLCOUNT: length? COLUMNS
;; Final output file
CSVOUT: ""
;; Raw data to turn into a CSV file
RAWDATA: [
[93.0 20190101]
[100.0 20190101]
]
;; Emit headings
CNT: 0
foreach COL COLUMNS [
CNT: CNT + 1
append CSVOUT COL/1
if lesser? CNT COLCOUNT [
append CSVOUT ","
]
]
append CSVOUT newline
;; Emit data
foreach REC RAWDATA [
CNT: 0
foreach FLD REC [
CNT: CNT + 1
;; -- Here we want to make sure that the fields in the raw data
;; -- have the data types specified in the COLUMNS block.
probe COLUMNS/:CNT/2 ;; for debugging
; append CSVOUT FLD ;; This puts it out as it comes in
; append CSVOUT to COLUMNS/:CNT/2 FLD ;; this produces an error
;; ...and this does not produce an error, but does not
;; give the hoped-for result
; if equal? COLUMNS/:CNT/2 integer! [
; append CSVOUT to-integer FLD
; ]
; if equal? COLUMNS/:CNT/2 string! [
; append CSVOUT rejoin [ {"} to-string FLD {"}]
; ]
if lesser? CNT COLCOUNT [
append CSVOUT ","
]
]
append CSVOUT newline
]
;; Check the result
;; Hoping for:
;; PID,DATE
;; 93,"20190101"
;; 100,"20190101"
print CSVOUT
halt