LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only
Back to discussions
Expand all | Collapse all

How to pull specific sheet name and specific data from a workbook.

  • 1.  How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-05-2016 04:55

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

    Originally posted by: Jagdev

    Hi Experts,

    I need your assistance on this. I have a workbook with many sheets/tabs with specific names on them. I want to pull data from a specific sheet and that to specific rows say from row 19 to row 22 only.

    Ex -
    I have 4 sheets with name sht1,sht2,sht3 and sht4. Is it possible to pull data from sht2 and sht3 only.

    Regards,
    Jagdev


  • 2.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-05-2016 05:27

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

    Originally posted by: mlam

    Hi Jagdev,

    To pull data from specific worksheets/ rows/ columns, you can use the WorkbookSpec parameter on the Optional tab of the Excel File node.

    For example, to only read in sheet named "sht2" for data rows 19 to 22 and the header row is 18, then you can put the following as WorkbookSpec:
    <workbook>
	<sheet name="sht2" headerRow="18" dataStartRow="19" dataEndRow="22"/>
</workbook>
    More detail can be found on these forum threads:
    1) Importing from a specific row in a spreadsheet: http://community.lavastorm.com/threa...xcel+worksheet
    2) Workbookspec in Excel file node: http://community.lavastorm.com/threa...xcel+worksheet

    Also, if you have access to the "Accelerator" library of nodes, it might be easier to use the "Excel Spreadsheet Import" node.


  • 3.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-07-2016 03:47

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

    Originally posted by: Jagdev

    Hi,

    Thanks for sharing your views on this. what I am looking for is bit different. I build a graph and it is attached with the thread. Is there any way I can pull each "sheets name" data one at a time in a Read in one file node. What I did as of now is pull in all the sheets name and now will pull the sheets which I want and then individual sheet should open and then I will pull the required column and whatever sheets added in the loop will come and the pulling of data will continue till the sheets name ended.

    Regards,
    Jagdev
    Attachments:
    Sheettabs.brg


  • 4.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-07-2016 06:08

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

    Originally posted by: AdamParker

    Jagdev,

    Try this first:

    Step 1: Excel File node, Optional Tab, 'MetadataOutputPin' parameter... put in the value 'metadata'
    Step 2: Excel File node, IO Tab.. rename the 'out1' output pin name to 'metadata'.
    Step 3: Run the Excel File node (assumes you have the correct value in the 'File' parameter).

    This will give you the sheet names in the Excel file. From there you will be able to do any type of manipulation you are looking for with a filter or other node, then you can use the 'Output' field, which contains a .BRD of each sheet's data, to bring the data into the graph.


  • 5.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-07-2016 23:04

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

    Originally posted by: Jagdev

    Hi Adam,

    It worked thanks. I am not that good with .BRD. Could you please help me to read each .BRD and get set a loop.

    Regards,
    JD


  • 6.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-08-2016 04:34

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

    Originally posted by: Jagdev

    Hi All,

    I am somehow managed to pull the sheets name with the above logic. I need your assistance in 2 things.

    To pull data from row 19 to row 22 only from each sheet
    And to Append the output one after the other. In current scenario I don't know why I am not able to append the data in the append excel node.

    Regards,
    Jagdev
    Attachments:
    Sheettabs2.brg


  • 7.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-08-2016 05:04

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

    Originally posted by: mlam

    Hi Jagdev,

    It sounds like we can do this without a loop.

    1) Set up your Excel file node as per Adam's comment above BUT also include the following in the WorkbookSpec parameter on the Optional tab of the Excel file node.
    <workbook>
	<generatedFields>
		<field type="filename" />
		<field type="sheet" name="SheetInFile" />
		<field type="row" name="RecordNumber"/>
	</generatedFields>
</workbook>
    Each of the .brd files listed in the Output field will also contain the filename, the name of the worksheet and the worksheet row number, enabling us to retain a lineage of the data.

    2) Attach a "Multiple BRD files" node to the Excel file and put Output as the Filename parameter. This will read all the .brd file data together (i.e. no need to set up a loop). If the data on the worksheets differ, you can choose how to manage this using the ConcatenationMode parameter on the Optional tab.

    3) To select only the data from rows 19 to 22 on each worksheet, you can use a Filter node to select records where "RecordNumber" field is between 19 and 22.

    Example data flow (please copy the text into your BRE):
    node:Multiple_BRD_Files
bretype:core::Multiple BRD Files
editor:sortkey=57d14f1c550901f9_2
input:@40fd2c7405e716f4/=Excel_File_2.48cf6cd2305e5f95
output:@40fd2c74028104af/=
prop:ConcatenationMode=union
prop:Filename=<<EOX
Output
EOX
editor:XY=580,80
end:Multiple_BRD_Files

node:Filter_3
bretype:core::Filter
editor:sortkey=57d1525b1d240f01
input:@40fd2c74167f1ca2/=Multiple_BRD_Files.40fd2c74028104af
output:@40fd2c7420761db6/=
prop:Script=<<EOX

emit *
where 'RecordNumber' >= 19 and 'RecordNumber' <= 22
EOX
editor:XY=670,80
end:Filter_3

node:Excel_File_2
bretype:core::Excel File
editor:sortkey=57d14f6e1030106a
output:@48cf6cd2305e5f95/metadata=
prop:File=C:\Users\mlam\Lavastorm\LAE Dev\Small examples\Multiple Excel worksheets\excel_multiple_sheets_02_cellA1.xlsx
prop:MetadataOutputPin=metadata
prop:UseSystemLocale=true
prop:WorkbookSpec=<<EOX
<workbook>
	<generatedFields>
		<field type="filename" />
		<field type="sheet" name="SheetInFile" />
		<field type="row" name="RecordNumber"/>
	</generatedFields>
</workbook>
EOX
editor:XY=480,80
end:Excel_File_2

    Michelle


  • 8.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-13-2016 04:56

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

    Originally posted by: Jagdev

    Hi Michelle and Adam,

    The logic worked fine and I am able to accomplished the task.

    Regards,
    Jagdev


  • 9.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 09-13-2016 06:23

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

    Originally posted by: mlam

    Great to hear! Thanks Jagdev


  • 10.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 10-18-2016 00:45

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

    Originally posted by: Jagdev

    Hi All,

    I have a query regarding the above thread. Say the sheets I pulled with the logic of "Metadata" in the excel node, won't pull the sheets information which didn't start its data from row1. Is there a way to consider those sheets as well in the above logic.

    Regards,
    Jagdev


  • 11.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 11-14-2016 00:38

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

    Originally posted by: Jagdev

    Hi All,

    Please let me know if there is any way around for the above query.

    Regards,
    Jagdev


  • 12.  RE: How to pull specific sheet name and specific data from a workbook.

    Employee
    Posted 11-14-2016 07:11

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

    Originally posted by: AdamParker

    Originally posted by: Jagdev
					

    Hi All,

    
I have a query regarding the above thread. Say the sheets I pulled with the logic of "Metadata" in the excel node, won't pull the sheets information which didn't start its data from row1. Is there a way to consider those sheets as well in the above logic.

    
Regards,
    
Jagdev
    No there isn't, not currently.