As there are some lovely applications out there which do not allow spying the embedded tables they have on display, it would be great if BP had a "Split Text to Collection" action within "Internal - Collections" Object. Reason being, when we have delimited text (comma, tab, pipe, etc.) stored in the clipboard, there is no way of converting it to a collection without using a code stage... which for many people is not an appropriate thing to do within an Object that interacts with applications. Why would we have such data in the clipboard you may ask? For example if dealing with Elements that have a "DataGrid" classname, or any MS Silverlight tables, or Embedded OracleDB views... the list goes on... we would have to resort to copying and storing said data in the clipboard, as it's not possible to spy. Example code for the action is as follows (VB.Net): Dim dtCSV As New DataTable Dim oRow As DataRow = Nothing Dim oColumn As DataColumn = Nothing Dim sValue, sSuffix, sPattern As String Dim aLines, aValues As String() Dim iMaxColumns, iColumn As Integer Dim FirstRow As Boolean = True Try If Qualifier.Length > 1 Then Throw New Exception("Qualifier must be a single character") End If 'Escape the separator if it is a pipe charcter If Separator = "|" Then Separator = "\|" End If sPattern = Qualifier & Separator & Qualifier '############################################################# 'Find the longest line in the CSV text aLines = CSV_Text.Split(New_Line) For Each sLine As String In aLines If sLine Is Nothing Then Exit For End If sLine = sLine.Trim If sLine = "" Then Continue For End If If Qualifier <> "" Then 'Either trim the leading and trailing Qualifier characters 'or add Qualifier characters if the line starts or ends in a Separator If sLine.StartsWith(Separator) Then sLine = Qualifier & sLine ElseIf sLine.StartsWith(Qualifier) Then sLine = sLine.Substring(1) End If If sLine.EndsWith(Separator) Then sLine = sLine & Qualifier ElseIf sLine.EndsWith(Qualifier) Then sLine = sLine.Substring(0, sLine.Length - 1) End If End If aValues = System.Text.RegularExpressions.Regex.Split(sLine, Separator) iMaxColumns = Math.Max(iMaxColumns, aValues.Length) Next '############################################################# 'Read the text again, split each line, create a data row and add to the the data table For Each sLine As String In aLines If sLine Is Nothing Then Exit For End If sLine = sLine.Trim If sLine = "" Then Continue For End If If Qualifier <> "" Then 'Either trim the leading and trailing Qualifier characters 'or add Qualifier characters if the line starts or ends in a Separator If sLine.StartsWith(Separator) Then sLine = Qualifier & sLine ElseIf sLine.StartsWith(Qualifier) Then sLine = sLine.Substring(1) End If If sLine.EndsWith(Separator) Then sLine = sLine & Qualifier ElseIf sLine.EndsWith(Qualifier) Then sLine = sLine.Substring(0, sLine.Length - 1) End If End If aValues = System.Text.RegularExpressions.Regex.Split(sLine, Separator) If FirstRow Then If Contains_Headings Then 'Assume this the header row For iColumn = 0 To iMaxColumns - 1 If iColumn < aValues.Length Then sValue = aValues(iColumn).Trim Else sValue = "Column" End If sSuffix = "" While dtCSV.Columns.Contains(sValue & sSuffix) If sSuffix = "" Then sSuffix = "1" Else sSuffix = CStr(CInt(sSuffix) + 1) End If End While dtCSV.Columns.Add(New DataColumn(sValue & sSuffix, Type.GetType("System.String"))) Next Else 'Use default headings For iColumn = 1 To iMaxColumns sValue = "Column" & CStr(iColumn) dtCSV.Columns.Add(New DataColumn(sValue, Type.GetType("System.String"))) Next 'Set the flag early so this first row is also added as data FirstRow = False End If End If If Not FirstRow Then 'Assume this is data oRow = dtCSV.NewRow() iColumn = 0 For Each oColumn In dtCSV.Columns If iColumn < aValues.Length Then sValue = aValues(iColumn) Else sValue = "" End If oRow(oColumn) = sValue iColumn += 1 Next dtCSV.Rows.Add(oRow) End If FirstRow = False Next CSV = dtCSV Success = True Message = "" Catch e As Exception Message = e.Message Success = False End Try Appreciate this can be done more elegantly... but as stated earlier - this is simply an example of the code that could be used for this. Benefit? being able to read data from screens that require a code stage to be developed in order to transform the data into a BP-Friendly format.
... View more