· Nov 19, 2018

Copy csv data into a global object

Hi, I have a CSV file with a list of 5000 records in the following format

Name, Acc, division

Eric, 1234, 567

John, 1235, 987

Peter, 3214, 879

I just want to copy the Acc, division to a global so eventually the global would be like the following:

^People("Customers", "Acc.division")

Can you advice on how I can perform this from the terminal? This is a one time task. I want to read all the values from the csv file and insert them into the global



Discussion (7)2
Log in or sign up to continue

Hi Eric,

Assuming you don't have escaped characters in the first or second field, you can just paste the following three lines into the terminal.

set file=##class(%Stream.FileCharacter).%New()
do file.LinkToFile("c:\Temp\file.csv")
for  quit:file.AtEnd  set ^People("Customers", "Acc.division",$zstrip($piece(file.ReadLine(),",",2),"<>W"))=""

You will end up with a global that looks like this...


If you have more complex / escaped data then here is an example of a CSV reader that will safely traverse the data (look at the Test examples)

"<>W" will strip whitespace so it doesn't end up in your global keys, more here on this...

Globals have a tree like structure, you can have many combinations of branches in the structure, but eventually your end branches (or leaves) will have to contain a value, even if that value is an empty string. It is not possible to set these end branches without a value, so you will always end up with a global looking like this...

^People("Customers", "Acc.divison", 1234.567)=""
^People("Customers", "Acc.divison", 1235.987)=""
^People("Customers", "Acc.divison", 3214.879)=""

Note also that the last key is a numeric value, so it will be automatically sequenced in numerical order, before any string values.

There is a post here that goes into more detail on globals...

Hi All

There is another (hidden and undocumented) method

At the top of the class
Include %sqlmgrUtil

And then you have access to a useful macro $$parseString

I find this useful when I need to do some transforms on the csv data rather than just suck the raw data into a class

Code snippit
    set oStream    =##class(%Library.FileCharacterStream).%New()
    set sc                  =oStream.LinkToFile("somefile.csv") 
    if sc'=$$$OK break
    do oStream.Rewind()
    set xDelim=",", xQuote=""""
    while 'oStream.AtEnd()
           set xLine=oStream.ReadLine(10000)
           set xLine=$zstrip(xLine, ">wp")
           set x1=$$parseString(xLine, xDelim, xQuote, .arr)
          ///you now have the individual cells from the csv in the array "arr" 
         ///and you can process/transform them individually

Look in for the definition


Take a look at the %SQL.Util.Procedures class - specifically the CSVTOCLASS method. The interface isn't very nice and it isn't intuitive but it will do what you want - and more. You can invoke this directly or you can execute it as an SQL procedure. I worked up a very quick example using the data you included - I just created a simple file. In the file you can replace the column headers with a ROWTYPE (SQL standard defines this thing) or you can pass the ROWTYPE as an argument (that is what I did here).

USER>do ##class(%SQL.Util.Procedures).CSVTOCLASS(,"Name VARCHAR(50),Acc INT,Div INT","/Users/danp/projects/csvdemo/data.csv",,,1,"User.Customers")

USER>zw ^User.CustomersD





Alternatively, you can use the CSV() method to simply return a result set .

USER>do ##class(%SQL.Util.Procedures).CSV(,"Name VARCHAR(50),Acc INT,Div INT","/Users/danp/projects/csvdemo/data.csv")    

USER>set result = %sqlcontext.%NextResult()

USER>w result.%Next()


USER>do result.%Print()

Name Acc division

USER>write result.%Next()


USER>write result.Name


USER>write result.Acc


USER>write result.Div