excel pricelist to excel import layout converter

[toneffectory]

Hi all,
I hope anybody can point me in the right direction of the challenge I'm faced with:
Periodically I have to convert excel pricelists that I receive from suppliers to an excel pricelist according to a layout that can be imported in our system. Currently this requires lots of manual labor and it would be great if this could somehow be (semi-)automated.

Detail explanation:
We receive a multitude of pricelists from our suppliers. They're all in excel, single worksheet, one item per row. Periodically (irregular timeframe) the prices of a supplier are updated and the supplier sends a new pricelist.

I can import pricelists in our quotation software. The import file has to be excel, single worksheet, one item per row. The name of the header row of each column should be a specific string in order for the import-tool to know what to do. E.g. "Brand", "Article number", "Description", "Gross price".

The excel pricelists of our suppliers are not perse according to the import file standard:
- Required columns may or may not exist (e.g. a single brand supplier will not have the "Brand" column, a multi-brand supplier will have the "Brand" column)
- Column header names may vary (e.g. "Product description" instead of "Description", "RRP" instead of "Gross price")
- Price column may or may not include valuta sign. Number may be comma or dot separated.



Is there a tool out there that aids in converting excel files from one layout into another layout? Preferably one that allows me to create conversion rules per supplier, in such a way that lots of repetitive actions are done according to pre-defined rules, e.g.:
- copy column C of the suppliers excel to column A
- copy column with headername "RRP" of the suppliers excel to column with header name "Gross price"
- for all items, add text "Nike" to column "Brand"

Thanks in advance.
BR,
Leo

 

[ranman256]

it sounds random.
youd have to write a macro for each vendor.
Vendor A, correct the column headers, change dot to comma, etc
Vendor B, other changes.

Sometimes my vendor has a unique item in a cell somewhere (their name, or other ID) to help me identify what vendor sent it. This way the macro can self correct it without my input.

 

[toneffectory]

It's random as such that it varies from vendor to vendor. But the actions that need to be done for Vendor A are always the same.
Writing a macro for each vendor is doable. But I'm curious if it could be done via a rule-based software.
Or perhaps an add-in?