LAE

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

Originally posted by: nathanjones

HI - I can't seem to get the combination of the directory list and excel reader to work. I'm using the demo version that came with the sunseekers thing - I think this is pro-plus according to the 'about BRE' file but not sure exactly..

I have 15 excel files, same format, I use the FileName parameter as mentioned in the documentation but it only reads the first file, not all of them. Screenshots attached. Each of the files has 20k or so records.

LavastormCapture1.PNG

LavastormCapture2.PNG

LavastormCapture3.jpg

I don't see what I'm doing wrong here.. any help appreciated.

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

Originally posted by: DanRosenblatt

It looks like the Excel File node might only be reading from the first file in your directory list-- in the node under the Optional tab, try setting the ConcatenationType parameter to Union. This can occur if the column names in each of the Excel files don't match up exactly.

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

Originally posted by: nathanjones

Hi Dan, thanks for the reply.

Originally posted by: DanRosenblatt
					

It looks like the Excel File node might only be reading from the first file in your directory list
			

That's what's happening. I tried with a different 'first file' (by putting the previous 'first file' into a different directory) and that just read from the new 'first file'.

Originally posted by: DanRosenblatt
					

 in the node under the Optional tab, try setting the ConcatenationType parameter to Union.
			

I tried this and it didn't make a difference.

Originally posted by: DanRosenblatt
					

 This can occur if the column names in each of the Excel files don't match up exactly.
			

The files are exactly the same - same number of columns in the same order.

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

Originally posted by: DanRosenblatt

Based on the information provided, it seems like the node should be working. Can you report this issue to Technical Support? Please advise on the LAE version you are using (can be found in BRE under Help=>About BRE), attach the .brg file for your graph, and if possible, include at least two of the Excel files. If the information is sensitive, feel free to truncate and/or overwrite the data with dummy values.

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

Originally posted by: nathanjones

Hi Dan, I've just sent a couple of these files through to support.
thanks
Nathan

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

Originally posted by: Tim Meagher

I guess support is already looking/looked into this, but just a quick thought...
Do the sheets have different names in the different Excel workbooks?

If so, then what is happening is that the additional sheets are not being written.
It is only outputting the first sheet to the first output, and ignoring the rest if the worksheet name does not match.
As per the Excel node help:

			
				Prior to determining which node outputs are to be used for each worksheet in the file, the worksheets are first placed into tentative output buckets.

This is done based first on the outputName or outputIndex parameters specified in the  WorkbookSpec if any are set. If no WorkbookSpec parameter is set or if the outputName or outputIndex is not set for a given worksheet in an input file, then the worksheet is  bucketed according to the name of the worksheet in the input file.
			

So if you want all of the different worksheets to be written to the same output - regardless of the name of the worksheet - you would need to configure the WorkbookSpec parameter to do this. Alternatively, you could change the sheet names within the workbooks.
If, however, you just want to read all of the data and write this all out, then you can create a new output on the Excel File node and set the MetadataOutputPin to the name of that output.
Then, all of the worksheets which can't be written to the first node output will be written to a temporary BRD file, and the name of the BRD file will be written to the output of the metadata output.
If you wanted to then join this data all into one output in the LAE, you could use the Multiple BRD Files node to read it all back in.
If the columns are different in the different worksheets, set the ConcatenationMode on the Multiple BRD Files node to "Union".

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: DanRosenblatt

Hey Tim-- as you suspected, we were able to determine that this was occurring due to the sheets having different names in the Excel files.

Adding

<workbook>

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

</workbook>


to the WorkbookSpec resulted in all the rows being sent to the output pin.