Export from DMN to spreadsheet and import from spreadsheet to DMN #1477

michaldytko · 2019-08-22T09:28:39Z

This popped up in support. I linked relevant support case in issue description

Is your feature request related to a problem? Please describe.

Our customers have clients that dont have the modeller and are not allowed to install it on their computer, but they also need to take part in developing the DMN.
Also to keep it less "technical" for clients. They are used to Excel, but not to DMN or XML.

Describe the solution you'd like

I would be nice to have a button in Modeler that allows to export DMN table to spreadsheet.
Additionally it would be nice to import data from spreadsheets into Modeler.

Describe alternatives you've considered

A clear and concise description of any alternative solutions or features you've considered.

Additional context

I know that there is a similar issue which should enable copy and paste from and into DMN.

Related to SUPPORT-6243

Related to #1341

nikku · 2019-08-22T11:50:16Z

Thanks for reporting.

akzincsystems · 2021-01-21T16:31:19Z

I can't believe that 18 months later, Camunda Modeller doesn't have the ability to import/export DMNs from/to common spreadsheet formats, or even just simply a csv straight out of the box. There's even a plugin to get spreadsheet into Modeller but not the other way around.

Spreadsheets are what business people understand. Asking them to install a piece of software just so they can look at something that looks and feels like a spreadsheet to them is untenable and is undermining mine, and no doubt others, ability to get DMN into the business, let alone share information with them.

It's such a shame that what should have been a relatively simple sell has become such as hassle.

nikku · 2021-01-21T17:18:26Z

Thanks four your feedback.

A DMN table, while it looks like an Excel spreadsheet, is not a simple spreadsheet after all. It contains numerous meta-data that will be lost once you convert the file to Excel. A roundtrip, i.e. Modeler -> Excel -> Modeler is hardly possible without loosing large parts of the hidden configuration that is contained within a DMN table.

Maybe you could provide an example to elaborate how a usable export would look like?

akzincsystems · 2021-01-22T06:48:54Z

@nikku Hi, thanks for responding. I understand the technical complexity, The use case isn't to get a perfect translation from DMN -> spreadsheet -> DMN. That indeed would be difficult to achieve.

The use case is about sharing information with business colleagues. They don't care, and probably shouldn't according to "Real Life BPMN", about what is under the covers.

Use case 1: Business expressed decision table.
My business colleague expresses a decision table in a spreadsheet. We discuss it and agree that it is a good foundation. I want to import that as a basis for a DMN. i.e. just like the existing plugin. I make some modifications to clean up things, create the yaml file to define input and output columns and do the import.

Use case 2: It need to share a DMN to a business audience.
Having now used my colleagues spreadsheet as a basis, I have tuned it for production, worked through some missing scenarios etc and put it through some tests in the Camunda engine. I need to share it back to my colleague(s), none of whom have the modeller installed. I export it to a spreadsheet, export the DRD to a png file (already available) and distribute those. They are only concerned with the decision content, not the meta data, and so a spreadsheet display analogous to what is in Modeller is sufficient. There are plenty of images available via your favourite search engine to illustrate this as other products are doing it. MVP doesn't worry about making it pretty as that can be done by the user. "Templates" can be a later feature. Exporting all decision tables as tabs in the same spreadsheet can be a later feature.

Does that help to clarify the requirement?

Regards
A

p.s. A simple XSLT will convert DMN XML to XLSX (spreadshseet) or CSV format

nikku · 2021-01-22T08:40:34Z

Thanks for following up on this @akzincsystems.

I think we're getting closer to something tangible here.

Given an example decision, i.e. this one and a contained decision table (i.e. ➡️ Season decision), how would the Excel spreadsheet look like that your colleagues find helpful enough? What information is contained and what is left out?

akzincsystems · 2021-01-22T09:30:50Z

@nikku I took the Dish Decision table

Dish Decision DMN.xlsx

Obviously this could also be a simple CSV file that most spreadsheet systems (MS, LibreOffice, Google etc) will simply import. This system won't allow csv files so I haven't attached it.

The example has no layout.

Addendum: CSV format

Decision,Dish Decision,,,
,,,,
U,When,And,Then,Annotation
,Season,How many Guests,Dish,
,(string),(integer),(string),
1,Winter,<=8,Spareribs,
2,Winter,>8,Pasta,
3,Summer,>10,Light salad,
4,Summer,<=10,Beans salad,
5,Spring,<10,Stew,
6,Spring,>=10,Steak,

nikku · 2021-01-22T09:47:42Z

Thanks for that example output.

nikku · 2021-01-22T09:49:38Z

For reference, a screenshot of the CSV imported:

akzincsystems · 2021-01-22T20:27:10Z

ok. Not pretty, not complete. 1.5hrs of xslt programming dragged up from memory. But it's the POC that it will work.

<?xml version="1.0"?>
<xsl:stylesheet
        version="2.0"
        xmlns="http://www.w3.org/1999/XSL/Transform"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xpath-default-namespace="https://www.omg.org/spec/DMN/20191111/MODEL/">
    <!-- 
        Convert DMN XML to CSV format for Spreadsheet ingestion 
        @author Ashley Kitson
    -->
    <xsl:output method="text" encoding="UTF-8"/>
    <xsl:strip-space elements="*"/>

    <!-- Input parameters -->
    <xsl:param name="decisionId" required="yes"/>
    
    <!-- Select the decision table nodes -->
    <xsl:template match="/">
        <xsl:apply-templates select="definitions/decision"/>
    </xsl:template>
    
    <!-- Output for the required decisionId -->
    <xsl:template match="definitions/decision">
        <xsl:for-each select=".">
            <xsl:choose>
                <xsl:when test="@id=$decisionId">
                    <!-- Decision name -->
                    <xsl:text>Decision,</xsl:text><xsl:value-of select="./attribute::name"/>
                    <xsl:text>&#xa;</xsl:text>
                 
                    <xsl:apply-templates select="decisionTable"/>
                </xsl:when>
            </xsl:choose>
        </xsl:for-each>
    </xsl:template>
    
    <!-- Headings -->
    <xsl:template match="decisionTable">
        <xsl:value-of select="substring(@hitPolicy, 1, 1)"/><xsl:text>,</xsl:text>
        <xsl:for-each select="input">
            <xsl:text>Input,</xsl:text>
        </xsl:for-each>
        <xsl:for-each select="output">
            <xsl:text>Output</xsl:text>
            <xsl:text>,</xsl:text>
        </xsl:for-each>
        <xsl:text>Annotations</xsl:text>
        <xsl:text>&#xa;</xsl:text>

        <xsl:text>,</xsl:text>
        <xsl:for-each select="input/@label">
            <xsl:value-of select="."/><xsl:text>,</xsl:text>
        </xsl:for-each>
        <xsl:for-each select="output/@label">
            <xsl:value-of select="."/>
            <xsl:text>,</xsl:text>
        </xsl:for-each>
        <xsl:text>&#xa;</xsl:text>

        <xsl:text>,</xsl:text>
        <xsl:for-each select="input/@label">
            <xsl:text>(</xsl:text><xsl:value-of select="./parent::node()/inputExpression/@typeRef"/><xsl:text>),</xsl:text>
        </xsl:for-each>
        <xsl:for-each select="output/@typeRef">
            <xsl:text>(</xsl:text><xsl:value-of select="."/><xsl:text>)</xsl:text>
            <xsl:text>,</xsl:text>
        </xsl:for-each>
        <xsl:text>&#xa;</xsl:text>
        
        <xsl:apply-templates select="rule"/>
    </xsl:template>

    <!-- Rules -->
    <xsl:template match="rule">
        <!-- Rule number -->
        <xsl:value-of select="position()"/><xsl:text>,</xsl:text>
        
        <!-- Inputs -->
        <xsl:for-each select="inputEntry/text">
            <xsl:choose>
                <xsl:when test="contains(., ',')">
                    <xsl:value-of select="concat('&quot;', replace(., '&quot;', ''), '&quot;')"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="replace(., '&quot;', '')"/>
                </xsl:otherwise>
            </xsl:choose>
            <xsl:text>,</xsl:text>
        </xsl:for-each>
        
        <!-- Outputs -->
        <xsl:for-each select="outputEntry/text">
            <xsl:choose>
                <xsl:when test="contains(., ',')">
                    <xsl:value-of select="concat('&quot;', replace(., '&quot;', ''), '&quot;')"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="replace(., '&quot;', '')"/>
                </xsl:otherwise>
            </xsl:choose>
            <xsl:text>,</xsl:text>
        </xsl:for-each>
        
        <!-- Annotations -->
        <xsl:choose>
            <xsl:when test="description">
                <xsl:value-of select="replace(description, ',', '')"/>
            </xsl:when>
        </xsl:choose>
        <xsl:text>&#xa;</xsl:text>
    </xsl:template>
    
</xsl:stylesheet>```

And although the output is not a "complete" csv, it will load the result into open office at least.  So, tart this up and you have the basis of your exporter.

akzincsystems · 2021-01-24T10:55:24Z

Further to the above. If you need to create CSV files for import to a spreadsheet until such time as Modeller gets the functionality, do the following (Linux):

Install Java (is probably already installed)
Install Saxon HE. https://www.saxonica.com/download/java.xml. Unzip to a directory (perhaps in your home directory)
Copy and paste the above xslt to a convenient file location
mkdir ~/bin if not already present
Make sure ~/bin is in your path (see ~/.profile or similar)
Create ~/bin/xslt-transform (e.g. nano ~/bin/xslt-transform) and add the following

 #!/bin/bash

java -cp ~/SaxonHE/saxon-he-10.3.jar net.sf.saxon.Transform "$@"

change the path to the jar file according to where you have put it
chmod -u+x ~/bin/xslt-transform

You should now be able to run something like:
xslt-transform /path/to/dmn-xml-file /path/to/conversion.xslt decisionId=your_decision_table_id > /path/to/output.csv

grexe · 2021-05-10T08:23:56Z

I am in the same situation as the reporter and just need to share a read-only spreadsheet version of my DMN for documentation purposes, without any implementation/technical details.
Reading through the thread here, it looks like a solution was almost complete, but with latest version 4.7.0 on MacOS X, there is still no export functionality at all.

On a side note, exporting metadata shown in the screenshot of comment #1477 (comment) could be realised rather simple by adding more header lines, one for each metadata info, and (for XSLX) hiding or protecting these columns so business people can not break the DMN, but only edit descriptive content like human-readable input/output names, e.g.:

InputColumn1, InputColum2,…
meta:type=String, meta:type=Integer,…
meta:internalName=my.input.col1, meta:internalName=my.input.col2
…

rob2universe · 2021-05-10T09:26:00Z

The import side is covered by the plugin and performed by the developer. Camunda's tool for Business-IT collaboration is not the locally installed modeler, but http://cawemo.com. The rules, just imported into DMN or already a refined DMN for production, can be uploaded to Cawemo. If no full roundtrip (information loss in spreadsheet) is intended, but only the new refined version of the DMN table should be shared, then Cawemo does that perfectly well and does not required a locally installed software.
Even with a free account Business and IT can collaboratively work on the DMN in their browser, including comments. No local modeler is required for business, no information loss if business wants to make changes. Users who require read-only access do not event require a Cawemo login. The share link function gives access to anyone via their browser. The embedded version of the link can also be used to include an always up-to-date DMN in process documentation (wiki, sharepoint, etc)
See example here:
https://cawemo.com/share/f26a5108-58c4-4f64-8734-90bda0e87790

So, now that business can look at the latest DMN version without modeler, access DRD, and can even make changes in the Browser (working roundtrip without information loss) , is there still a need to export a DMN back into the less suitable spreadsheet format?

grexe · 2021-05-10T09:53:03Z

While I appreciate the free online version, there is still a need for a private locally installed version for a lot of use cases, e.g. business confidential information that we are not allowed to share in an external cloud service, for example in banks or other financial instituations, which are audited in our case by the Austrian financial controlling authority.
So yes, please add official and built-in support for import/export to the Modeler, too.

rob2universe · 2021-05-10T10:00:29Z

For completeness: Cawemo Enterprise is available aaS on a dedicated environment. Installation on premises / own infrastructure is also supported.
https://docs.camunda.org/cawemo/latest/technical-guide/installation/

grexe · 2021-05-10T11:11:19Z

For completeness: Cawemo Enterprise is available aaS on a dedicated environment. Installation on premises / own infrastructure is also supported.
https://docs.camunda.org/cawemo/latest/technical-guide/installation/

this would of course mean leaving the open source version, but I understand the reasoning:)

eflaco · 2022-07-21T14:19:58Z

I believe that this
https://github.com/pinussilvestrus/camunda-modeler-excel-import-plugin
should be the solution to the discussion here. Easy installation, both ways working. Please note that you have to work with Camunda Modeler version 5 or higher, and the import module has some minor issues regarding complex expressions. Works fine for use cases described by @akzincsystems

michaldytko added the channel:support label Aug 22, 2019

nikku added DMN enhancement New feature or request export labels Aug 22, 2019

nikku added the backlog Queued in backlog label Aug 26, 2019 — with bpmn-io-tasks

nikku mentioned this issue Feb 7, 2023

Export DMN Decision Table to Image File (JPG, PNG, SVG) #3410

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Export from DMN to spreadsheet and import from spreadsheet to DMN #1477

Export from DMN to spreadsheet and import from spreadsheet to DMN #1477

michaldytko commented Aug 22, 2019 •

edited by nikku

nikku commented Aug 22, 2019

akzincsystems commented Jan 21, 2021

nikku commented Jan 21, 2021

akzincsystems commented Jan 22, 2021 •

edited

nikku commented Jan 22, 2021 •

edited

akzincsystems commented Jan 22, 2021 •

edited

nikku commented Jan 22, 2021

nikku commented Jan 22, 2021

akzincsystems commented Jan 22, 2021 •

edited

akzincsystems commented Jan 24, 2021 •

edited

grexe commented May 10, 2021

rob2universe commented May 10, 2021 •

edited

grexe commented May 10, 2021 •

edited

rob2universe commented May 10, 2021

grexe commented May 10, 2021

eflaco commented Jul 21, 2022

Export from DMN to spreadsheet and import from spreadsheet to DMN #1477

Export from DMN to spreadsheet and import from spreadsheet to DMN #1477

Comments

michaldytko commented Aug 22, 2019 • edited by nikku

nikku commented Aug 22, 2019

akzincsystems commented Jan 21, 2021

nikku commented Jan 21, 2021

akzincsystems commented Jan 22, 2021 • edited

nikku commented Jan 22, 2021 • edited

akzincsystems commented Jan 22, 2021 • edited

nikku commented Jan 22, 2021

nikku commented Jan 22, 2021

akzincsystems commented Jan 22, 2021 • edited

akzincsystems commented Jan 24, 2021 • edited

grexe commented May 10, 2021

rob2universe commented May 10, 2021 • edited

grexe commented May 10, 2021 • edited

rob2universe commented May 10, 2021

grexe commented May 10, 2021

eflaco commented Jul 21, 2022

michaldytko commented Aug 22, 2019 •

edited by nikku

akzincsystems commented Jan 22, 2021 •

edited

nikku commented Jan 22, 2021 •

edited

akzincsystems commented Jan 22, 2021 •

edited

akzincsystems commented Jan 22, 2021 •

edited

akzincsystems commented Jan 24, 2021 •

edited

rob2universe commented May 10, 2021 •

edited

grexe commented May 10, 2021 •

edited