LAE

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

Originally posted by: aop

Hi!

I'm trying to figure out how to use excel file workbook spec.


How should I configure the parameter to e.g. read second sheet from a workbook? I'm trying something like:
<workbook>
<sheet/>
<sheet index="2" />
</workbook>

How about reading multiple worksheets from singel workbook?

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

Originally posted by: andycooper

Hi,

With regards to your question about multiple sheets from a single workbook, this would depend on how you want to treat the data as there are two options you may wish to consider:-

1 - Merge data from multiple sheets into a single output
2 - Create an output pin per sheet in the workbook

If you want to merge two sheets into one then use (please replace the sheet names where appropriate)

<workbook>
<sheet name="Sheet1" outputIndex="1" />
<sheet name="Sheet2" outputIndex="1" />
</workbook>

If you want to create multiple outputs then you need to have the same number of output pins defined in the I/O tab then use the following: -

<workbook>
<sheet name="Sheet1" outputIndex="1" />
<sheet name="Sheet2" outputIndex="2" />
</workbook>

Ta

Andy

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

Originally posted by: Tim Meagher

Hey,

As Andy points out, adding additional output pins for the sheets you want to extract allows you to extract more than one sheet per workbook.

It's worth pointing out that you don't even need to configure the WorkbookSpec in order to achieve this.
If you have two output pins, and two worksheets in the workbook, the node will automatically extract the data from the worksheets to the node's outputs.

The node looks at all of the worksheets in the workbook and try and map them to the node outputs.
If a WorkbookSpec parameter is present, then the node will only extract the data and worksheets that are specifically referenced in the WorkbookSpec parameter.
Otherwise, it looks at each of the worksheets, and attempts to locate outputs with the same name as the worksheets.
If it sees a worksheet with the same name as an output, then that worksheet will be written to the corresponding output.

If the number of worksheets in the Excel files are variable - i.e. you don't know the number of worksheets in advance - then you can construct an output to handle all of these.
Simply create a new output on the node, give it some name e.g. "Additional Worksheets", and then set the value of the "MetadataOutputPin" parameter to the name of that output.
If this is done, then if there are more worksheets than there are outputs (aside from the output referenced in the "MetadataOutputPin" parameter), then each of those worksheets will be written to a separate BRD file, and a record will be written to the output referenced in the "MetadataOutputPin" parameter - referencing the location of the BRD file.

If, instead, you want to use the WorkbookSpec parameter, then it is possible to reference either by the index of the worksheet in the workbook, or by the name.
It is also possible to use an empty "<sheet/>" tag, which will simply get the "next" worksheet - assuming that you have not attempted to extract any worksheets by name prior to the "<sheet/>" tag.


Consider the WorkbookSpec you have used:

Here, the first "<sheet/>" element means that the "next" worksheet within the workbook should be extracted.
Since no other sheets have been specified, this would be the first sheet in the workbook.
Then, the line would extract the second worksheet in the Excel workbook.

However, if you do not have an additional output pin on the node to handle the second worksheet, then the worksheet will not be written.
If you simply wanted to extract only the second worksheet from the Excel file, and write this to the node's output - without constructing any additional outputs, you could use:

Hope this helps,

Tim.

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

Originally posted by: subhankar102

Hi All,

What if I do not want to have the column headers from the excel file while importing and want to produce the output with user defined columns, then how should the code look like?

Do we need to use the <generatedFields> option in the workbook. The reason I am asking is that my column header has a special character so Excel Node can't read it. In the Exceptional behavior option there is an option to substitute the special character, after that it worked. But this will remove the special characters from the data as well and I don't want that to happen.

Could you please advise.

Thanks

Kind Regards
Subhankar

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

Originally posted by: ThomasT

Hi Subshankar.
You can use outputHeaderRow="false" to hide the headers in the output excel.

Thomas