Copy csv data into a global object

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

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