Setting data types at run time
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
posted by: Steven White 9-Jan-2019/13:14:12-8:00
This would be the brute-force way and it does work, but I was hoping the one-liner approach would work also. ;; Column headers and data types for each field COLUMNS: [ ["PID" "integer"] ["DATE" "string"] ] ... if equal? COLUMNS/:CNT/2 "integer" [ append CSVOUT to-integer FLD ] if equal? COLUMNS/:CNT/2 "string" [ append CSVOUT rejoin [ {"} to-string FLD {"}] ]
posted by: Steven White 9-Jan-2019/13:28:46-8:00
I am not 100% sure if the goal is to generalize the column count COLUMNS/:CNT/2 If so I would access COLUMNS/:CNT/n with pick COLUMNS/:CNT n Also use switch or case instead of if statements?
posted by: VIDpuzzle 16-Jan-2019/7:46:12-8:00
|