# CSV to JSON using jFiles

**URL:** <https://clarionhub.com/t/csv-to-json-using-jfiles/7498>\
**Category:** tips\
**Created:** [September 26, 2024, 7:09pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498 "2024-09-26T19:09:17Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![MSegnari](https://clarionhub.com/letter_avatar_proxy/v4/letter/m/54ee81/32.png) [@MSegnari](https://clarionhub.com/u/MSegnari)\
**Post date:** [September 26, 2024, 7:09pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/1 "2024-09-26T19:09:17Z")

</div>

Trying to think of a way to read a CSV file and “convert” it to JSON (with [jFIles](https://www.capesoft.com/accessories/jFilessp.htm)). 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

---

<div class="post-metadata">

**Author:** ![CarlBarnes](https://clarionhub.com/user_avatar/clarionhub.com/carlbarnes/32/1859_2.png) [@CarlBarnes](https://clarionhub.com/u/CarlBarnes)\
**Post date:** [September 26, 2024, 9:16pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/2 "2024-09-26T21:16:01Z")

</div>

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:

> [@How to import CSV into Clarion 11 TopSpeed file without writing code](https://clarionhub.com/t/how-to-import-csv-into-clarion-11-topspeed-file-without-writing-code/3942/16):
>
> I thought that might be non 13,10 line endings so I tried a Unix (10) and MAC (13) file. The Wizard shows warnings below. The Mac file does not work and make a DCT file. The Unix file works, but did not set the Driver options to /ENDOFRECORD=10 /FIRSTROWHEADER=ON . Even though I config the Driver for 10 the Data Browser only shows the Header: (Edit: this was wrong, should have been /EndOfRecord=1,10 then everything worked.) The 13,10 file works

> [@Create a file layout from a CSV file](https://clarionhub.com/t/create-a-file-layout-from-a-csv-file/4232):
>
> Over on the Clarion 11 newsgroup Steve asked: Is there a quick way to get the file layout of a .csv file? I want to do some data processing on the csv and a .tps file. A couple of people quickly mentioned you could just import the file into the dictionary, although when setting the column widths it only checks the first 100 rows. Wolfgang mentioned: if you know the columns and if you know the delimiter, you could write a tiny tool with StringTheory, which parses the .CSV in advance an t…

---

<div class="post-metadata">

**Author:** ![vitesse](https://clarionhub.com/user_avatar/clarionhub.com/vitesse/32/837_2.png) [@vitesse](https://clarionhub.com/u/vitesse)\
**Post date:** [September 27, 2024, 8:10am UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/3 "2024-09-27T08:10:31Z")

</div>

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:

[https://www.capesoft.com/docs/jfiles3/jfiles.htm#ConstructingManually](https://www.capesoft.com/docs/jfiles3/jfiles.htm#ConstructingManually)

But I am wondering if that is going to be as much (or even more) work than just using [StringTheory](https://capesoft.com/accessories/StringTheorysp.htm) to do the job?

I’m sure Bruce will have an opinion

cheers

Geoff R

---

<div class="post-metadata">

**Author:** ![vitesse](https://clarionhub.com/user_avatar/clarionhub.com/vitesse/32/837_2.png) [@vitesse](https://clarionhub.com/u/vitesse)\
**Post date:** [September 27, 2024, 12:06pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/4 "2024-09-27T12:06:20Z")

</div>

> [@vitesse](#):
>
> 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.

```clarion
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

---

<div class="post-metadata">

**Author:** ![jslarve](https://clarionhub.com/letter_avatar_proxy/v4/letter/j/e480ec/32.png) [@jslarve](https://clarionhub.com/u/jslarve)\
**Post date:** [September 27, 2024, 1:01pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/5 "2024-09-27T13:01:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MSegnari](https://clarionhub.com/letter_avatar_proxy/v4/letter/m/54ee81/32.png) [@MSegnari](https://clarionhub.com/u/MSegnari)\
**Post date:** [September 27, 2024, 2:22pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/6 "2024-09-27T14:22:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MSegnari](https://clarionhub.com/letter_avatar_proxy/v4/letter/m/54ee81/32.png) [@MSegnari](https://clarionhub.com/u/MSegnari)\
**Post date:** [September 27, 2024, 2:23pm UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/7 "2024-09-27T14:23:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![vitesse](https://clarionhub.com/user_avatar/clarionhub.com/vitesse/32/837_2.png) [@vitesse](https://clarionhub.com/u/vitesse)\
**Post date:** [September 12, 2025, 2:58am UTC](https://clarionhub.com/t/csv-to-json-using-jfiles/7498/8 "2025-09-12T02:58:42Z")

</div>

> [@CarlBarnes](#):
>
> 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.

I just wanted to highlight that the latest version of ST (3.80) now has an option on st.split() to decode csv quoted strings. So you don’t have to worry about 9"" nails or 12"" LP records now.

You can see how this simplifies the code here:

> [@How to import CSV into Clarion 11 TopSpeed file without writing code](https://clarionhub.com/t/how-to-import-csv-into-clarion-11-topspeed-file-without-writing-code/3942/29):
>
> Well it is a few years on and a few (well 45 actually) ST versions on. As of the current ST version 3.80 there is a new “CSVDecode” option on the “remove quotes” parameter of st.split() so you don’t need to worry now about converting 9"" nails to 9" nails. so the above code can now be simplified to: str StringTheory lne StringTheory x Long,auto code str.LoadFile('Somefile.CSV') str.LineEndings() str.Split('\<13,10\>','"') loop x = 1 to str.Records() Lne.SetValue(Str.GetLi…