uniPaaS 1.9d - Export through DataviewToXML


Lado Wali
 

Hi Group,

When I export data through DataviewToXML and open the .xls file in excel, the date columns shows in text format. Is there any way it can default to date format like red color below:

Thanks
Lado

image.png


Steven Blank
 

Lado,

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

HTH.

Steve Blank

On 7/30/2019 2:03 PM, Lado Wali wrote:
Hi Group,

When I export data through DataviewToXML and open the .xls file in excel, the date columns shows in text format. Is there any way it can default to date format like red color below:

Thanks
Lado

image.png


Lado Wali
 

Thank you Steve, but I do not either possess any knowledge of xml. I do not know how to create the schema file.

Lado


On Tue, Jul 30, 2019 at 5:30 PM Steven Blank <sgblank@...> wrote:

Lado,

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

HTH.

Steve Blank

On 7/30/2019 2:03 PM, Lado Wali wrote:
Hi Group,

When I export data through DataviewToXML and open the .xls file in excel, the date columns shows in text format. Is there any way it can default to date format like red color below:

Thanks
Lado

image.png


Steven Blank
 

Lado,

Maybe some XSLT expert will take pity on you and at least confirm or deny the possibility ...

It's almost midnight in Europe right now. Give them eight hours or so ...

;)

Steve Blank

On 7/30/2019 2:36 PM, Lado Wali wrote:
Thank you Steve, but I do not either possess any knowledge of xml. I do not know how to create the schema file.

Lado


On Tue, Jul 30, 2019 at 5:30 PM Steven Blank <sgblank@...> wrote:

Lado,

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

HTH.

Steve Blank

On 7/30/2019 2:03 PM, Lado Wali wrote:
Hi Group,

When I export data through DataviewToXML and open the .xls file in excel, the date columns shows in text format. Is there any way it can default to date format like red color below:

Thanks
Lado

image.png


Andreas Sedlmeier
 

On Tue, Jul 30, 2019 at 11:30 PM, Steven Blank wrote:

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

The fifth column of DataviewToXML() is the name of the schema file (.xsd) which Magic will create when this parameter is not empty. This is not a XSLT file, which would be a XSL Template file , required for Something->Something transformations, like XML->XML or XML->XSV, ...
I have a sample program which shows usage of these xml technologies here: https://github.com/asedl/XpaDemo/blob/master/Doc/Samples/XpaTXML/OXMLT001_ExportCustomers.md

The program uses DataviewToXML() to create,
an XML File customers.xml
a XSD (Schema) file customers.xsd

The sample does not use the .xsd file. Magic would however use xsd datatypes different from "xs:string" when the original columns in data view differ from text (like dat, numeric, ...)
So, basically you could ask Excel to use that schema, when it imports the XML, thats just extremely complicated in Excel and a manual process
https://support.office.com/en-us/article/xml-schema-definition-xsd-data-type-support-7cd3c906-9b9e-4a64-ba77-1b23dc5c771c

Usage of XSLT the demo program shows too. Actually it uses XSLT to convert the customer.xml from above to .csv, and then actually opens that in Excel.
CSV does however not have data types at all so result would be the same: Excel would recognize the dates as text (unless you format it differently )
The XSLT file for that XML->CSC conversion would be that : https://github.com/asedl/XpaDemo/blob/master/Templates/Customerlist.xsl
Wont help, as mentioned and XML->XLSX (with XSLT) would be pretty complicated

In my opinion the best (and most straight-forward) approach to generate Excel with Magic is:
Generate Java source  like from a (merge) template which generates the Excel you want, compile it and run it.
Thats very simple programs.
 
Actually you can use any JVM language, even one which does not require compilation like groovy
http://jameskleeh.com/groovy-excel-builder/
Its based on Apache POI

Maybe I do that "Magic -> Excel" sample one day. Described it meanwhile pretty often here ^^
.NET is an option ofc too, only that its not really cools and when you do not have a framework like Apache POI, you need to have Excel installed.

Best regards,

Andreas





Lado Wali
 

Thank you Andreas. may be I'll try to do with SQL and see if it works.



On Tue, Jul 30, 2019 at 8:02 PM Andreas Sedlmeier <sedlmeier@...> wrote:
On Tue, Jul 30, 2019 at 11:30 PM, Steven Blank wrote:

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

The fifth column of DataviewToXML() is the name of the schema file (.xsd) which Magic will create when this parameter is not empty. This is not a XSLT file, which would be a XSL Template file , required for Something->Something transformations, like XML->XML or XML->XSV, ...
I have a sample program which shows usage of these xml technologies here: https://github.com/asedl/XpaDemo/blob/master/Doc/Samples/XpaTXML/OXMLT001_ExportCustomers.md

The program uses DataviewToXML() to create,
an XML File customers.xml
a XSD (Schema) file customers.xsd

The sample does not use the .xsd file. Magic would however use xsd datatypes different from "xs:string" when the original columns in data view differ from text (like dat, numeric, ...)
So, basically you could ask Excel to use that schema, when it imports the XML, thats just extremely complicated in Excel and a manual process
https://support.office.com/en-us/article/xml-schema-definition-xsd-data-type-support-7cd3c906-9b9e-4a64-ba77-1b23dc5c771c

Usage of XSLT the demo program shows too. Actually it uses XSLT to convert the customer.xml from above to .csv, and then actually opens that in Excel.
CSV does however not have data types at all so result would be the same: Excel would recognize the dates as text (unless you format it differently )
The XSLT file for that XML->CSC conversion would be that : https://github.com/asedl/XpaDemo/blob/master/Templates/Customerlist.xsl
Wont help, as mentioned and XML->XLSX (with XSLT) would be pretty complicated

In my opinion the best (and most straight-forward) approach to generate Excel with Magic is:
Generate Java source  like from a (merge) template which generates the Excel you want, compile it and run it.
Thats very simple programs.
 
Actually you can use any JVM language, even one which does not require compilation like groovy
http://jameskleeh.com/groovy-excel-builder/
Its based on Apache POI

Maybe I do that "Magic -> Excel" sample one day. Described it meanwhile pretty often here ^^
.NET is an option ofc too, only that its not really cools and when you do not have a framework like Apache POI, you need to have Excel installed.

Best regards,

Andreas





Andreas Sedlmeier
 

Hi Lado,

How would you do that with SQL ? With SQL you can generate .CSV, that however does not allow you to specify data types and formatting options.
It would however be easy to create a generic module which f.i. automates exports of sqlite data to excel, because that module.
A quick research did however not result in anything. 

XSLT, what Steve mentioned, btw . seems to be more common than I thought. There's lots of samples in the Internet. Most of them do however not go very far and would loose type information from XML (the data types from schema) as well. (https://stackoverflow.com/questions/3534546/xsl-taking-xml-transforming-it-into-excel) . 

Andreas


On Wed, Jul 31, 2019 at 07:13 PM, Lado Wali wrote:
Thank you Andreas. may be I'll try to do with SQL and see if it works.


Keith Canniff
 

Lado,

 

Kind of late to the conversation…

 

What about having a separate formatted spreadsheet (the way you want it) that has a macro that reads the DataviewToXML output file as an input file, so that when you open the formatted spreadsheet, the macro fires off and pulls the data in from the export automatically?

 

I’m guessing the DataviewToXML output is triggered from your application, so once that export is done, the next statement would be to open the formatted spreadsheet with the macro rather than the DataviewToXML spreadsheet.

 

Just a thought, but then you might just have the macro pull data directly from the database.

 

Keith

 

From: main@magicu-l.groups.io <main@magicu-l.groups.io> On Behalf Of Steven Blank
Sent: Tuesday, July 30, 2019 5:43 PM
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] uniPaaS 1.9d - Export through DataviewToXML

 

Lado,

Maybe some XSLT expert will take pity on you and at least confirm or deny the possibility ...

It's almost midnight in Europe right now. Give them eight hours or so ...

;)

Steve Blank

On 7/30/2019 2:36 PM, Lado Wali wrote:

Thank you Steve, but I do not either possess any knowledge of xml. I do not know how to create the schema file.

 

Lado

 

 

On Tue, Jul 30, 2019 at 5:30 PM Steven Blank <sgblank@...> wrote:

Lado,

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

HTH.

Steve Blank

On 7/30/2019 2:03 PM, Lado Wali wrote:

Hi Group,

 

When I export data through DataviewToXML and open the .xls file in excel, the date columns shows in text format. Is there any way it can default to date format like red color below:

 

Thanks

Lado

 

image.png


Virus-free. www.avast.com


Lado Wali
 

Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.

Thanks
Lado

On Thu, Aug 1, 2019 at 7:46 AM Keith Canniff <kcanniff@...> wrote:

Lado,

 

Kind of late to the conversation…

 

What about having a separate formatted spreadsheet (the way you want it) that has a macro that reads the DataviewToXML output file as an input file, so that when you open the formatted spreadsheet, the macro fires off and pulls the data in from the export automatically?

 

I’m guessing the DataviewToXML output is triggered from your application, so once that export is done, the next statement would be to open the formatted spreadsheet with the macro rather than the DataviewToXML spreadsheet.

 

Just a thought, but then you might just have the macro pull data directly from the database.

 

Keith

 

From: main@magicu-l.groups.io <main@magicu-l.groups.io> On Behalf Of Steven Blank
Sent: Tuesday, July 30, 2019 5:43 PM
To: main@magicu-l.groups.io
Subject: Re: [magicu-l] uniPaaS 1.9d - Export through DataviewToXML

 

Lado,

Maybe some XSLT expert will take pity on you and at least confirm or deny the possibility ...

It's almost midnight in Europe right now. Give them eight hours or so ...

;)

Steve Blank

On 7/30/2019 2:36 PM, Lado Wali wrote:

Thank you Steve, but I do not either possess any knowledge of xml. I do not know how to create the schema file.

 

Lado

 

 

On Tue, Jul 30, 2019 at 5:30 PM Steven Blank <sgblank@...> wrote:

Lado,

This is purely a guess, but it seems to me that you could specify the necessary formatting in an XSLT file, and pass the name of this file in the DataviewToXML() function's fifth argument.

Implementing this solution, if possible, obviously assumes a certain amount of prior knowledge of XML Transformations which I, unfortunately, do not possess, but the scant online help/reference sounds promising.

HTH.

Steve Blank

On 7/30/2019 2:03 PM, Lado Wali wrote:

Hi Group,

 

When I export data through DataviewToXML and open the .xls file in excel, the date columns shows in text format. Is there any way it can default to date format like red color below:

 

Thanks

Lado

 

image.png


Virus-free. www.avast.com


Andreas Sedlmeier
 

On Thu, Aug 1, 2019 at 02:15 PM, Lado Wali wrote:
Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.
Basically thats not even needed , writing a macro to fetch the latest data from XML into a Excel workbook - if an XML Map exists in that document.
You then can simply "refresh" the data and the XML from last import would either be replaced or Excel would append to it.
A "XML Map" is basically a pointer to the XML source + a pointrer to the XML Schema + mapping of XML elements to cells of a sheet
The complete process is described here pretty well: https://www.mrexcel.com/excel-tips/using-xml-in-excel/

Its a strange approach it however does look more complicated then it actually is and on the good side you have that you do not only preserve the data types (type information from xsd) but also any formatting you did in Excel (template)
When you did refresh the Excel data with XML data from last export, you just save the result with a different filename.

So a XSLX file basically becomes a "template" and its maintainable if you reduce the number of your export programs in your application to 1.
Why do you have that much when you export Dataview to XML ? Just overwrite DatavievToXml() with a global function and you have a single point where you can check if you have, for an XML->Excel Export, a schema and a template.
Besides this function can also be used to generate any other format you want to support. Like .pdf, .csv, ...


Andreas


Steven Blank
 

Brilliant!

Steve Blank

PS. An hour on this would make a nice presentation at the upcoming Brown Bag Conference in Montreal ... just saying. :)

On 8/1/2019 11:47 AM, Andreas Sedlmeier wrote:
On Thu, Aug 1, 2019 at 02:15 PM, Lado Wali wrote:
Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.
Basically thats not even needed , writing a macro to fetch the latest data from XML into a Excel workbook - if an XML Map exists in that document.
You then can simply "refresh" the data and the XML from last import would either be replaced or Excel would append to it.
A "XML Map" is basically a pointer to the XML source + a pointrer to the XML Schema + mapping of XML elements to cells of a sheet
The complete process is described here pretty well: https://www.mrexcel.com/excel-tips/using-xml-in-excel/

Its a strange approach it however does look more complicated then it actually is and on the good side you have that you do not only preserve the data types (type information from xsd) but also any formatting you did in Excel (template)
When you did refresh the Excel data with XML data from last export, you just save the result with a different filename.

So a XSLX file basically becomes a "template" and its maintainable if you reduce the number of your export programs in your application to 1.
Why do you have that much when you export Dataview to XML ? Just overwrite DatavievToXml() with a global function and you have a single point where you can check if you have, for an XML->Excel Export, a schema and a template.
Besides this function can also be used to generate any other format you want to support. Like .pdf, .csv, ...


Andreas


Lado Wali
 

Andreas,
Very interesting. I have to spend more time to understand this.

Thanks
Lado


On Thu, Aug 1, 2019, 3:23 PM Steven Blank <sgblank@...> wrote:

Brilliant!

Steve Blank

PS. An hour on this would make a nice presentation at the upcoming Brown Bag Conference in Montreal ... just saying. :)

On 8/1/2019 11:47 AM, Andreas Sedlmeier wrote:
On Thu, Aug 1, 2019 at 02:15 PM, Lado Wali wrote:
Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.
Basically thats not even needed , writing a macro to fetch the latest data from XML into a Excel workbook - if an XML Map exists in that document.
You then can simply "refresh" the data and the XML from last import would either be replaced or Excel would append to it.
A "XML Map" is basically a pointer to the XML source + a pointrer to the XML Schema + mapping of XML elements to cells of a sheet
The complete process is described here pretty well: https://www.mrexcel.com/excel-tips/using-xml-in-excel/

Its a strange approach it however does look more complicated then it actually is and on the good side you have that you do not only preserve the data types (type information from xsd) but also any formatting you did in Excel (template)
When you did refresh the Excel data with XML data from last export, you just save the result with a different filename.

So a XSLX file basically becomes a "template" and its maintainable if you reduce the number of your export programs in your application to 1.
Why do you have that much when you export Dataview to XML ? Just overwrite DatavievToXml() with a global function and you have a single point where you can check if you have, for an XML->Excel Export, a schema and a template.
Besides this function can also be used to generate any other format you want to support. Like .pdf, .csv, ...


Andreas


Andreas Sedlmeier
 

Well, would be nice to see some of the people of this list like you in person again, Steve, Going to Canada for an "Magic & Excel" Demo is however a bit ... much ^^
I can offer to add this demo to XpaDemo project and you could demo it in your session, I could assist via websession or whatever.

Actually I was thinking about going to brown bag for a "Magic DevOps / Kubernetes" demo, also showing how to deal with all those TLS issues from here by using smart proxies. That however would require me to have a Magic & Linux eval and that I cannot get. At the end im kind of a "persona non grata" in MSE world ^^

Anyways. Contact me privately if you want to demo some of that stuff and I will help you with it.

Best regards,

Andreas

Brilliant!

Steve Blank

PS. An hour on this would make a nice presentation at the upcoming Brown Bag Conference in Montreal ... just saying. :)

Hide quoted text
On 8/1/2019 11:47 AM, Andreas Sedlmeier wrote:
On Thu, Aug 1, 2019 at 02:15 PM, Lado Wali wrote:
Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.
Basically thats not even needed , writing a macro to fetch the latest data from XML into a Excel workbook - if an XML Map exists in that document.
You then can simply "refresh" the data and the XML from last import would either be replaced or Excel would append to it.
A "XML Map" is basically a pointer to the XML source + a pointrer to the XML Schema + mapping of XML elements to cells of a sheet
The complete process is described here pretty well: https://www.mrexcel.com/excel-tips/using-xml-in-excel/

Its a strange approach it however does look more complicated then it actually is and on the good side you have that you do not only preserve the data types (type information from xsd) but also any formatting you did in Excel (template)
When you did refresh the Excel data with XML data from last export, you just save the result with a different filename.

So a XSLX file basically becomes a "template" and its maintainable if you reduce the number of your export programs in your application to 1.
Why do you have that much when you export Dataview to XML ? Just overwrite DatavievToXml() with a global function and you have a single point where you can check if you have, for an XML->Excel Export, a schema and a template.
Besides this function can also be used to generate any other format you want to support. Like .pdf, .csv, ...


Andreas


Lado Wali
 

Thank you so much Andreas. I am travelling this week but will be in touch when time permits.

Thanks again
Lado


On Thu, Aug 8, 2019, 4:57 AM Andreas Sedlmeier <sedlmeier@...> wrote:
Well, would be nice to see some of the people of this list like you in person again, Steve, Going to Canada for an "Magic & Excel" Demo is however a bit ... much ^^
I can offer to add this demo to XpaDemo project and you could demo it in your session, I could assist via websession or whatever.

Actually I was thinking about going to brown bag for a "Magic DevOps / Kubernetes" demo, also showing how to deal with all those TLS issues from here by using smart proxies. That however would require me to have a Magic & Linux eval and that I cannot get. At the end im kind of a "persona non grata" in MSE world ^^

Anyways. Contact me privately if you want to demo some of that stuff and I will help you with it.

Best regards,

Andreas

Brilliant!

Steve Blank

PS. An hour on this would make a nice presentation at the upcoming Brown Bag Conference in Montreal ... just saying. :)

Hide quoted text
On 8/1/2019 11:47 AM, Andreas Sedlmeier wrote:
On Thu, Aug 1, 2019 at 02:15 PM, Lado Wali wrote:
Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.
Basically thats not even needed , writing a macro to fetch the latest data from XML into a Excel workbook - if an XML Map exists in that document.
You then can simply "refresh" the data and the XML from last import would either be replaced or Excel would append to it.
A "XML Map" is basically a pointer to the XML source + a pointrer to the XML Schema + mapping of XML elements to cells of a sheet
The complete process is described here pretty well: https://www.mrexcel.com/excel-tips/using-xml-in-excel/

Its a strange approach it however does look more complicated then it actually is and on the good side you have that you do not only preserve the data types (type information from xsd) but also any formatting you did in Excel (template)
When you did refresh the Excel data with XML data from last export, you just save the result with a different filename.

So a XSLX file basically becomes a "template" and its maintainable if you reduce the number of your export programs in your application to 1.
Why do you have that much when you export Dataview to XML ? Just overwrite DatavievToXml() with a global function and you have a single point where you can check if you have, for an XML->Excel Export, a schema and a template.
Besides this function can also be used to generate any other format you want to support. Like .pdf, .csv, ...


Andreas


Craig Martin
 

Andreas wrote:
>> 
Actually I was thinking about going to brown bag for a "Magic DevOps / Kubernetes" demo
>>

So are you going to quit teasing and do this?
See you there? If need be, I'll start a gofundme page to get you a damn Linux license!

Craig


From: main@magicu-l.groups.io <main@magicu-l.groups.io> on behalf of Andreas Sedlmeier <sedlmeier@...>
Sent: Thursday, August 8, 2019 1:57 AM
To: main@magicu-l.groups.io <main@magicu-l.groups.io>
Subject: Re: [magicu-l] uniPaaS 1.9d - Export through DataviewToXML
 
Well, would be nice to see some of the people of this list like you in person again, Steve, Going to Canada for an "Magic & Excel" Demo is however a bit ... much ^^
I can offer to add this demo to XpaDemo project and you could demo it in your session, I could assist via websession or whatever.

Actually I was thinking about going to brown bag for a "Magic DevOps / Kubernetes" demo, also showing how to deal with all those TLS issues from here by using smart proxies. That however would require me to have a Magic & Linux eval and that I cannot get. At the end im kind of a "persona non grata" in MSE world ^^

Anyways. Contact me privately if you want to demo some of that stuff and I will help you with it.

Best regards,

Andreas

Brilliant!

Steve Blank

PS. An hour on this would make a nice presentation at the upcoming Brown Bag Conference in Montreal ... just saying. :)

Hide quoted text
On 8/1/2019 11:47 AM, Andreas Sedlmeier wrote:
On Thu, Aug 1, 2019 at 02:15 PM, Lado Wali wrote:
Hi Keith,
This would work if you have only few places like this in your application otherwise it would be too much work to maintain the templates. I have export programs all over in my application.
Basically thats not even needed , writing a macro to fetch the latest data from XML into a Excel workbook - if an XML Map exists in that document.
You then can simply "refresh" the data and the XML from last import would either be replaced or Excel would append to it.
A "XML Map" is basically a pointer to the XML source + a pointrer to the XML Schema + mapping of XML elements to cells of a sheet
The complete process is described here pretty well: https://www.mrexcel.com/excel-tips/using-xml-in-excel/

Its a strange approach it however does look more complicated then it actually is and on the good side you have that you do not only preserve the data types (type information from xsd) but also any formatting you did in Excel (template)
When you did refresh the Excel data with XML data from last export, you just save the result with a different filename.

So a XSLX file basically becomes a "template" and its maintainable if you reduce the number of your export programs in your application to 1.
Why do you have that much when you export Dataview to XML ? Just overwrite DatavievToXml() with a global function and you have a single point where you can check if you have, for an XML->Excel Export, a schema and a template.
Besides this function can also be used to generate any other format you want to support. Like .pdf, .csv, ...


Andreas