TIBCO Scribe® Online Connector For Scribe Labs — Excel

Scribe Labs Connectors are Connectors that our TIBCO employees built and wanted to share. We think these are good enough to share but they are not “productized” like our Solution Connectors and results may vary. Use these Connectors “as-is”. TIBCO does not offer formal support or guarantees for these particular Connectors. Scribe Labs Connectors can be changed or removed at any time without prior notification. TIBCO provides no support or commitment to update or maintain these Connectors in the TIBCO Scribe® Online Marketplace.

Use the Connector For Scribe Labs — Excel to read and write MS Excel (.xls, .xlsx) and comma delimited (.csv) files. This Connector is designed for easy read/write access to on-premise Excel and CSV file data sources without needing to install MS Excel on the On- Premise Agent server. Metadata for the files is automatically generated from the header row of each file.

Connector Specifications

	Supported
Agent Types
On Premise	X
Cloud
Replication Services
Source
Target
Integration Services
Source	X
Target	X
Migration Services
Source	X
Target	X
Maps
Integration	X
Request-Reply	X
Message

Note: This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified Connectors for more information.

Supported Entities

Excel Files

Each sheet in your Excel spreadsheet becomes an entity with the name in the format filename.sheetname. For example, if the file name is accounts.xlsx and the sheet names are contacts and leads, then there would be two entities named accounts.contacts and accounts.leads.

CSV Files

Each CSV file becomes an entity with the name in the format filename. For example, if the file name is contacts.csv then the entity is contacts.

Standard Operations By File Type

File Type	Query	Create	Update	Delete
XLS	X	X*	X*	X*
XLSX	X	X	X	X
CSV	X	X	X	X

Note: * These operations convert the file to XLSX when data is written to the file.

Setup Considerations

When configuring the Connection dialog for this Connector, the Connection requires a full path to a directory accessible to the TIBCO Scribe® Online On-Premise Agent, such as C:\Program Files (x86)\Scribe Software\TIBCO Scribe® Online Agent\DataExchange\Excel. This Connector reads all XLS, XLSX, and CSV files in the selected directory.

Excel Files

Supports both XLS and XLSX files.
Excel is not required on the server where the TIBCO Scribe® Online Agent is installed.

Connecting To Scribe Labs — Excel

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.

Select More > Connections from the menu.
From the Connections page select Add to open the Add a New Connection dialog.
Select the Connector from the drop-down list in the Connection Type field, and then enter the following information for this Connection:
- Name — This can be any meaningful name, up to 25 characters.
- Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
- Full Path (No Filename) — The full path to the directory where your Excel or CSV files are stored, such as C:\Program Files (x86)\Scribe Software\TIBCO Scribe® Online Agent\DataExchange\Excel. This directory must be accessible to the TIBCO Scribe® Online On-Premise Agent and must contain at least one file.
- Archive Updated Data — Select either True or False. When using Excel as a target, the Connector writes data to the Excel files in the selected directory. The Archive feature protects those files by making a backup copy before the files are modified by the target operations. If a Solution executes using this Connection and the data in the target Excel files is modified, the files in the selected directory are processed as follows:
  - True — TIBCO Scribe® Online makes a backup of the files in the directory and saves them with a timestamp extension. After the backup copies of the files are made, the original files are modified.
  - False — Updates the data in the original files without making a backup.
Select Test to ensure that the Agent can connect to the folder containing your Excel files. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
Select OK/Save to save the Connection.

Metadata Notes

Excel Files

Each sheet in your Excel spreadsheet becomes an entity with the name in the format filename.sheetname. For example, if the file name is accounts.xlsx and the sheet names are contacts and leads, then there would be two entities named accounts.contacts and accounts.leads.
The first row of the spreadsheet is considered the header row and each column name is a field name for the entity.
Data types set for each column in Excel are honored.

CSV Files

Each CSV file becomes an entity with the name in the format filename. For example, if the file name is contacts.csv then the entity is contacts.
The first row of the CSV file is considered the header row and each column name is a field name for the entity.
Data types for all columns are 'string' data types.

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

Scribe Labs — Excel Connector As IS/MS Source

Consider the following when using the Scribe Labs — Excel Connector as an IS/MS source.

All files are read completely into memory by the TIBCO Scribe® Online Agent for faster processing. Extremely large files may have an impact on performance.

Filter, Match Criteria, Or Lookup

Supported for any field in either Excel or CSV.
Only AND operations are supported. OR operations are not supported.

Scribe Labs — Excel Connector As IS/MS Target

Consider the following when using the Scribe Labs — Excel Connector as an IS/MS target.

For Excel XLS file types, all operations that write to the target file automatically convert the file to XLSX when the updated file is written.
When Archive Updated Data is set to true on the Scribe Labs — Excel Connection dialog, a copy of the original target file is saved with a timestamp extension prior to writing or deleting data to any file type.

TIBCO Scribe® Online API Considerations

To create Connections with the TIBCO Scribe® Online API, the Scribe Labs — Excel Connector requires the following information:

Connector Name	Scribe Labs - Excel
Connector ID	3F9535EF-E5D8-45B5-83C2-339FE941986A

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

Name	Data Type	Required	Secured	Usage
RootFolderPath	String	Yes	No
archiveData	String	Yes	No

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.