CData Excel Functions
This section describes CData Excel Functions. A function is a preset formula that helps perform different operations and data analysis faster. All operations are related to the data within a table, i.e., to query, add, update or delete the rows, but you can also use these functions to execute stored procedures.
Functions perform specific calculations in a particular order based on the specified values, called arguments or parameters. Function parameters can refer to specific cells; as these cell values change, the add-in automatically refreshes the results.
Since Excel functions are stored within the Excel file, you can easily share your dynamic spreadsheets with others. In addition, they eliminate laborious manual entry of formulas. You can perform your tasks efficiently and save time.
Example
The examples in this section are based on different range of data as the rows of data can exist anywhere on the spreadsheet.
| J | K | L | |
| 14 | Id | Name | Name |
| 15 | MyId1 | Trujilo, Ana | Cook, Brian |
| 16 | MyId2 | ... | ... |
| 17 | MyId3 | ... | ... |
CDATAQUERY
=CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation], [Options])This formula executes a SELECT, INSERT, UPDATE, or DELETE statement or a stored procedure call. By default, the cell where the formula is entered shows the number of rows returned or the number of rows that have been modified and subsequent rows show the results.
| Query | The Query argument is a string containing the query. For example: SELECT * FROM CustomersTo execute a parameterized query, use the standard syntax: SELECT Id, Name FROM Customers WHERE Name = @Name AND Name = @Name The add-in reads the parameters from a range. Specify the range in the Parameters property. |
| Connection | A string that defines the connection: either a connection profile you created from the CData ribbon (see Managing Connections) or a connection string. Connection strings must include the Provider property. For example: Provider=Reckon Accounts Hosted;SubscriptionKey=test;CountryVersion=2021.R2.AU;CompanyFile=Q:/CData Software.QBW;User=test;Password=test;InitiateOAuth=GETANDREFRESH;CallbackURL=http://localhost:33333;OAuthClientId=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;
The Connection argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this connection by default. |
| Parameters | The Parameters argument specifies the range of cells that contains the names and values for the parameters. The first row of the range is a header row and specifies the parameter names.
For example, if the range A10:B11 contains the parameters then the cells A10:B10 should contain the parameter names, and the cells A11:B11 should contain the parameter values. |
| ResultLocation | A string that specifies the cell where the row-by-row output of the results start. By default, CDATAQUERY outputs the results just below the cell where the formula was entered. |
| Options | A string containing additional options to control the formula's behavior. The default behavior is to return data in a table, where the first row contains the column headers. "Header=False" may be set to return the data without headers. "Scalar=True" may be set to return only a single cell of data, and return it into the same cell as the formula. |
The following formula searches Reckon Accounts Hosted and outputs the results starting at row J14.
=CDATAQUERY("SELECT Id, Name FROM Customers WHERE Name = 'Cook, Brian'","ReckonAccountsHosted.Connection1",,"J14")
The following formula selects only the Name for a single Name from Reckon Accounts Hosted and outputs the result into the formula's cell.
=CDATAQUERY("SELECT Name FROM Customers WHERE Name = '"&J15&"'","ReckonAccountsHosted.Connection1",,,"Scalar=True")
CDATAINSERT
=CDATAINSERT(Data, [Table], [Connection], [ColumnHeaders])Inserts the specified rows into the specified table.
| Data | The Data argument specifies the range of rows to insert, such as J15:L15. The first row must contain the column headers unless they are specified in the ColumnHeaders argument; if you set ColumnHeaders, set only the column values in the Data argument. |
| Connection | A string that defines the connection: either a connection profile you created from the CData ribbon (see Managing Connections) or a connection string. Connection strings must include the Provider property. For example: Provider=Reckon Accounts Hosted;SubscriptionKey=test;CountryVersion=2021.R2.AU;CompanyFile=Q:/CData Software.QBW;User=test;Password=test;InitiateOAuth=GETANDREFRESH;CallbackURL=http://localhost:33333;OAuthClientId=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;).
The Connection argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this connection by default. |
| Table | The Table argument is a string that specifies the table to insert data into. This argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this table by default. |
| ColumnHeaders | The ColumnHeaders argument specifies the range of cells that contains the column headers. The ColumnHeaders argument only needs to be specified if the column headers are not in the first row of data. You can also use the ColumnHeaders argument to specify the orientation of the data table: If the column headers are located in a vertical array, such as B20:B30, then each column is inserted as a row into the chosen table. |
The following formula inserts 3 rows into the Customers table. This insertion assigns a new Id to each row. To refresh the Id values use the CDATAQUERY formula.
=CDATAINSERT(K35:Q40,"Customers","ReckonAccountsHosted.Connection1",A1:G1)
CDATAUPDATE
=CDATAUPDATE(Data, [Table], [Connection], [ColumnHeaders])Updates the specified rows.
| Data | The range of rows to update, such as J15:L15. The first row must contain the column headers unless they are specified in the ColumnHeaders argument; if you set ColumnHeaders, set only the column values in the Data argument. |
| Connection | A string that defines the connection: either a connection profile you created from the CData ribbon (see Managing Connections) or a connection string. Connection strings must include the Provider property. For example: Provider=Reckon Accounts Hosted;SubscriptionKey=test;CountryVersion=2021.R2.AU;CompanyFile=Q:/CData Software.QBW;User=test;Password=test;InitiateOAuth=GETANDREFRESH;CallbackURL=http://localhost:33333;OAuthClientId=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;.
The Connection argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this connection by default. |
| Table | The Table argument is a string that specifies the table to update. This argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this table by default. |
| ColumnHeaders | The ColumnHeaders argument specifies a range of cells that contains the column headers. The ColumnHeaders argument only needs to be defined if the column headers are not in the first row of the specified data. You can also use the ColumnHeaders argument to specify the orientation of the data table: If the column headers are located in a vertical array, such as B20:B30, then each column is updated as a row into the chosen table. |
The following formula updates the record in row 15 of the sheet:
=CDATAUPDATE(A15:K15,"Customers","ReckonAccountsHosted.Connection1",J1:L1)
CDATADELETE
=CDATADELETE(Data, [Table], [Connection], [ColumnHeaders])Deletes the specified rows.
| Data | The Data argument specifies the range of rows to delete, such as J15:L15. The first row must contain the column headers unless they are specified in the ColumnHeaders argument; if ColumnHeaders is specified, the range specifies only the values to be inserted. |
| Connection | A string that defines the connection: either a connection profile you created from the CData ribbon (see Managing Connections) or a connection string. Connection strings must include the Provider property. For example: Provider=Reckon Accounts Hosted;SubscriptionKey=test;CountryVersion=2021.R2.AU;CompanyFile=Q:/CData Software.QBW;User=test;Password=test;InitiateOAuth=GETANDREFRESH;CallbackURL=http://localhost:33333;OAuthClientId=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;.
The Connection argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this connection by default. |
| Table | The Table argument is a string that specifies the table to delete the records from. This argument is optional if you have used the Data Selection wizard to pull data into the spreadsheet. The formula uses this table by default. |
| ColumnHeaders | The ColumnHeaders argument specifies the range of cells that contains the column headers. The ColumnHeaders argument only needs to be specified if the column headers are not in the first row of the specified data. You can also use the ColumnHeaders argument to specify the orientation of the data table: If the column headers are located in a vertical array, such as B20:B30, then each column is updated as a row into the chosen table. |
The following formula deletes the record in row 14 of the sheet:
=CDATADELETE(A15:K15,"Customers","ReckonAccountsHosted.Connection1",A1:K1)