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
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.
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",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", 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:
or
ZWRITE ^People("Customers")
or
ZWRITE ^People("Customers", "Acc.divison")
depending on your needs
Here's how you can import CSVs into Caché without writing any code yourself.
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 .
-Dan
Social networks
InterSystems resources
To leave a comment or answer to post please log in
Please log in
To leave a post please log in