Written by

Article Ponnumani Gurusamy · Mar 11, 2019 1m read

Global Data converted to CSV file

#Caché #CSV #Tips & Tricks

Class User.samplexlsconversion Extends (%Persistent, %Populate) [ Owner = {_PUBLIC} ]{Property name As %String;Property DOB As %Date;Property address As %String;Property PhoneNumber As %Numeric;Property BloodGroup As %String(DISPLAYLIST = "", VALUELIST = "A A- B B- O");ClassMethod ExtractXls(){Set file=##class(%Library.File).%New("C:\Users\Ponnumani\Desktop\Ponnumani Tasks\file.csv")Do file.Open("WSN")Do file.WriteLine("""Name"",""Address"",""Phone Number"",""Blood Group"",""Date""")s id=""f {set id=$Order(^User.samplexlsconversionD(id))Quit:id=""Set txt=^User.samplexlsconversionD(id)Do file.WriteLine(""""_$List(txt,2)_""","_""""_$List(txt,3)_""","_""""_$List(txt,4)_""","_""""_$List(txt,5)_""","_""""_$List(txt,6)_"""")}d file.%Save()d file.Close()kill file}

Run: d ##class(User.samplexlsconversion).Populate(1000)

d ##class(User.samplexlsconversion).ExtractXls()

Discussion (4)1

Add reply

Comments

Vladimir Iliychev · Mar 12, 2019

Hi Ponnumani,

Thank you for sharing this example.

I allow myself to rewrite a little bit your example using SQL.

I think this is a little bit more universal in the sense that you don't have to know the detailed structure of a global.

ClassMethod ExtractXls1()
{
               Set file=##class(%Library.File).%New("C:\Users\Ponnumani\Desktop\Ponnumani Tasks\file.csv")
               Do file.Open("WSN")
               Do file.WriteLine("""Name"",""Address"",""Phone Number"",""Blood Group"",""Date""")
               s sql="SELECT Name,Address,PhoneNumber,BloodGroup,Date FROM User.samplexlsconversion"
   s sc=rs.Prepare(sql)
   s sc=rs.Execute(sql)
   while rs.Next() {
s Name=rs.Data("Name")
s Address=rs.Data("Address")
s PhoneNumber=rs.Data("PhoneNumber")
s BloodGroup=rs.Data("BloodGroup")
s Date=rs.Data("Date")
                    Do file.WriteLine(""""_Name_""","_""""_Address_""","_""""_PhoneNumber_""","_""""_BloodGroup_""","_""""_Date_"""")
               }
               d file.%Save()
               d file.Close()
               kill file
}

0 0

Vladimir Iliychev Mar 12, 2019 to Vladimir Iliychev

Hmm..I missed this line

s rs=##class(%ResultSet).%New("%DynamicQuery:SQL")

-----------------------------------------------------------------------------------------------------------------------------------

ClassMethod ExtractXls1() [ ZenMethod ]
{
               Set file=##class(%Library.File).%New("D:\CacheExport\file.csv")
               Do file.Open("WSN")
               Do file.WriteLine("""UNP"",""DateS"",""TimeS"",""DateE"",""TimeE""")
        s rs=##class(%ResultSet).%New("%DynamicQuery:SQL")
   s sql="SELECT UNP,DateS,TimeS,DateE,TimeE FROM Kino_Data.FB"
   s sc=rs.Prepare(sql)
   s sc=rs.Execute(sql)
while rs.Next() {
s UNP=rs.Data("UNP")
s DateS=rs.Data("DateS")
s TimeS=rs.Data("TimeS")
s DateE=rs.Data("DateE")
s TimeE=rs.Data("TimeE")
            Do file.WriteLine(UNP_","_DateS_","_TimeS_","_DateE_","_TimeS)
               }
               d file.%Save()
               d file.Close()
               kill file
}

------------------------------------------------------------------------------------------------------------------------------------

0 0

Ponnumani Gurusamy Mar 12, 2019 to Vladimir Iliychev

Really thanks for your another perspective explaination.

0 0

Vitaliy Serdtsev · Mar 12, 2019

Hi Ponnumani.

I would like to make a number of clarifications:

VALUELIST = "A A- B B- O" -> VALUELIST = " A A- B B- O"

it is necessary to consider the order of the fields in the global on the basis of Storage. For example, I have following Storage:

<Data name="samplexlsconversionDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>name</Value>
</Value>
<Value name="3">
<Value>DOB</Value>
</Value>
<Value name="4">
<Value>address</Value>
</Value>
<Value name="5">
<Value>PhoneNumber</Value>
</Value>
<Value name="6">
<Value>BloodGroup</Value>
</Value>
</Data>

need to add check for the presence of double quotes in strings, e.g. bla"bla
the date format for CSV should not be in the internal Caché format, because the date 37893 to users of other system/DBMS nothing says unlike e.g. 1944-09-30.

I still prefer to use built-in tools when they are there, rather than reinventing the wheel.

Class User.samplexlsconversion Extends (%Persistent, %Populate)
{
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">name </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">DOB </FONT><FONT COLOR="#000080">As %Date</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">address </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">PhoneNumber </FONT><FONT COLOR="#000080">As %Numeric</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">BloodGroup </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">VALUELIST </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">" A A- B B- O"</FONT><FONT COLOR="#000000">);
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">TestExport2CSV()
{
  
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">Populate</FONT><FONT COLOR="#000000">(1000)
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%OpenId</FONT><FONT COLOR="#000000">(1)
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">name</FONT><FONT COLOR="#000000"></FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(34)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">=..</FONT><FONT COLOR="#0000ff">%OpenId</FONT><FONT COLOR="#000000">(2)
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">name</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(34)</FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">name</FONT><FONT COLOR="#000000">_</FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(34)
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">tmp</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Save</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Export.Mgr</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">FileName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">"c:\Temp\test.csv"
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">ClassName </FONT><FONT COLOR="#000000">= ..</FONT><FONT COLOR="#0000ff">%ClassName</FONT><FONT COLOR="#000000">(1)
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TableName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$$$CLASSsqltablename</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$$$gWRK</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">ClassName</FONT><FONT COLOR="#000000">)
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Delimiter </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">","
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">StringQuote </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$c</FONT><FONT COLOR="#000000">(34)
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">DateFormat </FONT><FONT COLOR="#000000">= 3
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TimeFormat </FONT><FONT COLOR="#000000">= 1
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TimeStampFormat </FONT><FONT COLOR="#000000">= 1
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">NoCheck </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$$$YES
  s </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">HasHeaders </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$$$YES
  
  s </FONT><FONT COLOR="#800000">cols</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"Name:S;Address:S;PhoneNumber:N;BloodGroup:S;DOB:D"
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=1:1:</FONT><FONT COLOR="#0000ff">$l</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">cols</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">";"</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">c</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">cols</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">";"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">ColumnNames</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Insert</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">c</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">":"</FONT><FONT COLOR="#000000">,1))
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">ColumnTypes</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Insert</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$p</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">c</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">":"</FONT><FONT COLOR="#000000">,2))
  </FONT><FONT COLOR="#800080">}
  
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">mgr</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Export</FONT><FONT COLOR="#000000">()
}
}</FONT>

0 0