Blueprism Add Row Number To Collection

Question

I have a collection which I import from Excel. I would like to have a column which tells me the record number. Is this possible? A) I don't want to have an external counter B) I don't want to update the excel file to have a line number column.

I can provide screenshots if needed, but can't seem to find anyway to add the functionality I need.

Answer 1

All right, my proposed solution will use a bit of SQL inside some C# code blocks. The difference with my comment is that I thought I could use the MSSQL Syntax in the SQL query, which wasn't the case (for instance, ROW_NUMBER() doesn't work...), so I had to do a bit of googling and testing on my side. But yea it works and enough of the blabbering, you can follow these steps:

1. Create a new object (proposed name "MS Excel - SQL") and in the Initialise page, add System.Data.OleDb to the Namespace Imports and set the language to C#:

   

2. In the same initialise stage, insert the following code in the Global Code tab:

   public DataTable GetDataFromExcel (string filename,string sql)
   {
       DataTable dt; 
       OleDbConnection MyConnection;
       DataSet DtSet;
       OleDbDataAdapter MyCommand;
   
       MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filename+" Properties=Excel 12.0;");
       MyCommand = new System.Data.OleDb.OleDbDataAdapter(sql, MyConnection);
       DtSet = new System.Data.DataSet();
   
       MyCommand.Fill(DtSet);
       dt = DtSet.Tables[0];
       MyConnection.Close();
       return dt;
   }
   

   

3. Click OK, rename Action 1 appropriately, e.g. "Excel Query with Row Number" and insert a new Code stage (it's advised to name it properly as well):

   

4. You will at least need the path to the file, the sheet name and the query for inputs, and a collection for output:

   

5. Put the variables in the Start and End stages and in the Code tab, insert the following:

   Results = GetDataFromExcel(@"" + File_Path + ";Extended", Query + " FROM [" + Worksheet_Name + "$]");
   
   Results.Columns.Add("RowNumber", typeof(int));
   
   int Row = 1;
   foreach (DataRow ThisRow in Results.Rows)
   {
       ThisRow["RowNumber"] = Row;
       Row++;
   }
   

   

6. Save the object and you can now call this action from Process View. File path and Worksheet Name and Results should be straight forward, for the query, you can use something like SELECT * or select specific columns (recommended) such as with SELECT [Column 1], [Column 2]. Your Results collection will contain those columns, with an additional column called RowNumber. You can generate the identifier from this collection (add column, then loop to concatenate the date), or tweak the code stage in the object to add another column and fill it with Row and Date concatenated (except it will be less flexible as an object after that).