Add Split Text as Internal Action

[asilarow]

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.