Écrit par

Question Galena Teneva · Juil 5, 2018

How can I convert CSV to JSON

#InterSystems IRIS #Débutant #CSV #ObjectScript

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

Connectez-vous ou inscrivez-vous pour continuer

Ajouter une réponse

Comments

Julius Kavay · Juil 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

0 0

Dan Pasco Juil 6, 2018 to Vitaliy Serdtsev

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

Galena Teneva Août 7, 2018 to Vitaliy Serdtsev

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.

0 0

Rob Tweed Août 7, 2018 to Galena Teneva

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

0 0

Vitaliy Serdtsev Sept 14, 2018 to Galena Teneva

See

0 0

Eduard Lebedyuk · Juil 5, 2018

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.

0 0

Vitaliy Serdtsev · Juil 6, 2018

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

  <FONT COLOR="#008000">; CSV -> JSON
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">rowtype </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">"name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE"</FONT><FONT COLOR="#000000">,
    </FONT><FONT COLOR="#800000">fileIn </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">"C:\Temp\import.csv"</FONT><FONT COLOR="#000000">,
    </FONT><FONT COLOR="#800000">fileOut </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">"C:\Temp\export.json"</FONT><FONT COLOR="#000000">,
    </FONT><FONT COLOR="#800000">fileExp</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Stream.FileBinary</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">(),
    </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Document.Object</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">loadResultSet</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"call %SQL_Util.CSV(,?,?)"</FONT><FONT COLOR="#000000">,.</FONT><FONT COLOR="#800000">rowtype</FONT><FONT COLOR="#000000">,.</FONT><FONT COLOR="#800000">fileIn</FONT><FONT COLOR="#000000">),</FONT><FONT COLOR="#008000">"res"</FONT><FONT COLOR="#000000">),
    </FONT><FONT COLOR="#800000">fileExp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">LinkToFile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">fileOut</FONT><FONT COLOR="#000000">),
    </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%ToJSONStream</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">fileExp</FONT><FONT COLOR="#000000">),
    </FONT><FONT COLOR="#800000">fileExp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#008000">; Read JSON
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">fileImp</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%Stream.FileBinary</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">fileImp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">LinkToFile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">fileOut</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">obj </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%DynamicAbstractObject</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%FromJSON</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">fileImp</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%ToJSON</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!,</FONT><FONT COLOR="#008000">"obj.res.%Size() = "</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">res</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Size</FONT><FONT COLOR="#000000">()
    ,!,</FONT><FONT COLOR="#800000">obj</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">res</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">"0"</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">"""date"""</FONT>

Result:

C:\Temp\import.csv:
car,2000,100.51,27.10.2016,
phone,2003,65.8,15.01.2017,
USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">^test</FONT>
<FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"res"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#ff00ff">[{</FONT><FONT COLOR="#008000">"&quot;date&quot;"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"27.10.2016"</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"amount"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">100.51</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"name"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"car"</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"year"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">2000</FONT><FONT COLOR="#ff00ff">}</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#ff00ff">{</FONT><FONT COLOR="#008000">"&quot;date&quot;"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"15.01.2017"</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"amount"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">65.8</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"name"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#008000">"phone"</FONT><FONT COLOR="#808080">,</FONT><FONT COLOR="#008000">"year"</FONT><FONT COLOR="#808080">:</FONT><FONT COLOR="#000000">2003</FONT><FONT COLOR="#ff00ff">}]}</FONT>
obj.res.%Size() = 2
27.10.2016

0 0