cancel
Showing results for 
Search instead for 
Did you mean: 

Generating multiple dynamic filenames and output excel file

Former Member
0 Kudos

Hello,

I have an IDoc to csv and xsl file requirement.

Three types of target files are to be created: csv, two different xsl files with different structure.

The file name is to be created dynamically, with timestamp and a company code field value. This company code is coming in the IDoc.

The number of folders are to be created as per the countries, and we have 10 countries. One country can have multiple company codes and files have to be written with their company codes in respective country folder.

For 8 countries the expected target file type is .csv and for remaining two, excel structures have to be created.

I am not sure of how to generate multiple dynamic file names and should I go for java mapping or multiple Graphical mappings!

Please suggest!

Regards,

Indu.

Accepted Solutions (1)

Accepted Solutions (1)

bhavesh_kantilal
Active Contributor
0 Kudos

Are you have a 1:N Multimapping or a Multiple Interfaces?

  • For the different naming convention that is required for the directory and filename, you would need to have multiple inbound Interfaces corresponding to each file type and then set filename and directory using ASMA Parameters.
  • You can use this Blog Series from Eng Swee to understand how to convert Excel to XML and vie versa. The module is also provided very generously by Eng Swee..

Regards

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

You might also want to look at this blog in case you want to use MultiMapping with Content Conversion..

Regards

Bhavesh

Former Member
0 Kudos

Thank You Bhavesh for your reply.

I took a little while to figure out certain intricacies in my requirement.

I have worked out with process teams to send the data (IDocs) in sorted manner, hence the dynamic configuration for multiple company codes and respective country specific directories has been implemented.

The challenge is now the excel file creation from XML. For the next two structure, I am planning to create two Message mappings to create the target XML structures and later convert them to excel.

Thanks for sharing

Former Member
0 Kudos

This is in continuation of the last message.
Thanks for sharing Eng Swee Yeoh's link.

The excel file which needs to be created for X country has two column headers; a raw structure for reference:

Excel columns          A          B          C                    D          E          F          G          H

Header1                     EMP                                        Det                    xyz                    country

Header2                     Nm     FrstNm     ScnNm          ID         Amt     Price     Date     curr

Data row1

Data row2

.... and so on.

Can you Advise further on it?

Thanks,

Indu Khurana.

Answers (3)

Answers (3)

apu_das2
Active Contributor
0 Kudos

Hi,

You can develop this using three separate inbound SIs as per your requirement. Generating CSV file should not be an issue for you I think. For creating excel files (if your excel is a regular simple excel file without any complex structure),you can handle this using simple xslt mapping. I you need to put the file location dynamically then I guess you can do that using UDF and accessing ASMA parameter there.

An example for your excel creation xslt mapping. You can change for your requirement accordingly. If you find it difficult to map directly from IDOC structure , then use an intermediate structure to simplify IDOC data and from the intermediate structure use xslt mapping finally to create excel.

Input file -

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_TEST_IN xmlns:ns0="urn://MM_Test_Sender">

   <RECORD>

      <NAME>awd</NAME>

      <AGE>21</AGE>

      <ADDRESS>K</ADDRESS>

   </RECORD>

   <RECORD>

      <NAME>werwer</NAME>

      <AGE>65</AGE>

      <ADDRESS>A</ADDRESS>

   </RECORD>

   <RECORD>

      <NAME>345345</NAME>

      <AGE>45</AGE>

      <ADDRESS>H</ADDRESS>

   </RECORD>

   <RECORD>

      <NAME>hfgh</NAME>

      <AGE>67</AGE>

      <ADDRESS/>

   </RECORD>

</ns0:MT_TEST_IN>

XSLT mapping-

<?xml version="1.0" encoding="utf-8"?>

<?mso-application progid="Excel.Sheet"?>

<xsl:stylesheet version="1.0"

xmlns:html="http://www.w3.org/TR/REC-html40"

    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    xmlns="urn:schemas-microsoft-com:office:spreadsheet"

    xmlns:o="urn:schemas-microsoft-com:office:office"

    xmlns:x="urn:schemas-microsoft-com:office:excel"

    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    <xsl:template match="/">

        <Workbook>

            <Styles>

                <Style ss:ID="Default" ss:Name="Normal">

                    <Alignment ss:Vertical="Bottom" />

                    <Borders />

                    <Font />

                    <Interior />

                    <NumberFormat />

                    <Protection />

                </Style>

                <Style ss:ID="s21">

                    <Font ss:Size="22" ss:Bold="1" />

                </Style>

                <Style ss:ID="s22">

                    <Font ss:Size="14" ss:Bold="1" />

                </Style>

                <Style ss:ID="s23">

                    <Font ss:Size="12" ss:Bold="1" />

                </Style>

                <Style ss:ID="s24">

                    <Font ss:Size="10" ss:Bold="1" />

                </Style>

            </Styles>

            <Worksheet ss:Name="Page1">

                <Table>

                    <xsl:call-template name="XMLToXSL" />

                </Table>

            </Worksheet>

        </Workbook>

    </xsl:template>

    <xsl:template name="XMLToXSL">

        <Row>

                        <Cell>

                            <Data ss:Type="String">Name</Data>

                        </Cell>

                        <Cell>

                            <Data ss:Type="String">Age</Data>

                        </Cell>

                        <Cell>

                            <Data ss:Type="String">Address</Data>

                        </Cell>

                        

                    </Row>

        <xsl:for-each

            select="//RECORD">

            <Row>

                <Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="NAME" />

                    </Data>

                </Cell>

                <Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="AGE" />

                    </Data>

                </Cell>

                <Cell>

                    <Data ss:Type="String">

                        <xsl:value-of select="ADDRESS" />

                    </Data>

                </Cell>

            </Row>

        </xsl:for-each>

    </xsl:template>

<xsl:template match="MT_TEST_IN">

</xsl:template>

</xsl:stylesheet>

Thanks,

Apu

Former Member
0 Kudos

Hi,

This is a sample excel file with two col headers and from row 3 data commences.

Thanks,

Indu.

ambrish_mishra
Active Contributor
0 Kudos

This is a Header and sub-header structure which will be trickier to implement.

For a start, you can look to implement something like

Per-M    Per-N     Per-P and so on....

this is flattened and should make sense.

Hope it helps!

Ambrish

engswee
Active Contributor
0 Kudos

Hi Indu

You can try using my XML to Excel converter but instead of constructing the header lines from configuration, you can construct two additional lines in the XML instead.

I.e. use addHeaderLine = none, and have your mapping generate two additional lines for the two headers, as shown below:-

Regards

Eng Swee

Former Member
0 Kudos

Hello Eng Swee,

Thanks for taking out time for providing the pictorial explanation.

But, I have created the structures like this already for the headers and details:

<Title> hardcoded

<Header>hardcoded

<Details>

The Records with headers and details are getting created just fine.

I have created file after FCC and named as " file.XLS" and the adapter is actually creating an excel file without any adapter module.

I'm curious and desperate to know, If we can achieve this excel creation just with FCC, why would we need an adapter module?

Please provide your comments.

Thanks again!

Regards,

Indu Khurana.

engswee
Active Contributor
0 Kudos

Hi Indu

Creating a file using standard FCC and just changing the extension name to .xls IS NOT creating an Excel formatted file. It is just a plain text file (CSV or tab delimited) that can also be opened by Excel.

A correctly formatted Excel file conforms to Microsoft's proprietary format and is either in binary format (.xls) or XML format (.xlsx). Standard FCC do not have the functionality to create it in this format.

You need to check if your requirements need it in Microsoft's format or just plain text will do.

If you want to understand more about Microsoft's format, I suggest you read up on it on the Internet.

Regards

Eng Swee

anupam_ghosh2
Active Contributor
0 Kudos

Hi Indu,

                You cannot format the excel to have proper width/height of the cells.

You cannot add color to header line, neither can you alter the font format.

All this is because you are simply using a FCC to create the csv/xls file.

The business has to click on each column/row in generated sheet to see the contents properly since each cell is not formatted.

Either you need a  java mapping or you need an adapter module to create the xls file. 

Please check this blog 

Regards

A nupam

ambrish_mishra
Active Contributor
0 Kudos

Hi Indu,

You need to break down the requirement. You need separate inbound interfaces and use Bhavesh's suggestion on creation of XML to excel. For dynamic names, you can check the below document as well just for understanding.

Ambrish

Former Member
0 Kudos

Link : http://scn.sap.com/docs/DOC-59281

Hi Ambrish,

Thank you for your reply and guidance which is always valuable.

I have broken down the requirement and need to understand the ear file ceated by Eng Swee Jeoh.