The most recent version of PAM (RMS & RMH only) gives us ability to import data from xls files and match it to existing items. This way you can quickly add a new attribute and populate its values.
This tutorial allows you to work on large datasets so please be very careful and, as always, having backups is strongly recommended.
To do so, we need to create a spreadsheet that contains a column with Item Lookup Codes that we use for matching. In our example we will be adding brand names to our items.
As you can see, I named the columns in the first row. PAM has an option to read the first row as a column name what makes matching the spreadsheet columns to the attributes even easier.
Once we have the spreadsheet ready, we can proceed to the Product Attribute Manager and open the spreadsheet importation tool.
The first step is to select the file. You need to close your spreadsheet editor for this to work. The checkbox allows you to use the first row as a column name
If you named the first row ‘ItemLookupCode’ PAM will pick up on that and match the columns automatically. Other columns will have to be selected manually. Please be extra careful while doing that as this might lead to data corruption. In our case, I picked the Brand Name attribute.
Next screen presents us with a preview. It allows us to review the changes before accepting. At this stage we can still go back and reassign the columns.
When we accept the preview, the data is updated and we are presented with a result screen. Please remember, that during the process data is committed automatically, so when it finishes you won’t have an option to discard the changes.
You will need to restart PAM to see the results
Please let us know if you have any questions or contact support if you need assistance using this feature.
Is there a way to export the list from PAM in the first place?
No, we don’t have such feature currently available. This has been created with supplementing existing information with external sources such as spreadsheets coming from manufacturers.
Is there a way to match on something other than itemlookupcode? Product data from suppliers is increasingly available in spreadsheet form but for an import to work it would need to match on a suppliers identifier for a product not ours - an MPN or GTIN in other words. We have both of these available as PAM columns.
Not currently. As usual, we can go with a feature suggestion, or a (relatively small) custom dev. Let us know if you’re interested.
We would like to see it as a feature suggestion for now - as we cannot ever see ourselves being able to use the import utility without it. We have seen a huge increase in web ready data supplied from manufacturers but at the moment its a manual copy and paste job into the relevant PAM fields (including notes) for us. For the future it seems necessary in order to be able to keep up with the big internet only type retailers who are clearly able to bulk import en masse from supplier data, giving them significant advantage in terms of cost.
We have added this feature to our suggestion list and it will be taken care of.
We will inform you about the progress on that score.
This might work as an interim workaround to use the Item Lookup Number.
- First, build an Excel list with one column of supplier ID matched with another column of Item Lookup Numbers.
- Next insert that data as a second worksheet into your PAM import spreadsheet.
- In the PAM worksheet, create an Item Lookup Number column next to your supplier ID column.
- Build a vertical lookup calculation that uses the supplier ID to pull the Item Lookup Number info into the column you just created in the PAM worksheet. If needed, refer to Excel Help for details on how to use the vertical lookup function.
- PAM imports work best with a clean import source so I would suggest saving your spreadsheet and then saving as a second copy.
- In the copy file, first copy the PAM import worksheet data and then paste the data as values back on top of itself. The leftover vertical lookup calculation can mess up the PAM import tool so this cleans up that problem.
- Finally, remove the supplier ID/item lookup worksheet so that only the PAM import worksheet remains.
- As you import into PAM, I would double check the import mapping to make sure all the fields are matched correctly.
Hopefully, this is useful for you. This first document can be used as a template for future imports.
Let me know if you have questions or feedback.
Thank you SO much, these instructions are brilliant. I will definitely try this