on 07-01-2016 10:14 AM
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.
Are you have a 1:N Multimapping or a Multiple Interfaces?
Regards
Bhavesh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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.
Regards
A nupam
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.