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
Comments
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
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.
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:
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.
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