How can I convert CSV to JSON

Question

Question

Galena Teneva · Jul 5, 2018

#Beginner #CSV #ObjectScript #InterSystems IRIS

Hi,
it is possible to convert a csv file to json file?

I want to stream json files and output the data as it comes in from the files. So my files are "csv" type and I want to convert these files to "json" type.

I can read csv file as follows:

ClassMethod ReadFile()
{
   set stream = ##class(%Stream.FileCharacter).%New()
   set sc = stream.LinkToFile("*.csv")
   do stream.Rewind()
   while'stream.AtEnd {
       set line=stream.ReadLine()
       write line,!
}

What should I add to convert it to "json" and read it again?

Thank you

Discussion (7)3

Log in or sign up to continue

Julius Kavay · Jul 5, 2018

On-the-fly and untested:

set del=<your_csv_delimiter> // <;>, <tab>, <whatever...>

while'stream.AtEnd {
set line=stream.ReadLine()
write line,! // this is csv

set json=[]

for i=1:1:$l(line,del) do json.%Push($p(line,del,i))

write json.%ToJSON(),! // or whatever other action you need...
}

HTH

Julius

2 0

Dan Pasco · Jul 6, 2018

I should update the CSV and CSV2CLASS utility procedures to take advantage of table-valued functions. Then you could select JSON_ARRAYAGG() from CSVTVF(<arguments go here>). Of course you would have to supply the constructor for the objects, probably using JSON_OBJECT.

0 0

Vitaliy Serdtsev · Sep 14, 2018

See

0 0

Vitaliy Serdtsev · Jul 6, 2018

Example of converting a CSV to JSON file and then reading from a JSON file without creating intermediate classes/tables:

  ; CSV -> JSON

  s rowtype = "name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE",
    fileIn = "C:\Temp\import.csv",
    fileOut = "C:\Temp\export.json",
    fileExp=##class(%Stream.FileBinary).%New(),
    obj=##class(%Document.Object).%New()

  d obj.loadResultSet(##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.fileIn),"res"),
    fileExp.LinkToFile(fileOut),
    obj.%ToJSONStream(fileExp),
    fileExp.%Save()

  ; Read JSON

  s fileImp=##class(%Stream.FileBinary).%New()
  d fileImp.LinkToFile(fileOut)
  s obj = ##class(%DynamicAbstractObject).%FromJSON(fileImp)
  d obj.%ToJSON()
  w !,"obj.res.%Size() = ",obj.res.%Size()
    ,!,obj.res."0"."""date"""

Result:

C:\Temp\import.csv:
car,2000,100.51,27.10.2016,
phone,2003,65.8,15.01.2017,

USER>d ^test
{"res":[{"\"date\"":"27.10.2016","amount":100.51,"name":"car","year":2000},{"\"date\"":"15.01.2017","amount":65.8,"name":"phone","year":2003}]}
obj.res.%Size() = 2
27.10.2016

5 1

score 0 · Answer 1 · 2018-08-07T09:11:52-04:00

Hi,
Thank you for your answer!

Can you please explain to me what it %Document.Object exactly does? I have searched in the documentation but I didn't really find something.
And also the following part:

%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.fileIn),"res")

Thank you in advance.

score 0 · Answer 2 · 2018-08-07T10:14:29-04:00

This is one of those things that becomes dead easy when you use Node.js (and use QEWD to integrate Cache with Node). There's almost always a tried and tested module out there already that will do the stuff you want, eg in this case:

https://www.npmjs.com/package/csvtojson

And once in JSON format, you can transform it to whatever other JSON format you want using:

https://github.com/robtweed/qewd-transform-json

score 2 · Answer 3 · 2018-07-05T12:35:16-04:00

First you need to load CSV into class.

After that you can convert objects of your new class into json using SQL JSON_OBJECT function or %ZEN.Auxiliary.altJSONProvider.