A few months ago, I had to develop two features:
- Export and import data to Excel files
- Publish the TSV output of some existing Clarion processes to JSON/XML webservices
For both, I needed a straightforward way to convert between Clarion structures and Comma or Tab Separated Values.
I looked at the excellent CSVParseClass, but it didn’t have many of the features I needed, and for the parsing part I already had code I wrote years ago, so I decided to create a new class with a different approach (although I used and still use the CSVParserClass demo to create queue declarations from sample CSV files).
The class has been in production for months, but I finally had time to clean it, partially document it and share it. It’s available at GitHub.
To serialize a queue to a text file:
myQ QUEUE,PRE(myQ) id LONG Name STRING(30) Date DATE Time TIME END fs FlatSerializer CODE ... fs.Init fs.SerializeQueueToTextFile(myQ,'testqueue.csv')
testqueue.csv text file will look like this:
ID,NAME,DATE,TIME 5,"Some Name",2021-07-30,18:45:56 7,"Another Name",2021-12-16,08:12:34
To load the same text file to a queue:
FREE(myQ) fs.Init fs.LoadTextFile('testqueue.csv') fs.DeSerializeToQueue(myQ)
The class is named Flat Serializer because CSV/TSV are a flat file formats, and because it flattens Clarion structures. For example, a group inside a group will be flattened like this:
MyGroup GROUP SomeString STRING FullName GROUP FirstName STRING LastName STRING END END SomeString,FullName,FirstName,LastName Abc,Carlos Gutierrez,Carlos,Gutierrez
You can use
AddExcludedFieldByReference() the exclude either
LastName from the output.
The class uses the TUFO interface, published by Oleg Rudenko and Mike Duglas.
Feedback is welcome.
Edit Oct. 17, 2021
|Base line time (first release)||43.6|
|String reference and slicing when loading file||-6.3|
|Readonly mode and filebuffers||-0.5|
|Pre-resolving field aliases||-5.7|
I also added a tiny local class
fsDynString (inspired by the
StringClass coded in SV’s
TreeViewWrap.clw) to replace
ANY as the unknown length string storage. It didn’t have any noticeable effect on performance in deserializing, probably it helps when serializing, but I didn’t benchmark that.
Edit Nov. 2, 2021
GetColumnName, to query the structure of the loaded file.
GetValueByName now automatically converts dates and times (matching
hh:mm:ss) to Clarion standard date and time, and removes commas (thousand separators) from numbers in TSV. Can be disabled passing
Edit Nov. 7, 2021
FALSE): Use the first alias added with
AddFieldAliasByReference as column name when serializing, overriding the fields label and
Available at GitHub.