Skip to Content

The goal of this blog post is to detail how you can customize a standard Import Excel file from Cloud for Customer using the example of Marketing Leads.

A lot of the explanations are inspired from an old document Support referred to when I asked for some information. It was slightly different as it was meant for ByDesign but still relevant.

Also I did not see anything in the Help Guide. There are some steps detailed to replace the Templates themselves but not to adapt them with your own fields.

Before we start, you will need to have the C4C add-in installed and enabled of course as import templates need it. You can get it by heading to the Download section of your solution.

Since the Excel file is using an XML structure for the import templates generated by C4C, you will need to add the “Developer” tab in your ribbon. To do so just follow these quick steps: http://office.microsoft.com/en-001/excel-help/show-the-developer-tab-HA101819080.aspx

You will also need to have a Zip Tool like 7-zip, WinRar, WinZip… your preferred one. We will “unpack” the XLSX file retrieved from C4C and then we will need to repack it. It works exactly like a ZIP file, no surprises.

Finally, you will need to be able to edit an XML file. I personally am using Notepad++, but any editor will do of course even the classic Notepad.

Summary of steps to perform

  1. Download the Import Template from the relevant Work Center directly and unzip it;
  2. Find out which A2X Web Service is the Import Template using;
  3. Add the required extension fields in the A2X Web Service;
  4. Retrieve the Technical Field Name and the Namespace for these fields (you can get these in the A2X WSDL file);
  5. Edit the /xl/xmlMaps.xml file to add the fields in the XML structure;
  6. Repack the XLSX and open the file in Excel;
  7. Edit your import template using the new fields available in the XML structure;
  8. (Optional) Update the import template in C4C (Application and User Management > Business Flexibility > Microsoft Office Template Maintenance) – This is not covered in this blog as there already is a good guide in the Help Center.

Download the original Import Template


It is quite straightforward, just make sure that in your Scoping you have activated the feature, and then head to the Work Center for which you want to retrieve the import template and check the cogwheel menu for “Import from Microsoft Excel”.

MLeads-Export.PNG

Once you have downloaded your file, just right click on it and try unzipping it with your favorite tool:

MLeads-XLSX-Zip.PNG

Once it is unzipped, check the folder “xl” for the file “xmlMaps.xml”. This file will be very import for the next steps, you can open it using Notepad++ for instance or your favorite XML/Text Editor.

MLeads-XML.PNG

This file contains the XML structure used by Excel and the SAP C4C AddIn to build your request to the server and perform the import task. It is calling the A2X Web Service to do that and you will find traces of it in “xmlMaps.xml”.

For instance, look for “OfficeIntegrationConfiguration” without quotes. It will show you the few steps in the XML used to determine the web service, like “ManageMarketingLeadIn” in this case. You can get yours by checking what is in the <Name> tag.

XML-A2X.PNG

So no we already have 2 important pieces: the “xmlMaps.xml” file and the A2X Web Service.

Add the required extension fields in the A2X Web Service


You would perform this step as you would usually do for any A2X Web Service if you are familiar with this.

You just have to head to the Work Center (Marketing Leads for me here), switch to “Adaptation Mode” and then “Edit Screen”. This step can also be performed in the HTML5 client.

Open your “Extension Fields” details as shown below and highlight the field you want to add in your Import Template and click on “Further Usage”.

Adapt-FurtherUsage.PNG

In there, you will head to the tab “Services” to add the field to the A2X Web Service. Select the proper one in the list and click on “Add Field”, it is saved automatically. You will want to save 2 other information: the namespace as shown in the small header above the table “http://sap.com/xi/AP/CustomerExtension/BYD/A0452” and the Field Name generated for the Web Service as shown in the table “DetailSpecsRequired” for instance here. These will be important when updating the XML structure. Please note that the namespace will most probably be the same for all your extension fields.

Adapt-Services.PNG

If you are looking for adding standard fields, you will need to download the A2X Web Service WSDL first and check in it if the fields are available. If they are you can simply go ahead and check their technical names in the WSDL file. If you need some guidance for this, don’t hesitate to post in the comments and I can share more details.

MLeads-WSDL.PNG

Edit the xmlMaps.xml structure file


This part can be tedious depending on how familiar you are with XML structure. If this is one of the first times for your using XML files, try not to be intimidated by the size of the file and we are just going to perform minimal changes. Again my steps here will describe what I did for the Marketing Leads, if you are customizing another file and need some help do not hesitate to post in the comments section.

The first thing that we will want to do is to add the field in the structure itself. Search for the name of the A2X Web Service and the first result should give you a similar view. You should see the list of available fields in the XML structure just below. You can read different <xs:element> tags with names like UUID, ID, Name, QualificationLevelCode etc.

There are different levels as you can see below. The metadata level will be used by the AddIn to put in the Update Date and Time and the Status received when performing the action. You will generally focus on the level just below named “MarketingLead” here which will next the other header elements like Name, Firstname, Lastname, Company etc. In my example we are adding a field at this level.

As a side note, the property minOccurs will define if this is a mandatory field or not for the structure. If you put it to 1, you will have to have an entry in it in your Excel file when running the import. The other property maxOccurs should be set to 1 as well as shown below, only in very rare and complex occasions you would want multiple entries which I will not describe here.

XML-Services.PNG

We are now going to add one line to reflect the Extension Field we added in the A2X Web Service and want to use in the import process.

Copy one of the <xs:element> lines, for instance the one with the name “CompanyName”. You will want to perform 3 changes on this one: “minOccurs” should be set to 0 as the extension field is not mandatory, “name” should be set to “DetailSpecsRequired” to reflect the name of the extension field and the type should be set to “tns:Indicator”.

XML-Extension.PNG

Why Indicator? This field was created as a Checkbox which is also known as Indicator. It means that in the Excel file it will have to be filled with “TRUE” or “FALSE” (without quotes).
Another type could be “xs:String” which will actually work for most of your fields, like dropdowns or even indicators. The only problem here is that you have to make sure that you are filling the cell in Excel during the import with a data that C4C can read for your extension field. For instance, in our case here, the extension field is an indicator. If I set it to a String type, and put “TRUE” as the value it would fail. I would need to use 0 or 1 as a value instead.
Bottom line, your mileage may vary and you are more than encouraged to test things out here. I haven’t found any official documentation but maybe someone found one to complete the part about element types here.

We have now to update a second part of the XML structure in order to make the field fully work. Look for “MaintainBundle” and you should fall in the same area as you did when you were looking for the A2X Web Service name earlier. Below that, you will find a tag called “Transformations” which will process the information from Excel to format the Web Service Query and send the data to your C4C tenant. We will want to add a simple piece of code for each field that we created. There can be some logic implied in here but I will not cover that simply because I don’t know anything about that, again I haven’t found any documentation so I’m happy to point to any links you might have found and that would be relevant here or include your comments.

XML-MaintainBunder.PNG

Just above:

XML-BundleAbove.PNG

We will add a simple piece:

XML-NewTpl.png

Copy/Paste friendly:

<xsl:template match=”DetailSpecsRequired”>
<xsl:element namespace=”http://sap.com/xi/AP/CustomerExtension/BYD/A0452” name=”DetailSpecsRequired”>
  <xsl:value-of select=”.”/>
</xsl:element>
</xsl:template>

This code will basically just tell the file to look for the field “DetailSpecsRequired” in the namespace stated. You will have to edit this code with your field name and your namespace.

Save your XML and done.

Repack the XLSX


Easy step so you can go grab a coffee or a tea real quick. Just head back to the root of your initial extract, select all your files and zip them all.

XLSX-Zip.PNG

You will have a new ZIP file created, just change the extension to XLSX and open it in Excel!

Edit the Excel template with your new fields


Now that your file is in Excel, open it and head to the “Developer” tab in the Ribbon. Click on “Source”, a right panel will appear with the XML Structure, you can check that your field has been added properly by checking the SAP_BYD_MAIN mapping from the dropdown.

Excel-Source.PNG

If you have an error when trying to display the SAP_BYD_MAIN mapping you might want to recheck your XML structure file and make sure all the field names are properly etc. If nothing worked maybe share your issues to find out what could be the problem.

If everything is good we will now want to add the Field to the Table in Excel so you can add data to it. For this you will first create a new column in the worksheet wherever you want in the table (not at the end as there are some hidden columns used for technical stuff), in the example above I’ve created it as the column F.

Name it the way you want so your users know what this is about, and then simply take the field from the right and drag and drop it on the column header. If everything is fine whenever you select a field name that is in bold on the right it means that it has been mapped in the Excel and will highlight where the mapped data is. Check that it is the case with your new field.

Excel-Mapped.PNG

And you are done! After that, just save your new XLSX, give it a test of course by using the AddIn as usual (Log On then Save Data after adding information in the table) and check that your fields are correctly populated.

As mentioned at the beginning, I will not explain how to update the template so that this is this file that is being downloaded when your users are getting it from C4C. There already is a full documentation on this, simply search for “Maintain Microsoft Excel Templates” in the Help Center and you will find it.

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  1. Charan Sai Kattekola

    Hi Jean,

    We have a requirement of adding a list type to the excel through XML mappings.

    Can you please suggest for adding a field of list type in excel.

    It could be very helpful for us

    (0) 
  2. Hossain Michael

    Dear,

    i would need to have some standard fields available for importing leads using an excel template.

    How can i achieve this?

    Thanks,

    Regards

    (0) 
  3. Thierry CRIFASI

    Could you clarify your Step 1 statement “It is quite straightforward, just make sure that in your Scoping you have activated the feature” ?

    As an example I want to be able to upload Installation points into an Installed base. I can’t quite figure out where in Scoping I can activate the ability to Import an Excel file.

    Are you saying it should be possible to import via Excel any standard business object? How about customer business objects?

    (0) 
  4. Bahjat Saliba

    Could you please provide a link to the documentation for changing/replacing the Original Excel Template by the updated one?

    I search for it but got nothing!

    Thanks

    Bahjat

    (0) 
  5. Eloise Bruinink

    I actually work in ByDesign, and your explanation is geared towards C4C. You write:”A lot of the explanations are inspired from an old document Support referred to when I asked for some information. It was slightly different as it was meant for ByDesign but still relevant.”

    Would you be able to share this document with me?

    Eloise

    (0) 
    1. Bahjat Saliba

      Dear Eliose,

      Your Topic workd for C4C too. what I actually meant is the last paragraph in your Documentations.

      /wp-content/uploads/2016/03/20_03_912181.png

      I couldn’t find this documentation for making the updated Excel  as the Default one.

      do you have any idea about it or any Explanation how to achive it?

      thank you

      Bahjat

      (0) 
  6. Piyush Gandhi

    Hi

    Can you please guide us who did you download the “Import From Microsoft Excel” template in a ZIP file. When We do click on the Sprocket wheel we are only able to download .xls verison of the template.

    How do we get the XML file “xmlMaps.xml” file.

    Regards

    Thanks

    Piyush

    (0) 
      1. Roger Sainsbury

        It may be a bit late to help, but just for the record…

        The .xlsx file is itself a zip file. If you can’t unzip it directly, just rename the file from .xlsx to .zip. You will then be able to unzip it to access the xmlMaps.xml file.

        (0) 
  7. Antonio Sposato

    Hello Mr. Steiner,

    does the steps created here works for customizing the template for the “New Sales Order from Excel” in SAP Business ByDesign too?

    Would be able to write the differences between the two scenarios?

    Thanks,

    Antonio

    (0) 
  8. Dídac Sales Castro

    Hello

    We are facing the same issue that the comment before, we would like to customize the template of New Sales Order in Bydesign but with standard fields, we have read that is a little different.

    Can anyone tell us how to do it?

    Thanks

    Best Regards

    Dídac

     

    (0) 
  9. Roger Sainsbury

    Hi Jean-Baptiste,

    Many thanks for writing this blog, a few years ago now I see. It helped me enormously to make a similar extension to the Purchase Order Excel upload in Business ByDesign. In particular, the part about adding the reference to the customer extension namespace under Transformations:

    <xsl:element namespace=”http://sap.com/xi/AP/CustomerExtension/BYD/A0452” name=”DetailSpecsRequired”>

    I had been stuck until I got that tip from the blog.

    In response to two earlier questions – the approach given here worked for me with POs in ByD, so I would expect it should work for Sales Orders too.

    Regards, Roger

     

    (0) 
    1. Monica Cotes

      Good day Roger,

       

      I´m totally new with this.. how you identify the namespace of the extension field in C4C? I´m trying to edit my impiort template and I get an error message ´Damage template´ in excel 🙁

       

      If any could provide some advice will be really helpful.

       

      Best regards,

      Monic

      (0) 
  10. Arun Mavinahally Lakshmikantha

     

    Hi Jean,

    I have the same request to add the extension fields in Supplier Invoice Upload excel in product Business By Design. I tried the detailed approach you have described, however the ext field is not populating in excel. Looks there could be a problem with xml structure.

    Can we have a discussion on this?

    Best Regards,

    Arun

    (0) 

Leave a Reply