Copy csv data into a global object

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

  • 0
  • 0
  • 326
  • 3
  • 4

Answers

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