LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: MarkCunningham

HI

I'm trying to import mutliable worksheets (8 in total) and the first 6 have the same headers but the last 2 do not. so the last 2 fail the node, the F1 help does not show me how to import different work sheets with different headers.

How can i do this ?

Many thanks in advance
Mark

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi Mark,

Which version of the LAE are you using?
A new Excel File node was introduced in LAE 4.5.2 which has more (and better) support for multiple worksheets with different metadata (header) information.

So how to do this depends on the version you are using.

Regards,
Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: MarkCunningham

Hi Tim
i'm using LAE 4.5
BRE V4.5.2.0

Regards
Mark

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Ok,

So you should be using the new Excel File node then.

Since you can't find this in the node help, however, I'm not sure whether or not you have the latest version of the Excel File node, since this is documented in the node parameters.....

If you see parameters "ConcatenationType" and "UnmatchedOutputNameAction" then you have the new version of the Excel File node, and the below information should help.
If not, then it would appear you are using an old Excel File node - and the below information won't help much and I think you will need to use the WorkbookSpec parameter to do what you want. If this is the case, let me know..


Anyway, assuming that you have the new Excel File node, the following questions need to be answered first:

• Is this 1 workbook with 8 worksheets, or multiple workbooks each with 1 sheet, where the sheets in the different workbooks have different metadata?
• What do you want to do with the different sheets/books?
• Do you want these all to be merged onto the one output, with a intersection/union of the metadata?
• Or do you want to have a different output for each of the worksheets/workbooks with different metadata?

If it's the case that you have one workbook with multiple worksheets and you want to merge these all onto the one output pin, then you will need to use the WorkbookSpec parameter to specify all of the sheets, and to specify the outputIndex attribute on each, specifying that they should go to the same output pin. You should then be able to set ConcatentationType to Union or Intersection to get the desired result.

If it is multiple different worksheets with one worksheet per workbook and you want to simply merge these onto one output pin, then you can set the "ConcatenationType" as Union or Intersection, and provided that the sheets have the same sheet name, these will all be merged onto the same output.

Alternatively, if you want a different output for each case where the metadata doesn't match, then you can simply add an output pin to the Excel File node for each different set of worksheet metadata you have, then set ConcatenationType to "Exact New Output" and UnmatchedOutputNameAction to "Auto Output".

If you do this, then all of the worksheets with the same sheet name, and matching metadata will be output to one of the outputs.

Each time the node encounters a new worksheet name, and each time it find a worksheet where the name matches another worksheet, but the metadata doesn't match, a new sheet will be written to a different output.

It will first attempt to write these to outputs where the output pin has the corresponding name as the worksheet name. If it can't find any output pins which have the same name, then it will look for an unused output pin and write the data to this output pin.

In cases where you don't know upfront how many different combinations of worksheet name & worksheet metadata you have, you can add an additional output pin (call it for example "metadata").

Then, on Parameters 2, you can set MetadataOutputPin to "metadata". If you do this, and have UnmatchedOutputNameAction as "Auto Output" and ConcatenationType as "Exact New Output", then the node will perform the steps as described above. However, when no more unused output pins exist, it will start writing to BRD files instead of to a node output.

It will create a record in the metadata output pin for each worksheet it encounters, stating if it was written to an output pin or to a BRD file, it will specify the name of the output pin if it was written to a node output, and the name of the BRD file if it was written to file. You can then use the metadata output pin to determine the BRD files to load into the LAE and use a BRD File node to read in the worksheet data that was written to file.

If this doesn't help, then post an example, together with information as to what you want to do, and I can see if I can help you configure the node to do what you want.


Regards,
Tim

Hi Infogix

I am using the MetaDataOutputPin to load multiple worksheets dynamically from an excel file into as many BRD files as there are worksheets. This works well except for when there is a header. E.g. if there is a header value in cell A1 of a worksheet only column A is outputted to that worksheet's BRD file. 

Is there a setting or workaround for this? It needs to be dynamic as i wont know the number of tabs or fieldnames up front or whether a particular worksheet has a header. I just want D360 to put the full contents of the worksheet into the BRD.

Thanks

Scott

 

 

Hi

Just to add that if there was a way to put an if condition in the workbook xml so that for each sheet it will check if cell A1 and B1 are populated then set headerRow="1" else If cell A2 and B2 are populated then set headerRow="2" else If cell A3 and B3 are populated then set headerRow="3" else headerRow="4". (In reality we will only ever have 1 or 2 extra rows that need to be ignored)

Or alternatively is it possible  to dynamically set the headerRow value at run time I do have that headerRow value available in the graph  (imported during the run from a separate config input file) so as to be able to create the appropriate workbookSpec data as per this post below. (Note it is old and the example wont import so i cant tell exactly what it is doing but it sounds very useful). 

https://support.infogix.com/hc/en-us/community/posts/360050696734-WorkbookSpec-in-the-Excel-node

much appreciated