Copy csv data into a global object

Primary tabs

Caché, Ensemble

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

Regards,

Eric

Replies

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

^People("Customers","Acc.division",1234)=""
^People("Customers","Acc.division",1235)=""
^People("Customers","Acc.division",3214)=""


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)

https://gist.github.com/SeanConnelly/cb4ff970c2df5266d24c8802d56f48da

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

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RCOS_fzstrip

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

https://community.intersystems.com/post/globals-are-magic-swords-managing-data-part-1

for display in terminal:

ZWRITE ^People
or
ZWRITE ^People("Customers")
or
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 %sqlmgrUtil.inc for the definition

Peter

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

^User.CustomersD=3

^User.CustomersD(1)=$lb("","Eric",1234,567)

^User.CustomersD(2)=$lb("","John",1235,987)

^User.CustomersD(3)=$lb("","Peter",3214,879)

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

1

USER>do result.%Print()

Name Acc division



USER>write result.%Next()

1

USER>write result.Name

Eric

USER>write result.Acc

1234

USER>write result.Div

567


-Dan