LAE

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

Originally posted by: CarCrazyAl

I have a VERY simple Excel file with 2 columns and 2 rows. This Node Workbook Spec works just fine:

<workbook>
<sheet index="1" />
</workbook>

However, this Workbook Spec fails with a Java Exception saying "Error Reading Excel File":

<workbook>
<sheet index="1/>
<sheet headerRow="1" />
</workbook

Can anyone explain what I am doing wrong or why this is happening? I want to be able to specify which row contains the Header and which row starts the actual data.
Thank you!

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

Originally posted by: stonysmith

If your workbook has only one sheet, then use the <sheet> keyword only once.
<workbook>
<sheet index="1" headerRow="1" />
</workbook>

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

Originally posted by: CarCrazyAl

Thank you stonysmith. Makes sense.

Continuing with this topic, I have another Excel file with multiple Tabs. Tab #3 has row 1 populated with months of the year, rows 2 and 3 empty, and row 4 beginning with some numbers, 28 records (rows) total.

When I configure the node as:

<workbook>
<sheet index="3" />
</workbook>

BRE returns 28 rows.

When I modify the Node as

<workbook>
<sheet index="3" /> <sheet dataStartRow="4"/>
</workbook>

BRE still returns 28 rows. I thought it would only return 26, ignoring rows 2 and 3 which are empty. Can you explain this?
Thank you!

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

Originally posted by: stonysmith

Again, you only need the <sheet> tag once...

<workbook>
<sheet index="3" dataStartRow="4"/>
</workbook>

The <sheet> tag says to read a new sheet each time you use it.. in your example, it would be looking to read two sheets.

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

Originally posted by: Jagdev

Hi Stonysmith,

I have a query regarding the sheet name. Say I have to consolidate data from different workbooks. Each workbook contains many sheets. Is it possible to pull data from a specific sheet name say "ABC" which is available in all the workbooks. Please let me know how can we deal with this situation.

Regards,
Jagdev

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

Originally posted by: stonysmith

Refer to the sheet by name instead of by index:

<workbook>
<sheet name="ABC" dataStartRow="4"/>
</workbook>

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

Originally posted by: Jagdev

Hi Stonysmith,

I am still getting entire data instead of specific sheet.

I added a directory list and link in to the Excel file node. Below is the updated code. Please lemme know what I am doing wrong here.

<workbook>
<sheet/>
<sheet name="ABC" dataStartRow="4" headerRow="3" />
<field name="Product" />
<field name="Description" />
</sheet>
<generatedFields>
<field type="filename" />
<field type="sheet" name="SheetInFile" />
<field type="row" name="RecordNumber"/>
</generatedFields>
</workbook>

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

Originally posted by: stonysmith

You don't want the two extra sheet references.


<workbook>
<sheet name="ABC" dataStartRow="4" headerRow="3" />
<field name="Product" />
<field name="Description" />
<generatedFields>
<field type="filename" />
<field type="sheet" name="SheetInFile" />
<field type="row" name="RecordNumber"/>
</generatedFields>
</workbook>

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

Originally posted by: Jagdev

Hi stonysmith,

Thanks for the below code, but still my excel node is pulling data from different sheet. Let me explain you what I am doing here. I have 2 files and I kept the sheet name "ABC" in both the workbook and it doesn't have a single data init (Empty sheet). When I run it the node should have 0 count, because I kept the sheet name "ABC" empty, it pulls the data from the other sheet which contains data init.

Please let me know how can I achieve the desire output.

Regards,
Jagdev
Attachments:
Validation2.brg

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

Originally posted by: lnason

Jagdev - You need to populate your workbook specification xml into "WorkbookSpec" parameter (4th parameter in the "Optional" tab)....right now you have it populated in the "WorkbookSpecExample" parameter, which doesn't do anything.

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

Originally posted by: Jagdev

Hi lnason,

Thanks for the clarification on the place where the code needs to be added. I am getting the desired result. Is there anyway I can make it user friendly for example - In current scenario I have to go to workbookspec everytime and call for the specific sheet manually. Is it possible to set option for the number of sheet selection available in the workbook and call it with such option.

Regards,
Jagdev

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

Originally posted by: awilliams1024

Changing for a second, for simple use cases you may be interested in using one of the new 'accelerators' that are available to users of LAE 6.1 / LAE 6.0 who have installed LAL 6.1.44.0 / LAL 6.0.44.0.

In particular, the 'Excel Spreadsheet Import' accelerator enables you to import a single sheet from a spreadsheet file. It provides parameterized controls to specify the file path, the sheet name, the row number of the header row, the data start row number and the data start column number.

The accelerator is intended to streamline the configuration for the majority of simple use cases. The existing Excel File node can still be used to cater for more complex use cases e.g. where multiple spreadsheet files are to be imported using a single node.

Excel_Spreadsheet_Import_Accelerator_Example.zip


The accelerators can be made visible in the node palette for the current data flow by adding the accelerators as a base library:

In the BRE menu click on Graph -> Edit Base Libraries...
Then click on the 'New' icon in the Base Libraries dialogue panel
Type 'accelerators' into the new empty row (without the quotes) and click OK
The accelerators should now be visible in the different categories of the palette.

To add the accelerators to the palette for all data flows perform the following:

In the BRE menu click on Tools -> Preferences
In the BRE Preferences panel click on the 'Libraries' tab
Click on the 'New' icon in the 'Default Base Libraries' pane
Overwrite the 'new' library name with 'accelerators' (without the quotes)
Click OK
Close BRE and reopen it - the accelerators should now be visible in the palette.

Should you want to remove the accelerators from the palette, follow the initial steps you used to add them for the current data flow or all data flows and when the list of base libraries is displayed select the accelerator entry and click on the 'Delete' icon and click OK

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

Originally posted by: Jagdev

Hi William,

This is awesome node, but it should have option for multiple spreadsheets. It will be handy when someone needs to consolidate data from multiple sheets into one sheet.

The parameter option here is good. Is it possible to add then in an Excel file node.

Regards,
Jagdev

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

Originally posted by: awilliams1024

Hi Jagdev,

I'm glad you like the accelerator.

This accelerator is aimed at streamlining the '80%' of use cases where someone wants to quickly import data in a single sheet into Lavastorm. If data from multiple sheets needs to be consolidated into one data set, the simplest way to do this is to use multiple instances of the accelerator to acquire each sheet individually and then connecting the output of each accelerator to a Cat node (additional input pins will be automatically added as you add subsequent connections). The default operation of the Cat node expects the fields in each input data set to be exactly the same. However, if the fields are different you can change this behavior by setting the ConcatenationMode property of the node to either 'union' or 'intersection', as required.

Using separate accelerators to acquire each sheet in the workbook also aids transparency as it is easier to confirm that data from each sheet is being imported correctly before consolidating the data.

Regards,
Adrian

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

Originally posted by: Jagdev

Hi Adrian,

Thanks for providing the above explanation on the usage of the node. This gonna really work fine, if I need to work on the consolidation work. I am aware which node needs to be added where, but for the my other colleague it will be hard for them to understand and add nodes as per the workbook count. I already using excel node for consolidate function, but I really like the option available in this node.

Regards,
Jagdev