How can I convert CSV to JSON

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

  • 0
  • 1
  • 237
  • 4
  • 3

Answers

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

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

  ; CSV -> JSON

  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()

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

  ; Read JSON

  fileImp=##class(%Stream.FileBinary).%New()
  fileImp.LinkToFile(fileOut)
  obj ##class(%DynamicAbstractObject).%FromJSON(fileImp)
  obj.%ToJSON()
  !,"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>^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

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.

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.