SXI Forum

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Configuring the Excel connector to collect data

How does one configure the excel connector to collect data from a particular excel file?

Firstly I notice that unlike the XML file collector and contrary to the annotation where the "lookupValue" attribute on the Collect mapping is used to define the name of the file to be used in the process (if the value specified in the "lookupValue" attribute does not exist in memory the value of the signal EntryID is used) this is not so when using the Excel Connector, and it seems when using the excel connector the name of the excel file and the path to this file are defined in the Connector node - Is there a reason for this ? How would I for instance read a file where the name I need is extracted from a previous collect?

Secondly what should I specify in the "lookupValue", "lookupField" and "source" attributes of the collect mapping when using the Excel connector? The clues given in the annotation offer little assistance in this regard.

Finally what should I be specifying in the Data Definition in order to pick up different cells from different worksheets from the input excel file? There is no mention in the annotation on what needs to be configured to achieve this.

SeanR

Administrator

Registered: 20-11-2018

Posts: 148

Re: Configuring the Excel connector to collect data

1. I'm not entirely sure why the file name is not specified as one would do an XML file when using the XML connector. How you would read a file where the name of that file is contained in a field in memory is a mystery to me. ANY HELP ON THIS?

2. LookupValue and LookupField can be blank, (or N/a in the case of LookupField) and the Source is the name of of the Excel Worksheet, as an Excel file can contain multiple worksheets.

3. You can specify the column as the input field ( <sxi:Field name="A"> for column A) that will cause the collect to iterate through every single row of the spreadsheet, and collect values for column A on every row.

Or, you can specify the column and the row as input field ( <sxi:Field name="A1"> for column A row 1) that will cause the collect to only collect the data in that 1 cell?

MarekR

Member

Registered: 21-02-2019

Posts: 19

Re: Configuring the Excel connector to collect data

In answer to the above post point 2:

2. The source is not referring to the name of the sheet, but rather the sheet number.

So for example, if you had 3 sheets and named the first one "info" in source you would still use "Sheet1" in the source attribute in the collect mapping.

Last edited by MarekR (08-04-2019 15:31:58)