Back
  1. Home
  2. Ensemble
  3. Copy csv data into a global object

Copy csv data into a global object

Caché

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
Eric David's picture
Post by Eric David 19 November 2018

Answers

Sean Connelly's picture
Answer by Sean Connelly 19 November 2018

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

0
Accepted answer
Eric David's picture
Comment by Eric David 19 November 2018

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

0
Sean Connelly's picture
Comment by Sean Connelly 19 November 2018

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

0
Robert Cemper's picture
Comment by Robert Cemper 19 November 2018

for display in terminal:

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

depending on your needs

0