REBOL [
    Title: "Functions for viewing a CSV file"
]

;; [---------------------------------------------------------------------------]
;; [ This is a module of functions for browsing any CSV file in a particular   ]
;; [ format, creating words and values from a csv file.                        ]
;; [ to be more specific, we start with a csv file that has a line of          ]
;; [ headings as the first line.  Each word in the line of headings            ]
;; [ is going to be the name of the corresponding item in each following       ]
;; [ record of the csv file.  For example:                                     ]
;; [     name,address,birthdate                                                ]
;; [     "John Smith","1800 W Old Shakopee Rd",01-JAN-2000                     ]
;; [     "Jane Smith","2100 1ST Ave",01-FEB-1995                               ]
;; [     "Jared Smith",3500 2ND St",01-MAR-1998                                ]
;; [ The above text file is like a little data file.                           ]
;; [ We will "open" the file by performing some function, and then we          ]
;; [ will "read" "records" from the file.                                      ]
;; [ Every time we read a record, the words 'name, 'address, 'birthdate        ]
;; [ will have, as values, the values from the record we just read.            ]
;; [ In other words, when we "read" the first record, the following            ]
;; [ situation will exist:                                                     ]
;; [     name = "John Smith"                                                   ]
;; [     address = "1800 W Old Shakopee Rd"                                    ]
;; [     birtdhdate = 01-JAN-2000                                              ]
;; [ Then, when read the next record, those same words of 'name, 'address,     ]
;; [ and 'birthdate will refer to the values from the second record.           ]
;; [ And so on to the end of the file.                                         ]
;; [ Then, when we try to read beyond the end, we will get an indicator        ]
;; [ that we have reached the end of the file.                                 ]
;; [                                                                           ]
;; [ This module was designed as functions for viewing the contents of a file  ]
;; [ in a window, so there is the obvious function to display the window       ]
;; [ plus functions for reading the first, last, next, and previous            ]
;; [ data records.                                                             ]
;; [                                                                           ]
;; [ As an additional service, we want to provide the ability to rewrite       ]
;; [ a csv file after we make changes.  So, when we "open" a file, we also     ]
;; [ will copy the headings to an output area just in case we want to          ]
;; [ rewrite the file.  Then, we will provide a "write" procedure that will    ]
;; [ make a csv record out of the current data and append it to the output     ]
;; [ area.  A "close" procedure will write the output area to disk.            ] 
;; [---------------------------------------------------------------------------]

;; [---------------------------------------------------------------------------]
;; [ These are the data items used to get the csv file into memeory,           ]
;; [ pick off the first record of column headings, and so on.                  ]
;; [---------------------------------------------------------------------------]

CSV-FILE: none          ;; Name of the file, will come from caller 
CSV-LINES: none         ;; The entire contents of the file
CSV-HEADINGS: none      ;; Words from the first line as strings
CSV-WORDS: none         ;; The words from the first line as words
CSV-WORDCOUNT: 0        ;; Number of heading words 
CSV-RECORD: none        ;; The current data record, in the CSV-READ procedure
CSV-VALUES: none        ;; The parsed values from a single data line
CSV-EOF: false          ;; End-of-file flag when we "read" beyond last "record"
CSV-LENGTH: 0           ;; Number of lines in the file, including heading line
CSV-COUNTER: 0          ;; Record counter as we move through the file
CSV-VAL-COUNTER: 0      ;; For stepping through values in one record
CSV-OUTPUT-LINES: none  ;; Copy of the input file, with modifications 
CSV-OUTPUT-FILE: none   ;; Name of output file
CSV-OUTPUT-REC: none    ;; One output record
CSV-COMMACOUNT: 0       ;; Used to NOT put comma after last field of record 
CSV-IN-FIELD: false     ;; Used in comma-replacement operation
CSV-COMMA-MARKER: "%C%" ;; Will replace comma temporarily before parsing
CSV-LIST-BLOCK: copy [] ;; Word-value pairs for the display list
CSV-LIST-START: 0       ;; Starting point for list display

;; [---------------------------------------------------------------------------]
;; [ We will need a function to clear the above items so that a calling        ]
;; [ program can read more than one file.                                      ]
;; [---------------------------------------------------------------------------]

CSV-CLEAR-WS: does [
    CSV-FILE: none     
    CSV-LINES: none    
    CSV-HEADINGS: none 
    CSV-WORDS: none    
    CSV-WORDCOUNT: 0 
    CSV-RECORD: none   
    CSV-VALUES: none   
    CSV-EOF: false     
    CSV-LENGTH: 0      
    CSV-COUNTER: 0     
    CSV-VAL-COUNTER: 0 
    CSV-OUTPUT-LINES: copy ""
    CSV-OUTPUT-FILE: none
    CSV-OUTPUT-REC: none 
    CSV-COMMACOUNT: 0 
    CSV-IN-FIELD: false
]

;; [---------------------------------------------------------------------------]
;; [ Procedure to "open" the file.  What does that mean?                       ]
;; [ Read the entire file into memory.  Parse the first line into a block      ]
;; [ of words.  Make a note of the number of lines in the file.                ]
;; [ Set up a counter so we can pick our way through the file and stop         ]
;; [ when we reach the last record.                                            ]
;; [ Since this module is designed for use inside another program,             ]
;; [ this function normally will be called with a file name as argument.       ]
;; [---------------------------------------------------------------------------]

CSV-OPEN: func [
    FILE-TO-OPEN      
] [
    CSV-CLEAR-WS
    CSV-FILE: FILE-TO-OPEN
    CSV-LINES: read/lines CSV-FILE
    CSV-LENGTH: length? CSV-LINES
    append CSV-OUTPUT-LINES first CSV-LINES   ;; preparation for possible writing 
    append CSV-OUTPUT-LINES newline
    CSV-HEADINGS: parse/all first CSV-LINES ","
    CSV-WORDS: copy []
    foreach CSV-HEADING CSV-HEADINGS [
        if not-equal? "" trim CSV-HEADING [
            append CSV-WORDS to-word trim CSV-HEADING
            CSV-WORDCOUNT: CSV-WORDCOUNT + 1
        ] 
    ]
    CSV-COUNTER: 1 
    CSV-EOF: false
    return CSV-EOF 
]

;; [---------------------------------------------------------------------------]
;; [ The (optional) procedure to "close" the file.  What does that mean?       ]
;; [ To mimic the idea of opening a file I-O, meaning that we can rewrite      ]
;; [ a record after we have read it, we can write the data we have read        ]
;; [ into an output area, which will be a copy of the input file (or at        ]
;; [ least those records we have chosen to write).  The "close" procedure      ]
;; [ will write that file to disk.  You have to specify a file name,           ]
;; [ which may be the same (which will be like "saving" the file) or may       ]
;; [ be different (which will be like "saving as."                             ]
;; [---------------------------------------------------------------------------]

CSV-CLOSE: func [
    FILE-TO-CLOSE
] [ 
    CSV-OUTPUT-FILE: FILE-TO-CLOSE
    write/lines CSV-OUTPUT-FILE CSV-OUTPUT-LINES
] 

;; [---------------------------------------------------------------------------]
;; [ Procedure to "read" the file.  What does this mean?                       ]
;; [ Obtain the next line.  This is determined by "picking" based on the       ]
;; [ record counter.  If the counter becomes bigger than the file size,        ]
;; [ that means we have reached the end of the file.                           ]
;; [ Parse the line into a block of strings.                                   ]
;; [ For each word in the block of column headings, set that word to the       ]
;; [ corresponding item parsed from the data.                                  ]
;; [ We have to be sure to return the value of CSV-EOF so any calling          ]
;; [ procedure can use CSV-EOF to decide when to quit processing.              ]
;; [ There is a special little thing we do with each line before parsing it.   ]
;; [ It is possible that the data could contain commas.  It is customary       ]
;; [ that in such situations the field is enclosed in quotes.                  ]
;; [ We will assume that our data follows this custom, and take steps to       ]
;; [ to handle the possibility of commas in the data.                          ]
;; [ Before we parse a line on commas, we will go through the line one         ]
;; [ character at a time.  When we hit the first quote, we will assume that    ]
;; [ we are entering a fields.  From then on, we will replace commas with      ]
;; [ special place holders.  When we hit the next quote, we will assume        ]
;; [ we have left the field and we will stop replacing commas.                 ]
;; [ The next quote takes us into a field, the next one out, next in, etc.     ]
;; [ When we are done replacing embedded commas, we parse the line on          ]
;; [ commas.  Then, as we load each field, for each string field we check      ]
;; [ for our place holder and replace it with a comma.                         ]
;; [---------------------------------------------------------------------------]

CSV-REPLACE-EMBEDDED-COMMAS: does [
    CSV-IN-FIELD: false
    foreach CHARACTER CSV-RECORD [
        either equal? CHARACTER {"} [
            either CSV-IN-FIELD [
                CSV-IN-FIELD: false
            ] [
                CSV-IN-FIELD: true
            ]
        ] [
            if CSV-IN-FIELD [
                replace CHARACTER "," CSV-COMMA-MARKER
            ] 
        ]
    ]
]

;;  -- The reading procedures are not the best REBOL.
;;  -- We assume that CSV-COUNTER contains a valid value for the position 
;;  -- in the file of the desired record.  
;;  -- The read first/next/prev/last procedures adjust the counter and
;;  -- the call CSV-READ-SPECIFIC.
;;  -- CSV-READ-SPECIFIC also will build up CSV-LIST-BLOCK which is the
;;  -- data source for the list in the viewing window.

CSV-READ-SPECIFIC: does [
    CSV-EOF: false
    CSV-RECORD: pick CSV-LINES CSV-COUNTER
    CSV-REPLACE-EMBEDDED-COMMAS
    CSV-VALUES: parse/all CSV-RECORD ","
    CSV-VAL-COUNTER: 0
    foreach CSV-WORD CSV-WORDS [
        CSV-VAL-COUNTER: CSV-VAL-COUNTER + 1
        TEMP-VAL: pick CSV-VALUES CSV-VAL-COUNTER
        if equal? string! type? TEMP-VAL [
            replace/all TEMP-VAL CSV-COMMA-MARKER ","
        ] 
        either TEMP-VAL [
            set CSV-WORD trim TEMP-VAL                            
        ] [
            set CSV-WORD TEMP-VAL
        ]
    ]
;;  -- Must produce a block of blocks for the list style. 
;;  -- The block can contains REBOL words, and these words still will
;;  -- have their values.  This is part of the beauty of REBOL. 
    CSV-LIST-BLOCK: copy []
    foreach CSV-WORD CSV-WORDS [
        CSV-TEMP-BLOCK: copy []
        append CSV-TEMP-BLOCK to-string :CSV-WORD
        append CSV-TEMP-BLOCK get CSV-WORD
        append/only CSV-LIST-BLOCK CSV-TEMP-BLOCK
    ]
    return CSV-EOF 
]

CSV-READ-FIRST: does [
    CSV-COUNTER: 2
    CSV-READ-SPECIFIC
]

CSV-READ-NEXT: does [
    CSV-COUNTER: CSV-COUNTER + 1
    if (CSV-COUNTER > CSV-LENGTH) [
        CSV-COUNTER: CSV-LENGTH
        CSV-EOF: true
        return CSV-EOF 
    ]
    CSV-READ-SPECIFIC
]

CSV-READ-PREV: does [
    CSV-COUNTER: CSV-COUNTER - 1
    if (CSV-COUNTER < 2) [
        CSV-COUNTER: 2
        CSV-EOF: true
        return CSV-EOF
    ]
    CSV-READ-SPECIFIC
]

CSV-READ-LAST: does [
    CSV-COUNTER: CSV-LENGTH
    CSV-READ-SPECIFIC
]

;; [---------------------------------------------------------------------------]
;; [ Procedure to "write" the file.  What does this mean?                      ]
;; [ We are not really writing the file.  We are formatting the current data   ]
;; [ into a csv record and appending it to an output area.                     ]
;; [ If we do a "write" procedure for every "read" procedure, we will,         ]
;; [ in effect, copy the input file.  If we read the input, and then maybe     ]
;; [ or maybe not write to the output file, we will, in effect, filter the     ]
;; [ input file.  This is not quite like the COBOL operation of opening        ]
;; [ a file for input and output.  In COBOL, you could read a record, and      ]
;; [ then maybe or maybe not rewrite it, and at the end, you would have the    ]
;; [ same number of records in the file and maybe some of them would be        ]
;; [ altered.  Here, if you don't write the file, you don't get a record       ]
;; [ into the file, and when you close it you either write over the input      ]
;; [ file if you use the same name, or make a copy if you close under a        ]
;; [ different name.                                                           ]
;; [ Note that performing this procedure makes no sense if you don't first     ]
;; [ perform CSV-READ to read a record.                                        ]
;; [---------------------------------------------------------------------------]

CSV-WRITE: does [
    CSV-OUTPUT-REC: copy ""
    CSV-COMMACOUNT: 0 
    foreach CSV-WORD CSV-WORDS [
        append CSV-OUTPUT-REC mold get CSV-WORD ;; strings might contain commas
        CSV-COMMACOUNT: CSV-COMMACOUNT + 1 
        if (CSV-COMMACOUNT < CSV-WORDCOUNT) [
            append CSV-OUTPUT-REC ","
        ]    
    ]
    append CSV-OUTPUT-LINES CSV-OUTPUT-REC
    append CSV-OUTPUT-LINES newline
] 

;; [---------------------------------------------------------------------------]
;; [ Procedures for the buttons on the viewing window below.                   ]
;; [---------------------------------------------------------------------------]

BUTTON-FIRST: does [
    CSV-READ-FIRST
    show CSV-FIELD-LIST
    set-face CSV-LINE-NO to-string CSV-COUNTER
]

BUTTON-PREV: does [
    CSV-READ-PREV
    either CSV-EOF [
        alert "At the beginning"
        exit
    ] [
        show CSV-FIELD-LIST
        set-face CSV-LINE-NO to-string CSV-COUNTER
    ]
]

BUTTON-NEXT: does [
    CSV-READ-NEXT
    either CSV-EOF [
        alert "At the end"
        exit
    ] [
        show CSV-FIELD-LIST
        set-face CSV-LINE-NO to-string CSV-COUNTER
    ]
]

BUTTON-LAST: does [
    CSV-READ-LAST
    show CSV-FIELD-LIST
    set-face CSV-LINE-NO to-string CSV-COUNTER
]

;;  -- Searching takes advantage of the fact that we can put words
;;  -- in the drop-down, select one of those words, and then use
;;  -- the "get" function to get the value of the selected word. 
;;  -- This is the handiness of an interpreted language.


BUTTON-SEARCH: does [
    if not CSV-SEARCH-ITEMS/text [
        alert "No search column selected"
        exit
    ]
    CSV-READ-FIRST
    until [
        if equal? CSV-SEARCH-VALUE/text (get CSV-SEARCH-ITEMS/text) [
            show CSV-FIELD-LIST
            set-face CSV-LINE-NO to-string CSV-COUNTER
            exit
        ]
        CSV-READ-NEXT
    ]
    alert "Not found" 
]

BUTTON-SEARCH-AHEAD: does [
    if not CSV-SEARCH-ITEMS/text [
        alert "No search column selected"
        exit
    ]
;;  -- Read ahead from where we are. 
    until [
        if equal? CSV-SEARCH-VALUE/text (get CSV-SEARCH-ITEMS/text) [
            show CSV-FIELD-LIST
            set-face CSV-LINE-NO to-string CSV-COUNTER
            exit
        ]
        CSV-READ-NEXT
    ]
    alert "Not found" 
]

BUTTON-SEARCH-BACK: does [
    if not CSV-SEARCH-ITEMS/text [
        alert "No search column selected"
        exit
    ]
;;  -- Read backwards from where we are. 
    until [
        if equal? CSV-SEARCH-VALUE/text (get CSV-SEARCH-ITEMS/text) [
            show CSV-FIELD-LIST
            set-face CSV-LINE-NO to-string CSV-COUNTER
            exit
        ]
        CSV-READ-PREV ;; Returns EOF when we get to the beginning.
    ]
    alert "Not found" 
]

BUTTON-EXPORT-REC: does [
    CSV-WRITE
    alert "OK."
]

BUTTON-SAVE-EXPORTED: does [
    either CSV-SAVE-ID: request-file/only/save/title "Save to this file" "Save as" [
        CSV-CLOSE CSV-SAVE-ID
    ] [
        alert "No save file ID requested."
    ]    
]

;; [---------------------------------------------------------------------------]
;; [ This is the viewing window for the data in the file.                      ]
;; [ Remember, the "supply" function is executed for each "cell" in the list.  ]
;; [ In this example of a list of two columns and any number of rows,          ]
;; [ the supplied variables "count" and "index" will vary this way:            ]
;; [ 1-1, 1-2, 2-1, 2-2, 3-1, 3-2, and so on.                                  ]
;; [---------------------------------------------------------------------------]

CSV-WINDOW: [
    across
    CSV-WIN-FILE: text 600 (to-string CSV-FILE) font [size: 18 shadow: none]
    return
    CSV-FIELD-LIST: list 600x600 [
            across 
            text 200
            text 400
        ]
        supply [
;;;;;;;;;;  either even? count [face/color: white] [face/color: tan] ;; looks bad
            count: count + CSV-LIST-START
            if none? PICKED-ROW: pick CSV-LIST-BLOCK count [face/text: none exit]
            face/text: pick PICKED-ROW index
        ]
    slider 20x600 [
        CSV-LIST-START: (length? CSV-LIST-BLOCK) * value
        show CSV-FIELD-LIST
    ]
    return
    text 150 "File line number" font [size: 16 style 'bold]
    CSV-LINE-NO: text 50 (to-string CSV-COUNTER) font [size: 16 style 'bold]
    return
    text 80 "Search" font [size: 14 style 'bold] 
    CSV-SEARCH-ITEMS: drop-down 200 data CSV-WORDS ;; We can put word in a drop-down.
    text 40 "for" font [size: 14 style 'bold']
    CSV-SEARCH-VALUE: field 300
    return
    button 150 "Search" [BUTTON-SEARCH]
    button 150 "Search ahead" [BUTTON-SEARCH-AHEAD]
    button 150 "Search back" [BUTTON-SEARCH-BACK] 
    return
    button "First" [BUTTON-FIRST]
    button "Prev"  [BUTTON-PREV]
    button "Next"  [BUTTON-NEXT]
    button "Last"  [BUTTON-LAST]
    return
    button 200 "Export current record" [BUTTON-EXPORT-REC]
    button 200 "Save exported records" [BUTTON-SAVE-EXPORTED]
    return
    button "Quit"  [quit] 
    button "Debug" [halt]
]
     
;; Uncomment to test.
;; This shows the order in which the above functions should be used:
;; FIRST open the file, THEN read a record to get some data,
;; THEN create the window with the "layout" function, 
;; THEN view the window.  

;write/lines %testcsv.csv {FIELD1,FIELD2,FIELD3
;VALUE-1-1,VALUE-1-2,VALUE-1-3
;VALUE-2-1,VALUE-2-2,VALUE-2-3
;VALUE-3-1,VALUE-3-2,VALUE-3-3}
;CSV-OPEN %testcsv.csv
;CSV-READ-FIRST
;view layout CSV-WINDOW