Global Data converted to CSV file

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

Comments

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""")
               sql="SELECT Name,Address,PhoneNumber,BloodGroup,Date FROM User.samplexlsconversion"
     sc=rs.Prepare(sql)
     sc=rs.Execute(sql)
     while rs.Next() {
  Name=rs.Data("Name")
  Address=rs.Data("Address")
  PhoneNumber=rs.Data("PhoneNumber")
  BloodGroup=rs.Data("BloodGroup")
  Date=rs.Data("Date")
                    Do file.WriteLine(""""_Name_""","_""""_Address_""","_""""_PhoneNumber_""","_""""_BloodGroup_""","_""""_Date_"""")
               }
               file.%Save()
               file.Close()
               kill file
}

Hmm..I missed this line

   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""")
        rs=##class(%ResultSet).%New("%DynamicQuery:SQL")
     sql="SELECT UNP,DateS,TimeS,DateE,TimeE FROM Kino_Data.FB"
     sc=rs.Prepare(sql)
     sc=rs.Execute(sql)
  while rs.Next() {
  UNP=rs.Data("UNP")
  DateS=rs.Data("DateS")
  TimeS=rs.Data("TimeS")
  DateE=rs.Data("DateE")
  TimeE=rs.Data("TimeE")
            Do file.WriteLine(UNP_","_DateS_","_TimeS_","_DateE_","_TimeS)
               }
               file.%Save()
               file.Close()
               kill file
}

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

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

Property name As %String;

Property DOB As %Date;

Property address As %String;

Property PhoneNumber As %Numeric;

Property BloodGroup As %String(VALUELIST " A A- B B- O");

ClassMethod TestExport2CSV()
{
  
  ..%KillExtent()
  ..Populate(1000)
  
  tmp=..%OpenId(1)
  tmp.name=tmp.name_$c(34)
  tmp.%Save()
  
  tmp=..%OpenId(2)
  tmp.name=$c(34)_tmp.name_$c(34)
  tmp.%Save()

  mgr ##class(%SQL.Export.Mgr).%New()
  mgr.FileName "c:\Temp\test.csv"
  mgr.ClassName = ..%ClassName(1)
  mgr.TableName $$$CLASSsqltablename($$$gWRK,mgr.ClassName)
  mgr.Delimiter ","
  mgr.StringQuote $c(34)
  mgr.DateFormat = 3
  mgr.TimeFormat = 1
  mgr.TimeStampFormat = 1
  mgr.NoCheck $$$YES
  s mgr.HasHeaders $$$YES
  
  s cols="Name:S;Address:S;PhoneNumber:N;BloodGroup:S;DOB:D"
  i=1:1:$l(cols,";"{
    c=$p(cols,";",i)
    mgr.ColumnNames.Insert($p(c,":",1))
    mgr.ColumnTypes.Insert($p(c,":",2))
  }
  
  mgr.Export()
}
}