SAP EWM Initial Stock upload file preparation using simple excel functions
Stock upload is a key activity in any SAP EWM project. Sometimes we may get automated methods to build a file but sometimes we must build it from scratch. That’s where this blog will come handy. There could be many ways of building the stock upload csv file and this is one of the method.
In this example we are using Single level HU upload with external SSCC number range.
Assuming from business users we get the file in below format:
In a reference ISU file format, we can see the key indicators:
- No of Records in ISU file
- HU numbers in HUIDENT and TOPHUIDENT field are duplicated in both the lines. Also, in storage bins are duplicated in both 1st and 2nd row per record.
- Material, Batch, quantity fields have 1st line as blank.
- Different material can have different packaging materials.
Let’s try to address the above requirements:
- Create the number of rows *2 as number of HU’s in ISU file from the reference file. In our example we have 14 HU’s so we should have 14*2 =28 rows in ISU file
2. HU numbers in HUIDENT and TOPHUIDENT field + Storage bins
- In this case we copy the SSCC/HU numbers from Fig1. Stock upload file from Business into an excel in same column back-to-back 2 times. As shown in highlighted yellow and green. This can be done for storage bin also.
- Add numbers from 1 to n in series in column B for both yellow and green highlighted Hus.
- Select the column A and click on ‘sort and filter’ , and sort A to Z.
- Select ‘Expand selection’ in the pop-up.
- The HU’s are duplicated back-to-back. We can copy paste the values in ISU file
3. Material, Batch, Quantity fields have 1st line as blank.
- Similarly for Material, Batch, and quantity we can prepare another excel worksheet. Copy the value there in column A only once. And put number series in column B from 1 twice and sort the column B using expand selection. Use the same process for others as shown for material.
- Sort the column B and expand the selection. This will show 1st line as blank, and 2nd line with an entry.
- Finally insert a blank row on the top of the excel and copy paste the entry in ISU.
- At this point the ISU will look like below. For Stock type party entitled, BP use simple drag and drop excel or above options in case its different.
4. Different material can have different packaging materials.
This could be in same excel file or different as this could be needed for packspec uploads also. Hence assuming it is in different file. In this case we will do ‘vlooup’ function to get the respective packspec into the isu file.
Fig, Pack mat table
- Keep the cursor on packmat row and type =vlookup to trigger the excel function
- Look up value ‘material in ISU file column B, in table array A:B in pack mat table, column index =3, value = 0 or false
- This will bring the correct packaging material from pack mat table.
- Drag till the end and paste the value and delete N/A values
- Our final ISU file is ready for upload.
Save it in .csv format and it is ready to be loaded using /SCWM/ISU transaction. Please keep a note on HU fields and keep them in text format.
Hope you have enjoyed reading this blog to build ISU file formatting using excel. Please like, comment with your valuable feedback. Thanks.
Please note :
- Refer SAP note for csv file : 974852 – Stock data transfer using transaction /SCWM/ISU
- All the images above taken from Microsoft office 365- Excel. Copyright protected with microsoft.com
Please follow SAP EWM Community links:
– Related topics –
– Ask questions about SAP Extended Warehouse Management and follow (https://answers.sap.com/tags/01200615320800000705)
– Read other SAP Extended Warehouse Management and follow blog posts (https://blogs.sap.com/tags/01200615320800000705/)