ED Coder · 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



5 0 7 586


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)

Hi Sean, 

Thank You so much. I will give the above a try. By the way, if I want the global to display like below, what can I do?

Also, can you advice what does "<>W" do?

^People("Customers", "Acc.divison", "1234.567")

^People("Customers", "Acc.divison", "1235.987")

^People("Customers", "Acc.divison", "3214.879")

"<>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...

for display in terminal:

ZWRITE ^People
ZWRITE ^People("Customers")
ZWRITE ^People("Customers", "Acc.divison")

depending on your needs

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