LAE

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

Originally posted by: Sam.D

Hi there,

I am trying to build a chart that can be future proofed. The scenario is this, I have a directory which contains a number of excel files, the excel files can have between 1-4 sheets populated. The columns 'should' always be identical on the sheets and the files.
I can import the excel files one by one and specify the number of sheets in each then merge them into a single output, but the folder is dynamic meaning that files can be added or removed at any given time. Because of this I would like some way to identify all the files in the directory then store an array of file names for files matching a string, once I have the list of filenames I want to extract the data and concatenate it.

Using pseudocode:

CheckFileNameStr="MyFile"
Dir="MyPathtoFiles"
DataSet=nothing

Open $Dir
FileList=find files where CheckFilNameStr in $Dir\fileList

Foreach file in Filelist
open file
x=1
if sheet $x exists and contains data
append data to DataSet
else
next file

increment x
next sheet


Does anyone know whether this is possible in brainscript/lavastorm? Any pointers or examples are appreciated.

Thanks

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

Originally posted by: mlam

Hi Sam,

1) To pick up file names from a directory, you can use the Directory List node under the Acquisition category. This will search a folder directory that you specify in the "DirectoryName" parameter e.g. "C:\Users\Documents\" and return a list of the full file paths of the files found. You can either search for all files in that folder by putting an asterisk * in the Pattern parameter or you can specify a pattern e.g. putting ".xlsx" will search for all files with extension .xslx.

2) You can then use this list of file paths in the Excel File node, also under the Acquisition category and use the "MetadataOutputPin" to account for variable worksheets.
Tim Meagher provides a good explanation in this thread: http://community.lavastorm.com/threa...xcel-file-node

I've attached an example - please change the directory in the Directory List node.
excel_multiple_sheets_example.brgexcel_multiple_sheets_example.brg

Hope that helps.

Michelle