Several issues with Spotfire Custom SharePoint Connector v20 (Spotfire Analyst v 12.0)

[Qing Wong]

Hello,

I have recently installed the Spotfire SharePoint connector v20, which is now compatible with .xlsm files. Originally I had posted this question as a reply to another thread, but thought it was more appropriate to make a new post instead. I have been running into several issues when working with Excel (.xlsm) present on SharePoint: 

1. The connector is working to read .xlsm files. However, one thing I am finding is that 'Ignore Empty Rows' does not work properly when there are "#N/A" in the rows - the custom connector will still read these rows into the table. This is not an issue when browsing a local file, where rows containing only "#N/A" will still be ignored when 'Ignore Empty Rows' is checked.
2. When a data connection is established with a .xlsm file, and the columns are imported into Spotfire, there seems to be a problem if a new column is added to the analysis. When browsing a local file, this new column just becomes part of the analysis; however, when using the SharePoint connector, it does not seem to be able to add in new columns that did not exist when that data connection was first made.

Would the dev team be able to look into these issues?

Thanks,

Qing

[Fredrik Rosell]

Hello Qing,

Please note, as stated in the Custom Connectors page (https://community.spotfire.com/s/article/custom-connectors-tibco-spotfirer)

"Make sure that you add the tag "CustomConnector" so that it will be visible and routed to the appropriate team(s) to respond"

I have just set that tag (topic) so no need for action now, but just something to consider for the future, to ensure your questions reach the appropriate team.

Thanks!

/Fredrik

[Qing Wong]

Hello Fredrik,

Apologies, I missed that in the docs. Thanks for routing to the appropriate teams.

Qing

[Peter McKinnis]

Qing,

I am looking into this. I may have some questions about the second point, but let me do some testing first. I am first looking at the NA issue. I will get back to you with any questions.

Thanks,

Peter

[Qing Wong]

Thanks, Peter. If I may bring up one other issue, it is that the connector doesn't seem to be able to detect the 'Name' and 'Type' rows very well. I always end up having to manually select them, whereas when browsing local files, it's able to easily detect 'name' and 'type' rows each time.

[Qing Wong]

Hello Peter and Fredrik,

I'd like to follow up on these items. Has there been any investigation from TIBCO on this matter? Specifically, data getting shifted over when a new column is added is a serious issue that makes it hard to retro-actively change the source data without totally messing up the Spotfire analysis file.

Thanks,

Qing

[Peter McKinnis]

Qing,

I did start looking at a few of these issues and then was pulled onto another project. I think I have a solution for the type and name rows and should be able to test that next week. The #N/A issue likely will require asking the user to confirm that the string "#N/A" should be treated as empty since the formula is lost by the time the data is read.

Reading a file from SharePoint Online is different than reading a local file since we have to use Microsoft's Graph API to get the content from SharePoint and not reading the file directly as one does from the local disk.

I will investigate the data getting shifted over when a new column is added. Because the Custom SharePoint Online Files Connector is a connector, a new schema is needed which is why it can't immediately pick up a new column. But it sounds like if a new column is not added at the end of the file, then the data might be wrong, is that a correct understanding of the problem?

Thanks,

Peter

 

[Qing Wong]

Hello Peter,

Understood regarding #N/A: this isn't a huge deal as any NA values can be filtered out.

Regarding data getting shifted over, you're correct in that if a new column is added in the middle of the file, the analysis file won't recognize there is a new column in the 'name row', but obviously all the data values have been moved over. As a result, the data is no longer matched with the column it should be.

Thanks,

Qing

[Peter McKinnis]

Qing,

A new release (v21) of the Spotfire Custom Connector for SharePoint Online files has been posted on the Community Exchange on May 26, 2023. This release has various updates based on this and other discussions:  

• Users can now browse for files in addition to searching
• Column name rows are automatically detected
• If an existing file has new columns, the data without the new columns displays correctly. In order to get the new columns displayed, one has to edit the schema. 
• A user can specify that "#N/A" should be treated as an empty cell for Excel files.

Thanks for the feedback,

Peter