Global Data converted to CSV file

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
}

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()
{
  
  d ..%KillExtent()
  d ..Populate(1000)
  
  s tmp=..%OpenId(1)
  s tmp.name=tmp.name_$c(34)
  d tmp.%Save()
  
  s tmp=..%OpenId(2)
  s tmp.name=$c(34)_tmp.name_$c(34)
  d tmp.%Save()

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