Excel Upload using AIF File Adapter
During the last weeks I was checking the AIF File Adapter functionality specifically using it for Excel Uploads. The documentation for this feature is very basic so I thought it may be usefull to share my findings and provide a small documentation how to configure the AIF file adapter to upload excel files.
First I designed an excel file that I want to upload using AIF. I decided to have a header/item relation and to seperate these 2 areas into different worksheets.
The header worksheet looks like this (row 4 is helper input that just puts the value from the cell above in capital letters. I found row 3 better readable like this)
and the item worksheet like this (again you see the helper input in row 4):
In the item worksheet I added a column “Header_ID” that should act as foreign key to the header worksheet. The AIF file adapter will not link the data from header and item worksheets. This task is not in scope of this blog. I only want to demonstrate how the upload works.
In order to continue we will need to create a data structure that can hold the data. The target structure that is later used in the AIF File adapter customizing must be a flat structure otherwise the adapter cannot handle it.
First I created 2 simple structures:
- ZEXCELUPLOAD_HEADER_STR – contains exactly the same fields as the header worksheet
- ZEXCELUPLOAD_ITEM_STR – contains exactly the same fields as the item worksheet
Now for each structure I created a table type:
- ZEXCELUPLOAD_HEADER_TAB
- ZEXCELUPLOAD_ITEM_TAB
Finally I created the main structure that should hold the data after upload.
ZEXCELUPLOAD_HEADER_ITEM
Now the implementation part is finished. We can now look into the customoizing of the AIF File adapter. Start transaction /AIF/CUST and select System Configuration -> Configure File Adapter
In the next step you need to define under which namespace you want to configure the adapter settings.In my case I decided to go with namespace FILE:
On the next screen you need to define a configuration ID which I named HEADERITEM. Also here you define the target structure that the data should be loaded to. In our case this is the structure we created above: ZEXCELUPLOAD_HEADER_ITEM.
Next we need to define 2 scope entries – one for header worksheet and one for item worksheet.
The entry for header should look like this:
The Mapping Type must be set to “10 Automatic by field names”. The target field is the HEADER Element of the structure ZEXCELUPLOAD_HEADER_ITEM.
And now the entry for the item:
Explanaition for both. With the “Sheet Name” you configure for which excel worksheet the configuration is valid for. The data should be taken “From row” until “To row”. The columns should be considered “From column” to “To column”. The headings for the automatic mapping should be read out of “Field Name Row”.
That’s it for the file adapter customizing.
I configured an interface with raw structure ZEXCELUPLOAD_HEADER_ITEM without any further customizing just to be able to load some data in to the AIF to demonstrate the file adapter upload is working.
Important is to use XML persistence engine as shown below as otherwise the upload does not work.
So lets load some data into AIF now. You can upload an excel file using transaction /AIF/LFA_UPLOAD_FILE. Provide the Config Namespace and the Config ID – as well as the path to the excel file on your local PC.
You should see a success message like this:
Now change to transaction /AIF/ERR and select the AIF Interface for the Excel Upload where you will find one message in non final status (because there is no interface implemented beside the raw structure definition).
Header Information:
Item Information:
To process further header and items must be mapped in a way that the correct data belongs together again. This is not scope of this blog.
Hope you liked this little how to and maybe it helps you in case you have some similar requirements to solve.
Hi Oliver, thank you for sharing! Is it possible to upload the excel file from application server and to schedule the execution of upload to run in background?
Stefano
Hi, You can try t-code /AIF/LFA_CHECK_SEND.
Hi Oliver,
Can you please provide details about how to setup Odata service monitoring using AIF? may be small example?
I already tried –>
https://help.sap.com/viewer/1cefaed5b7a3471cb08564e54d5ba866/3.0/en-US/596cddcfbca143f4831aa75f235bccb6.html
Regards,
Prasad
Hi Oliver,
it would be absolutely great if you would go one step further and show hat the mapping of the HeaderIDs is done.
I'm trying to upload salesorders with exactly this szenario but i do not manage to get the correct table structure in the end.
I would really appreciate your help.
Thanks and best regards
Sebastian
Hi Oliver,
Thanks for the blog.
I have set up a file upload scenario to AIF, but I seem to be unable to map the multiple documents to the bapi structures. A single document is working fine, but multiple documents seems no to be possible. The result of the loading of the file looks exactly the same as your result from uploading the excel. Would it be possible to add the mapping of these multiple documents to the target bapi ?
Or is the only way to split this first and send the individual records to a BAPI in an action ?
Hi Peter,
sorry I maybed missed your point. Having multiple Excel Sheets in one document is not easy to merge again in AIF. For this I have used later LTMC or today Migrate Your Data. With that tool it is much more comfortable to upload Excel Files as the whole background mechanism is already implemented in standard. Have a look at this blog -> https://blogs.sap.com/2021/01/29/__exceluploadintoaif/
It is also using AIF to process record by record.
Regards
Oliver