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!
AHgp0_ID
Offline
Last seen: 6 years 6 months ago
Joined: 12/14/2016 - 05:51
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
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
Hi Anre,
Ok, I will put some things together for you but it will probably not be before next week.
Thanks
Brian
Thanks a lot, I will wait! Anyway I can do my reports in the old way.
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
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?
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
Now it's working... Cheers!
Const quote As String = """"task.AddExtraction dbName, "", "@FindOneOf(CHAR4;"& quote &"_"& quote &") "task.PerformTask 1, db.Count
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