Skip to main content

Extract only the rows, which have a defined symbol in "cell"

Hello, I was an Excel user before, but have started to work with IDEA for a while. First of all sorry if will use such definitions as "cells" etc. I have a database, it looks like
UserName | bla | bla2| bla 3| .....
Superman | 1     |   2   |    3    |  .....
Hyperm_n | 4   |   5    |    6    | .....
Lucker        |  9   |   9    |   9    | .....
So, I have about 500 Users, each of them some information in columns. Users could use wildcards in their names, like "_", but they shouldn't. So I need to obtain all the users, which have for example "_" in their name. I've already managed this using @Isin("_";CHAR4), but I want to do it in a Script. The thing is, it is a SAP Report, there are about 20 different reports, and if I learn, how to "read" an information of "cells".. like
if cells(4,B)="_" then "extract" so I will be able to use it for the rest of reports.
So I will get as a result from the table above
Username | bla | bla2  | bla3 | ....
Hypem_n | 4    |   5      |   6     | ....
So I see it like this
1. Get the index of the column which is named "ColumnName"
2. Get "count of rows"
3. For "index of row" = 1 to "count of rows" DO if "_" is in the cell(index of column, index of row)
3.a if TRUE extract to a new created ... "worksheet"
3b ELSE next i
Could anyone please help me with that? Thank you!

Brian Element Thu, 12/15/2016 - 08:14

Hello and welcome to the site.

There is an easier way to do this using the internal functions of IDEA.  I would first import the files into IDEA, depending on what you want to do with them, you might want to append the 20 different reports into one file or you can keep all 20 files.

I would then use direct extraction to extract all the transactions that meet your criteria, namely using the @Isin("_"; CHAR4).  The way that you are suggesting is doable but would contain a large amount of code, using the direct extraction should give you the same results but with less coding.

If you need help on developing the script let me know and I can create a demo for you.

Thanks

Brian

AHgp0_ID Thu, 12/15/2016 - 08:50

In reply to by Brian Element

Hello Brian,
thanks for an answer, I would appreciate if you help with developing, because the search of "wildcards" is only the one task, for only 1 SAP-report of 20. I do a lot of routine stuff with these reports, like compare users in 2 reports, check if some changes in tables aren't protocollized and so on. I could do this alone by using internal functions, thats ok by me. At the end I get normally about 25 own reports and I need about 6-8 hours to do them. So I thought it could be a good way to write a script, that could do hole import-compare-check by one click. I read most of scripts you wrote on this forum, so I know how I can get to some databases and usefull fields, the only question is... how to get to the cell in this field?
Many Thanks
Anre

Brian Element Thu, 12/15/2016 - 09:32

In reply to by AHgp0_ID

Hi Anre,

Ok, I will put some things together for you but it will probably not be before next week.

Thanks

Brian

AHgp0_ID Fri, 12/16/2016 - 03:50

So far I've got this, I've used one of your Scripts and made some changes
Sub Main
Dim db As database
Dim table As table
Dim field As field
Dim rs As Object
Dim rec As Object
Dim i, j,n As Long
Dim value As String
Dim task As task
i = 5
n = 0
Set db = Client.OpenDatabase("Usr02.txt.IMD")
Set task = db.Extraction
Set table = db.TableDef
Set Field = table.GetField("CHAR3")
Set rs = db.RecordSet
    
field.Protected = FALSE
rs.GetAt(i)Set rec = rs.ActiveRecord
value = rec.GetCharValue("CHAR4")
'Getting the count of cells in a row, actually I can save this if I use Do while, I'll do it later
Do While value <> ""
    i = i + 1
    rs.Next
    Set rec= rs.ActiveRecord
    value = rec.GetCharValue("CHAR4")
Loop
'Searching for cells containing "_" and counting them
For j=5 To i
    rs.GetAt(j)
    Set rec = rs.ActiveRecord
    value = rec.GetCharValue("CHAR4")
        If iFindOneOf("_",value) <> 0 Then            
        n = n+1
        End If
Next j
        
MsgBox n
End Sub

As a result I got access to every Cell, it returns now the number of cells there are "_" could be found.
Now I'm trying to extract them... donno how. I've tried first to use
task.AddExtraction db.Name, "", "@FindOneOf("_",value)"
The Problem is "@FindOneOf("_",value)", _ isn't recognized and drops an error if i try to use it, because it is not in the String.
Best regards,
Anre
 

AHgp0_ID Fri, 12/16/2016 - 04:09

In reply to by AHgp0_ID

May be if I get an array of index of rows which contain "_" and extract an array? Is that possible?
Or may be there is the way to use @FindOneOf() for extraction?

AHgp0_ID Fri, 12/16/2016 - 04:30

In reply to by AHgp0_ID

Const quote As String = """"task.AddExtraction dbName, "", "@FindOneOf("& quote &"_"& quote &",prevValue) "task.PerformTask 1, db.Count
and
task.AddExtraction dbName, "", "@FindOneOf(\"_\",prevValue) "task.PerformTask 1, db.Count
aren't working here

AHgp0_ID Fri, 12/16/2016 - 04:42

Now it's working... Cheers!
Const quote As String = """"task.AddExtraction dbName, "", "@FindOneOf(CHAR4;"& quote &"_"& quote &") "task.PerformTask 1, db.Count

Brian Element Fri, 12/16/2016 - 05:16

In reply to by AHgp0_ID

That is great to hear, the extraction is easy to use and saves lots of coding.  In the case where you were loopinng through the file that would work but you would also have to create a new database to hold the results, more code whereas the extraction does it for you in one function instead of having to use multiple functions.

Good luck on your reports.

Brian

The website encountered an unexpected error. Try again later.