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

Regards,

Eric

  • 0
  • 0
  • 75
  • 3
  • 2

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