For June our Enterprise Connector Spotlight looks at Excel Files and Slack. For this article we are going to take a look at working with Excel Files. Register now to join the webinar and get your free Enterprise Connector licenses (free license for update subscription customers only).
Many of the Enterprise Connectors support connecting to remote data sources hosted in the cloud via software as a service provides. The Excel File Enterprise Connector is different in that it supports connecting to a local Excel spreadsheet file. In theory the file could be stored on the network, but the practice is the same: It is accessing an Excel file via the filesystem.
When you define the connection you indicate the Excel file that the connector will access. One table is defined for each sheet within that file, and each column of the sheet becomes a column in the table.
You can change the orientation of the data in the sheets too. Here is the full list of connection parameters from the documentation:
Once you are connected you can use the TFDConnection's GetTableNames and GetFieldNames to enumerate the sheets of the spreadsheet and see what columns are defined.
FDConnection1.Connected := True;
ListBox1.ItemIndex := 0;
From there you simply write standard SQL to read and write date from the spreadsheet just like it was a database.
SELECT RowId, Name, Item, Quantity, Amount FROM Sheet1 WHERE Amount > '50'
There also is a CreateWorksheet stored procedure you can use to create a new worksheet or whole new workbook (spreadsheet file). There is no mechanism for modifying or dropping tables.
The Excel Enterprise Connector does not require Excel to be installed to access Excel XLSX files. Instead the connector includes a formula engine that can natively calculate most of the commonly used Excel formulas. There are two connection properties that influence how the engine works: Recalculate lets you specify if the engine should intelligently recalculate the formulas, and AllowFormulas allow you turn on the ability to insert and modify formulas via SQL statements:
INSERT INTO Excel_Sheet (A, B) VALUES ('Bill', '=SUM(B1:B5)')
Register now to join the webinar and get your free Enterprise Connector licenses. The free Enterprise Connector licenses are offered for everyone who registers for the webinar and has an active Update Subscription license.