Aegis ‘Depot’ Activity – Microsoft Excel Query

Aegis ‘Depot’ Activity – Microsoft Excel Query

Originally I didn't think this activity would be used much - but as it turns out Excel is still widely used for storing portable table data which you might expect to find in an SQL Database so this is still a really popular activity in Aegis workflows.  This version has a number of updates to keep up with the fast paced world of Office!

This is the Activity Configuration UI - Its much the same as the previous version (which makes upgrade easy), but it has some extra options for Excel Files of version 2007 -> 2013.  In this example, its a simple query, pulling all columns from Sheet1.  There are additional options for filtering in the simple query.

ExcelConfig

And these are the results at run-time, available in Table and plain text formats.

ExcelResults

For more control over your data - and this really helps Aegis as we don't really want to read in entire Excel sheets if they are not required - you'll might want to use the complex query option and treat your excel file like an SQL database.  This is a simple example of a Complex Query (this one can also be done in the Simple filter as its pretty trivial!) :

ExcelConfigcomplex

And this is the result :

ExcelResultsResults

Nice!

Excel file being a single user 'database' will only allow one process access it at a time, so the 'file in use' flag' can be used as a decision path so you can wait and certain time before retrying to read the file.  Other File I/O issues will result in activity error which can be handled by workflow.

Complex Queries can also be used to INSERT and UPDATE data in your Excel Spreadsheets.   These are examples for the same Sheet as in previous examples:

INSERT INTO [Sheet1$] VALUES ('MMarx',909-555-123,'The Grove','Galway',1234567)

UPDATE [Sheet1$] SET [User] = 'NMarx' WHERE [User] = 'MMarx'

If your Data does not have Headers, then you can still access your data using F1, F2, F3 as column names, where F1 is the first column:

SELECT F1, F2 FROM [Sheet1$]

select F4,F1 from [Sheet1l$] WHERE F4 = 'Cork'

Update [Sheet1$] Set F4 = 'Belfast' WHERE F4 = 'Cork'

insert into [Sheet1$] (F1, F2, F3, F4) values ('MMarx',909-555-123,'The Grove','Galway',1234567)

This activity basically conforms to the rules set out in : http://support.microsoft.com/kb/257819, so DELETE operations are not possible.  As in the KB, you can use the UPDATE statement to blank out entries.

If you already have a ‘pre-community’ version of the Depot_MSExcelQuery_3000_0_2 you will need to update to this version!  If you are running the previous version Depot_MSExcelQuery_3000_0_1,  the Module updater in the Aegis Configuration Console can be used to update your workflows automatically to the new versions or they can be updated manually.  Old and new versions can also run side by side so no upgrade is required.

As well as updated version and file formats, this version also automatically handles reserved values used as column names in Excel Documents when you use the Simple Query Option.  If you choose the Complex Query option, then you need need to put [ ] around column names such as [User].

There is also a parameter (not in screenshots) to allow you specify the delimiter for the text result.

For reference a list of reserved words are available here: http://support.microsoft.com/kb/321266

Activity Installation Instructions are here:

https://www.netiq.com/communities/cool-solutions/how-to-install-a-custom-depot-aegis-activity-from-netiq-cool-tools
Attachments

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2013-12-05 19:44
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.