Excel data as input for Custom Tasks

[nrose]

Hi all,

This is probably more of a question for Chris, but if anyone knows, I am not sure how to implement custom tasks so that they can read in data from Excel as input. The usual call in C# is to use:

SasData data = new SasData(this.Consumer.InputData[0] as ISASTaskData2

but this assumes the data is SAS based from a library. When you run the task in Excel, and the user chooses Excel data, a window appears that allows the user to select the cells that contain the data of interest, but when the variable selector appears, no data variables appear in the selection, presumably because the Excel data was not able to be read in. A workaround is for the user to first copy the Excel data to the work library, and to run the task on this, but there must be a way of reading in the data as part of the custom task.

Any help appreciated.

Nick

                    );

[ChrisHemedinger · Accepted solution]

Nick,

In this case, you will need to rely on a different API to get to information about this "unrealized data" -- data that is in the spreadsheet but not yet copied to SAS.  The Office Add-In supports these APIs (as does EG) to surface information about data sources independent of the easier-to-use SasData classes.

The technique uses ISASTaskDataAccessor and ISASTaskDataColumn APIs, which are shown in the Top N Report example in my Custom Tasks book (see Chapter 9).  You can access the sample code in my GitHub repository - see the TopNReportForm.cs file.

Hope this helps,

Chris

[nrose]

Thanks Chris, that worked.

Although the SaSData class is a bit easier to use, the API you mentioned has the advantage of allowing Excel data to be used, without much extra effort.

Nick