- Prepare an appropriate import template:
- From the
Main Menu
, chooseAdministration
Data Import/Export
Data Import
Import from Excel
. - In the
Import from Excel
window, from theData Type to Import
dropdown list, select the required business object. - Save the template:
- Choose the
Save As
button. - In the
Save As Data Import Template
window, define the template code and name. - Choose the
Save
button.
- Open Microsoft Excel and create a spreadsheet, based on the template defined in step 1.You must define the same fields in the same order as the template.
- Enter data into the spreadsheet and then save the spreadsheet as a TXT file.NoteBefore saving the spreadsheet into a TXT file, you must remove the headers, if any, and keep only the data to be imported.
- Import the data into SAP Business One:
- Go back to the
Import from Excel
window. - Choose the browse button beside
Use Data Template
and select the template defined in step 1. - Select an appropriate import method.Note that for some business objects (for example, price lists), only one or two methods are available. For detailed explanation of the different import methods, see Import from Excel Window.
- Choose the browse button beside
File to Import
and select the TXT file. - Choose the
OK
button.
The cost center planning side of SAP gives you the ability to create templates based on report painter type layouts that you then can upload into the specific planning are. Of course, if you're trying to upload something to the G/L, then I would follow Neal and Horacio's advice.
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.