I receive Excel spreadsheets from various Brokers and the contents of each spreadsheet differs for each Broker (e.g. column headings have different names or are in a different order, some spreadsheets have more columns than others etc). I've created Graph files to map the contents of each spreadsheet into a standard Excel format (i.e. one outgoing standard format Excel file for each incoming spreadsheet) and they work well.
However, when I receive "renewal" spreadsheets from the Brokers, some of the column headings may have changed. For example, Broker A's original spreadsheet has a column called "Buildings" which is mapped to the "buildings_value" column in the outgoing standard format Excel file. Broker A's renewal spreadsheet has been revised by the Broker and the name of the "Buildings" column has been changed to "Bldgs", but it still needs to be mapped to the outgoing "buildings_value" column.
Rather than having to manually edit the Graph file each time a renewal spreadsheet is received, is there a way for the outgoing Excel file's column to be mapped to a list of possible column headings? For example, could I set up a list of potential column names (Buildings, Bldgs, Bld, Build, etc) some of which will not be in the incoming spreadsheet at the time the Graph file is created, and for those columns to be mapped to the outgoing "buildings_value" column?
However, when I receive "renewal" spreadsheets from the Brokers, some of the column headings may have changed. For example, Broker A's original spreadsheet has a column called "Buildings" which is mapped to the "buildings_value" column in the outgoing standard format Excel file. Broker A's renewal spreadsheet has been revised by the Broker and the name of the "Buildings" column has been changed to "Bldgs", but it still needs to be mapped to the outgoing "buildings_value" column.
Rather than having to manually edit the Graph file each time a renewal spreadsheet is received, is there a way for the outgoing Excel file's column to be mapped to a list of possible column headings? For example, could I set up a list of potential column names (Buildings, Bldgs, Bld, Build, etc) some of which will not be in the incoming spreadsheet at the time the Graph file is created, and for those columns to be mapped to the outgoing "buildings_value" column?
-
Hi lwickenden,
you can do mapping not based on name, but column id.
See:
http://doc.cloveretl.com/documentation/ ... rence.html (section "Mapping with Column Codes")
or
http://doc.cloveretl.com/documentation/ ... eader.html (section "Map by order")
depending on you Designer version and type. -
Thank you very much, I've tested your suggestion and it works.
This approach will work for spreadsheets that have the columns in the same order but with different names. I assume that if subsequent spreadsheets have had columns deleted or added I will just have to define new graph files, is that correct?
Best regards,
Loretta -
Hi Loretta,
yes. You can also try to pass formatting as parameter. See attached graph and:
* http://doc.cloveretl.com/documentation/ ... ments.html (-P parameter)
* http://doc.cloveretl.com/documentation/ ... eters.html (${} substitution)
Please sign in to leave a comment.
Comments 4