Results 1 to 5 of 5

Thread: Importing from multiple excel files

  1. #1
    Lavastorm Employee
    Join Date
    May 2014
    Location
    Melbourne
    Posts
    3

    Default Importing from multiple excel files

    Hi,

    I think there is simple thing I'm overlooking here. But cant figure it out! I've got two xls files which has some simple formatting. Using a directory node to list the two files and send in the FileName parameter to an Excel acquisition node.

    I have a basic workbook spec to get the data sections I need.

    For some reason, it always only reads the first file. If I use the same node with each individual Excel file, it works fine.

    Any ideas? I've attached a sample graph and dummy files.


    Thanks!
    Attached Files Attached Files

  2. #2
    Lavastorm Employee
    Join Date
    Feb 2009
    Location
    Boston
    Posts
    52

    Default

    I would have also expected the content for both files to be written to the first output pin based on how you have the node configured.

    The Excel node doesn't seem to like the fact that the sheet names differ between the two files ("UH" in one and "DH" in the other). If you rename the sheet in one of the two files to match the other and then run your nodes again....you'll see that the content from both files is sent to the output pin.

    I think this may be a bug, but I'm not sure. There might actually be a reason why the node has been designed to work this way. I've opened a support case to have the scenario investigated (CS-9232) and I'll update this thread when I get some feedback.

    There is another approach you can take here if you don't want to change the sheet names in your excel files, but you have a static set of sheet names that you expect to encounter. You can just a new output pin to the Excel Reader node for each distinct sheet name, as shown in the attached example.

    LukeTEST.brg

  3. #3

    Default

    Hello Luke,

    Has there been any updates to the Excel (Acquisition) node since?

    I anticipate to have a similar problem where I will have to combine into one "stacked file" daily files of a report; unfortunately, the sheet name changes consistent with the date of the report - the filename is Main File DQ CCYYMMDD and sheet name is MAIN FILE DQ CCYYMM. Defining the pattern of the filename using the Directory node will not create any problem regardless of month. The problem arises when the filenames change to the next month as the sheet name will change, e.g. MAIN FILE DQ 201709 to MAIN FILE DQ 201710 - which is similar to the UH and DH issue in this thread. Because it's a daily file, the CAT node, as you have suggested as a work around, will have at least 260 input nodes to combine into one stacked output.

    Do you have an alternative approach or this issue has been fixed?

    Thank you in advance for your inputs.

    Regards,

    tris

  4. #4
    Lavastorm Employee
    Join Date
    Aug 2009
    Location
    Cologne
    Posts
    513

    Default

    Hey,

    In your WorkbookSpec, try also setting the outputIndex attribute, eg:
    Code:
    <workbook>
    <sheet index="1" outputIndex="1" headerRow="2" dataStartColumn="2"></sheet>
    </workbook>
    This should do what you want.
    Without explicitly setting the output index, it is trying to extract the data from the first input sheet, but still requires that the sheet names match when merging across multiple workbooks.
    By setting the output index, it overrides the default sheet name matching.

    Regards,
    Tim.

  5. #5

    Default

    Thanks for your inputs Tim!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •