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.