Trying to think of a way to read a CSV file and “convert” it to JSON (with jFIles). The first record in the CSV is a header, and would like to use those values for the data element names for the additional records in the file. All the data fields are in the CSV are in double quotes.
The Clarion DCT has great support for creating a Basic File Specification by reading a CSV. Then you can wizard a Process to read them all and code for JFiles. I would think Json.Save(CsvFile:Record,…more). I do not use jFile. You can also create a Report or Browse.
You can read CSV files string StringTheory but it does not handle quoted strings any where near as well as the BASIC driver without a bunch of odd code.
It is possible I am misunderstanding the question but I think Mark is after some way to convert csv to json at runtime without knowing the structure of the csv at compile time - so not able to import file into dct etc. Is that right Mark?
I’m no expert on jFiles but generally jFiles excels where there is a Clarion structure like a file, queue or view etc that can be saved to json with one statement like: json.save(filename,‘myFile.json’)
Maybe look at this part of the docs about manual construction:
But I am wondering if that is going to be as much (or even more) work than just using StringTheory to do the job?
Just knocked up a quick procedure using ST that does this. Give it a try and let us know how you go. You may need to “salt to taste” for your requirements but it should at least give you a good starting point.
CSVtoJSON PROCEDURE (STRING pCSV,STRING pJSON) ! Declare Procedure
! (c) 2024 Geoff Robinson Vitessegr at gmail dot com
! 27 September 2024
! released under the MIT License https://opensource.org/license/mit
st StringTheory
hdr StringTheory
out StringTheory
lne StringTheory
x long,auto
y long,auto
startJson string('[<13,10>')
startRec string(' {{<13,10>')
endRec string('<13,10> },<13,10>')
endJson string('<13,10>]')
CODE
if ~st.loadFile(clip(pCSV))
message('Failed to load file ' & clip(pCSV) &'||Error: '& st.LastError ,'File not Loaded')
return
end
st.split('<13,10>','"')
st.removeLines()
! first row is assumed to be header
hdr.setValue(st.getLine(1))
st.DeleteLine(1)
hdr.split(',','"')
loop x = 1 to hdr.records() ! make sure all fields are quoted
hdr.SetValue(hdr.getLine(x))
hdr.trim()
hdr.quote()
hdr.setLine(x,hdr.getValuePtr())
end
out.setValue(startJson)
loop x = 1 to st.records() ! loop through lines
lne.setValue(st.getLine(x))
lne.split(',','"') ! split into fields
out.append(startRec)
loop y = 1 to lne.records() ! loop through fields
lne.setValue(lne.getLine(y))
lne.quote()
if lne.len() < 1 then lne.setValue('""'). ! or CYCLE if you want to leave out blank fields
out.append(' ' & |
choose(y <= hdr.records(), hdr.getLine(y), '"Field'&y&'"') & |
' : ' & lne.getValue() & ',<13,10>')
end
out.adjustLength(-3) ! get rid of comma CR LF
out.append(endRec)
end
out.adjustLength(-3) ! get rid of comma CR LF
out.append(endJson)
out.SaveFile(clip(pJSON))
Having converted it to JSON you could presumably read it into jFiles if you wanted to do further processing or transformations. Anyway I will be interested to see if there is a pure jFiles way to go about it.
JFiles can be used for this, but I think it’s probably not much more work to do it with just ST.
Either way you’d want some kind of way to determine/specify what the data types are. I’d, at minimum, want to know if it’s a string, number, or boolean. You’d want to encode the special characters accordingly.
JFiles is really cool though. It’s basically a tree of objects that are easy to visualize while you’re using it. Create an object for a record, add the fields (which become json objects that are children of the record), then add that object as a row of the main object. But you’d still somehow need to glean and/or specify the data type to get the JSON output right. Or just call everything a STRING and be done with it
I have JSON export worked out in my head for my csv class, but need to find the time. It’s not much code, really.
That’s great! I was hoping someone had a jFiles method to import string theory lines into json, but this will help me a lot because the vendor will not promise the file layout when I get the CSV. They can tell me what columns to use, but they can’t promise the columns I need will be the only columns in the file.