Many e-Commerce software standard packages nowadays have a tool to import product information, content and other data with an XML or CSV file tool. A little expert knows that a CSV or XML can also be generated from Excel.
A major problem for many shops is the amount of data that is all supplied from different parties in a different way. Manually retyping or copying/pasting if you have a few products and pages is no problem at all, but if you have more data and multiple suppliers, this will be a time-consuming job. And most of the time not a very nice job too…
Of course there are plenty of options to connect a shop to ERP, PIM systems, etc., but if that is still a bridge too far because it is to expanensive or still your suppliers data is not in your ERP system, transforming data via PowerQuery offers a solution!
The power of Power Query?
Power Query is a tool in Excel available in Office365, among others, to transform data and merge Excel files or other files into 1 file.
This means that you can combine information from your own system with information from other systems and thus merge columns so that you have 1 file that you can import into your own shop.
How do you deploy it?
For example, there are 3 files:
- a file from your own shop
- a file from the supplier x
- a file from supplier y
By retrieving the 3 files in the Power Query as a separate query, you can merge them again into a new query and, for example, match based on EAN code or an article number. Then you merge the data from the columns of the supplier with your own empty columns, e.g. article characteristics, and then you have a new file that can be read in the webshop.
An advantage of this is that if the supplier file contains new products or only new information, this file must be refreshed and, based on the existing rules in the query, neatly merged with the previous merged columns.