For whatever reason I'm receiving more and more requests to do various kinds of things with CSV files. Mostly recently, I found a need to perform a couple of operations (sorting the file, and deleting an item) that sounded simple at first but eventually consumed enough time that it seemed worth packaging them into SOSLIB functions (in
fncsvutl.bsi) ...
1. Sorting a CSV file. The complication here of course is that you have to first unpack it, or parse it, into a structure suitable for sorting, then output it back to the file. You might want to select the sort-by column based on its name or its number. Additional complications are the presence or absence of one or more header lines, and if you're using CSV2XL, perhaps some //XL directives. And then finally there is the fact that you can't directly update a sequential file, so you have to read it and write the new version to a new file, then do a double-rename to save the old copy and replace it with the new one. All of that now accomplished with a single function call:
Function Fn'CSV'Sort(csvfile$ as T_NATIVEPATH:inputonly, &
sortcolno=0 as b2:inputonly, &
sortcolname$="" as s64:inputonly, &
hdrlines=0 as b2:inputonly) as i4
2. Deleting a line from a CSV file. The need for this arose from a site using a variety of CSV files as the sources for INFLD drop-down lists. It's relatively easy to let the user add to such lists, but to delete an item, they didn't want to use a text editor. The chosen solution was to use another SOSLIB CSV utility function (
Fn'CSV'XTREE() ) to put the CSV into a generic XTREE dialog with a
Delete Selected Item button. That takes care of selecting the item to delete, but we still have to locate and remove it from the file. Since XTREE CSV Mode is a variation of XTREE File Mode, for the selected item it returns the entire string (all of the columns concatenated). But that line no longer matches the original input line precisely enough for a simple string match. (For one, the quotes have been removed; for another column spacing may have been added.) The solution I ended up with was to use a combination of EDIT$() and XSTRIP.SBR to squeeze out or eliminate the blanks and quotes and trailing commas so as to be able to compare lines in a consistent way ...
input line #chin, pline$
if pline$ # "" then
! strip out all the unimportant stuff from the source strings
pline$ = edit$(pline$,EDITF_CTLS+EDITF_SPTBL+EDITF_SPTB1+EDITF_SPTBR)
if pline$ # "" then
xcall XSTRIP, pline$, """ ", 1 ! remove all quotes and spaces
xcall XSTRIP, pline$, ",", 5 ! remove all trailing commas
endif
endif
if pline$ = delitem$ then ! compare to target string (already stripped)
bmatch = .TRUE
endif
That seems to work reasonably well. The resulting function is found in that same fncsvutl.bsi module ...
Function Fn'CSV'Delete'Item(csvfile$ as T_NATIVEPATH:inputonly, &
delitem$="" as s0) as i2
One other side note about these two CSV utility modules - fncsvutl.bsi (CSV utililties) and fncsvxtr.bsi (CSV-XTREE routines) which may be of interest: both support conditional compilation of the bsi to create test programs:
.COMPIL FNCSVUTL.BSI/X:2/M/PX/C:FNCSVUTL_TEST=1
.RUN FNCSVUTL
.COMPIL FNCSVXTR.BSI/X:2/M/PX/C:FNCSVXTR_TEST=1
.RUN FNCSVXTR
Previously I've used the technique of creating a test program in [907,11] with the same name as the bsi (e.g. fncsvutl.bp for fncstutl.bsi), and that remains a reasonable approach, but in terms of code management for your own utility function modules, you might find that it's simpler to just embed the test routines in the same module as the functions. It doesn't take any extra space at runtime (since the test code is only conditionally compiled), and it saves looking for, and possibly recreating, your test programs when some issue arises or you want to enhance the routines and need more testing.
