CSV to JSON using jFiles

questions tips
1

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.

Thanks,
Mark

2

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.

Here is some discussion:

1 Like
3

Hi Mark and Carl

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?

I’m sure Bruce will have an opinion

cheers

Geoff R

1 Like
4

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.

cheers

Geoff R

1 Like
5

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 :slight_smile:

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.

2 Likes
6

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.

7

Thanks for the discussion links Carl! I use CSS almost daily so much appreciated!