Hi Tim,
Thanks for your response.
But i was looking to fetch data from multiple files with file containing _INSTANCE_REPORT.txt" of Folder "C:"
I want to fetch data in between quotes (" ") separated by comma mentioned in screen shot below.
Anyone who can help me with this ?
I’m not entirely sure if this is what you’re looking for, but perhaps the following is a start, and we can adjust based on your feedback.
First, we’ll want to filter the files:
files whose (name of it ends with “INSTANCE_REPORT.txt”) of folder "C:"
Then, we’ll want to return the lines of the files in question, and parse the strings separated by comma:
(substrings separated by “,” of it) of lines of files whose (name of it ends with “INSTANCE_REPORT.txt”) of folder "C:"
Then we can return the data between the double- quotes from the substrings (if desired?):
(preceding text of first “%22” of following text of first “%22” of it | it) of (substrings separated by “,” of it) of lines of files whose (name of it ends with “INSTANCE_REPORT.txt”) of folder "C:"
Does this help? If not, can you please provide sample input data, and the desired output?
Thanks Aram for your response but my issue is that i am not able to pull values after the first output data within the file.
I need to fetch data in one cell for each highlighted value.
Kindly suggest.
Can you provide a screenshot of an evaluation within Fixlet Debugger (or Console/Web Reports?) where only the “first output data within the file” is returned? Is Fixlet Debugger (or a custom property?) returning an error? Based on the screenshot above, and the relevance I had referenced previously, I would expect an output similar to:
Q: (substrings separated by "," of it) of lines of files whose (name of it ends with "INSTANCE_REPORT.txt") of folder "R:\"
A: "16.Jan.18.13"
A: "V3.5"
A: "CNDAIFEPDBDP07"
A: "10.14.166.66"
A: "ifeinst1"
A: "FEEDBACK"
A: "FEEDBACK
A: "
A: "C"
A: ""
A: "17.Jan.18.13"
A: "V3.5"
A: "CNDAIFEPDBDP08"
A: "10.14.166.67"
A: "ifeinst2"
A: "FEEDBACK"
A: "FEEDBACK
A: "
A: "C"
A: ""
T: 1.436 ms
I: plural substringHi Aram,
Thanks once again for your response.
I am looking to pull data from files and put it in below mentioned format.
I want to pull data in between double quotes (" ") because if i use the separator (,) then it will not pull data as mentioned in SYSADM Users tab.
“19.Jan.14.25”,“V3.5”,“abcdefg”,“xx.xx.166.66”,“filinst1,filinst1,filinst1,filinst1,filinst1”,“”,“db2grp1”,“ifeinst1”,“etzdbusr”,“ifefenc1”,“filinst1”,“db2inst2”,“l1db2grp”,“l1db2dba”,“l2db2grp”,“l2db2dba”,“a1542853”,“ia514140”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“YES”,“C”,“CLIENT”,“NC”,“SQLCA_ERR”,“C”,“0”,“C”,“0”,“C”,“0”,“C”,“”,“C”,"
Kindly suggest
This thread has been running for 17 days and I still don’t think you have described what you want. My best guess at this point, is that you have a well-formatted CSV file and you want to retrieve every CSV field as a separate result. Is that correct?
If that’s the case, see whether this does what you want CSV, Regular Expression, and Tuple String Items
Hi Aram / Jason,
Let me describe my problem once again.
I am trying to fetch data from multiple files created in same location containing same naming convention i.e “c:\instance_xyz.txt” or “c:\instance1_xyz.txt”
Data present in file is in below mentioned format : -
“19.Jan.14.25”,“V3.5”,“abcdefg”,“xx.xx.166.66”,“filinst1,filinst1,filinst1,filinst1,filinst1”,“”,“db2grp1”,“ifeinst1”,“etzdbusr”,“ifefenc1”,“filinst1”,“db2inst2”,“l1db2grp”,“l1db2dba”,“l2db2grp”,“l2db2dba”,“a1542853”,“ia514140”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“YES”,“C”,“CLIENT”,“NC”,“SQLCA_ERR”,“C”,“0”,“C”,“0”,“C”,“0”,“C”,“”,“C”,"
Now i want to pull data in between double quotes (“…”) to each cell mentioned below.
So i need to write multiple relevance to pull value1, value2 and so on to be inserted in each cell to get a report created as pasted above.
As per the relevance pasted by you i am able to fetch data from a file in single cell whereas i need separate relevance created to get data for each cell as mentioned above.
Let me know in case of any queries or concerns.
Did you read the thread I linked?
I’m pretty sure that relevance is not the best tool for what you’re trying to do. I think you are incorrectly approaching your own requirement, as Relevance is not a spreadsheet building tool. If you already have the data in a CSV spreadsheet somewhere, you should just be reporting that instead of having the client try to parse it back into … a spreadsheet.
A more common scenario would be the client attempting to ingest a CSV spreadsheet and use that to take some action on the client. That was the basis for the CSV parsing relevance I wrote in the thread, which expands out to
tuple string items of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
That will give you each cell. You can also look at an individual “column” (by index, not by column name) -
tuple string items 1 of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
Keeping track of the column names for each column is possible, but again I think you’re taking the wrong approach on this. The relevance is also going to be fairly slow to evaluate, and will get worse as the file size increases.
There’s also an inspector I recently learned about, “substrings between”. There are a lot of places this can go wrong with a CSV (embedded quotes in field data, for example) but it’s certainly simpler to read -
substrings between "%22" of lines of file "c:\temp\test.csv"
Hi Jason,
It is not the case.
Let me give you the complete picture here.
We are executing one script which is creating a set of txt files as output on a particular location in the below mentioned format.
“19.Jan.14.25”,“V3.5”,“abcdefg”,“xx.xx.166.66”,“filinst1,filinst1,filinst1,filinst1,filinst1”,“”,“db2grp1”,“ifeinst1”,“etzdbusr”,“ifefenc1”,“filinst1”,“db2inst2”,“l1db2grp”,“l1db2dba”,“l2db2grp”,“l2db2dba”,“a1542853”,“ia514140”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“YES”,“C”,“CLIENT”,“NC”,“SQLCA_ERR”,“C”,“0”,“C”,“0”,“C”,“0”,“C”,“”,“C”,"
And now i need to fetch this data using analysis to create csv file using Web Report in the below mentioned format.
Each value in between substring (“%22”) is required to be pulled and put in each cell to generate reports.
Also i am looking to get multiple relevance created for each cell highlighted in excel for each cell value.
I hope now i am able to make my situation more clear.
In order to make these appear as separate cells in Web Reports, you will need to create an Analysis with multiple properties.
Analysis: My Spreadsheet
Property 1: "Generated On"
Relevance:
tuple string items 0 of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
Property 2: “IM”
tuple string items 1 of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
Property 3: “Server Name”
tuple string items 2 of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
Property 4: IP Address
tuple string items 3 of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
Property 4: “Instance Name”
tuple string items 5 of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\computers.csv"
…repeat for each column you want in the spreadsheet.
But really you’d probably be better off taking the script that is generating the spreadsheet on the client, and using that logic to set up separate properties anyway.
Hi Jason,
Thanks again for you response.
I tried this version to fetch data but got stuck as mentioned below.
It seems analysis is not fetching data for cells where multiple results are generated.
Data fetched in QnA for tuple string item 4 is correct but data fetched in tuple string item 5 is not correct. It seems it is still fetching data with in the cell which is already fetched in tuple string item 4.
As per the result file data for tuple string 5 should be Blankbut analysis is fetching data from tuple string 4 dividing it into multiple results.
Kindly suggest.
Post the actual text of the file you’re parsing, not just a screenshot. I still think this is not the best approach, but if there’s a case my CSV regex isn’t handling I’d be interested to know.
Hi Jason,
I am executing one script on all Non-Windows machines to get output generated.
Output is generated within SHC_Logs folder as mentioned below :-
Now i need to fetch data from * .txt files and get a CSV file created using Analysis.
Data is mentioned below for reference :-
bash-4.2$ cat 10.14.166.212_inninst1_INSTANCE_REPORT.txt
“20.Jan.14.29”,“V3.5”,“CNDANETCDBDP01”,“10.14.166.212”,“inninst1”,“”,“db2grp1 “,“ctginst1,inninst1,db2cdc”,“l1db2grp”,“l1db2dba”,“l2db2grp”,“l2db2dba,ia514140”,“C”,””,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“YES”,“C”,“CLIENT”,“NC”,“SQLCA_ERR”,“C”,“0”,“C”,“0”,“C”,“0”,“C”,“”,“C”,“”
bash-4.2$ cat 10.14.166.212_inninst1_TOCAPP_DB_REPORT.txt
“20.Jan.14.29”,“V3.5”,“CNDANETCDBDP01”,“10.14.166.212”,“inninst1”,“TOCAPP”,“TOCAPPDB,TOCAPP,”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“NC”," 168",“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“C”,“”,“NC”," 85",“C”,“”,“C”,“”
Now kindly help me get the best possible approach to fetch data from these files and fetch report as per format mentioned below :-
Columns for creating Instance Report :-
body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small }
Generated On
IM
Server Name
IP Address
Instance Name
Default_IDs
SYSADM Group
SYSADM Users
SYSMAINT Group
SYSMAINT Users
SYSCTRL Group
SYSCTRL Users
UMASK
UMASK VAL
AUTHENTICATION
AUTHENTICATION VAL
SRVCON_AUTH
SRVCON_AUTH VAL
DISCOVER
DISCOVER VAL
DISCOVER_INST
DISCOVER_INST VAL
DIAGLEVEL
DIAGLEVEL VAL
USE_SNA_AUTH
USE_SNA_AUTH Val
DATALINKS
DATALINKS Val
NOTIFYLEVEL
NOTIFYLEVEL Val
FCM_NOAUTH
FCM_NOAUTH Val
CATALOG_NOAUTH
CATALOG_NOAUTH Val
TRUST_ALLCLNTS
TRUST_ALLCLNTS Val
TRUST_CLNTAUTH
TRUST_CLNTAUTH Val
DB2 AUDIT
DB2 AUDIT Val
DB2 AUDIT FILES
DB2 AUDIT FILES CNT
FILES OWNED BY FENCE
FILE COUNT OWNED BY FENCE
Instance Home
Instance Home File Count
DB2SET_DB2LOGINRESTRICTIONS
DB2SET_DB2LOGINRESTRICTIONS Val
DBM_SERVICE_RUN
DBM_SERVICE_RUN Val
Columns for creating Database Report :-
body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small }
Generated On
IM
Server Name
IP Address
Instance Name
Default_IDs
SYSADM Group
SYSADM Users
SYSMAINT Group
SYSMAINT Users
SYSCTRL Group
SYSCTRL Users
UMASK
UMASK VAL
AUTHENTICATION
AUTHENTICATION VAL
SRVCON_AUTH
SRVCON_AUTH VAL
DISCOVER
DISCOVER VAL
DISCOVER_INST
DISCOVER_INST VAL
DIAGLEVEL
DIAGLEVEL VAL
USE_SNA_AUTH
USE_SNA_AUTH Val
DATALINKS
DATALINKS Val
NOTIFYLEVEL
NOTIFYLEVEL Val
FCM_NOAUTH
FCM_NOAUTH Val
CATALOG_NOAUTH
CATALOG_NOAUTH Val
TRUST_ALLCLNTS
TRUST_ALLCLNTS Val
TRUST_CLNTAUTH
TRUST_CLNTAUTH Val
DB2 AUDIT
DB2 AUDIT Val
DB2 AUDIT FILES
DB2 AUDIT FILES CNT
FILES OWNED BY FENCE
FILE COUNT OWNED BY FENCE
Instance Home
Instance Home File Count
DB2SET_DB2LOGINRESTRICTIONS
DB2SET_DB2LOGINRESTRICTIONS Val
DBM_SERVICE_RUN
DBM_SERVICE_RUN Val
The regex appears to be working ok for me (though I’m on Windows).
q: lines of file "c:\temp\test.csv"
A: "20.Jan.14.29","V3.5","CNDANETCDBDP01","10.14.166.212","inninst1","","db2grp1 ","ctginst1,inninst1,db2cdc","l1db2grp","l1db2dba","l2db2grp","l2db2dba,ia514140","C","","C","","C","","C","","C","","C","","C","","C","","C","","C","","C","","C","YES","C","CLIENT","NC","SQLCA_ERR","C","
T: 0.314 ms
I: plural file line
q: tuple string items (1;2;3;4;5;6;7;8;9;10) of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" of concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "c:\temp\test.csv"
A: V3.5
A: CNDANETCDBDP01
A: 10.14.166.212
A: inninst1
A:
A: db2grp1
A: ctginst1,inninst1,db2cdc
A: inninst1
A: l1db2grp
A: l1db2dba
T: 6.405 ms
I: plural stringIt’s working correctly on Red Hat as well, with BESClient 9.2.1 …
Default masthead location, using /etc/opt/BESClient/actionsite.afxm
Q: tuple string items (1;2;3;4;5;6;7;8;9;10) of (("( " & concatenation " ), ( " of (if it starts with "%22" then (concatenation "%22" of substrings separated by "%22%22" of first (length of it - 1) of last (length of it - 1) of it) else it ) of (if (number of characters whose (it="(") of it = number of characters whose (it=")") of it) then it else concatenation "." of substrings separated by "(" o f concatenation "." of substrings separated by ")" of it) of parenthesized parts 3 of matches (regex "((^|,)((%22([^%22]|%22%22)*%22)|[^%22,]*)(,|$))((%22([^%22 ]|%22%22)*%22)|[^,]*)(,|$)") of it & " )")) of lines of file "/tmp/test.csv"
A: V3.5
A: CNDANETCDBDP01
A: 10.14.166.212
A: inninst1
A:
A: db2grp1
A: ctginst1,inninst1,db2cdc
A: inninst1
A: l1db2grp
A: l1db2dba
T: 64083
Q: version of client
A: 9.2.1.48
T: 72Problem still remains.
Please check the relevance mentioned by you above.
As per data we should get below mentioned results -
“ctginst1,inninst1,db2cdc”,
“l1db2grp”,
“l1db2dba”
but we are getting something else.
This is what i was trying to mention earlier as well. In file where multiple results are generated by machine, relevance is not picking correct data.
Kindly suggest.
Ahh, I see. I thought your problem was that the empy cell was getting skipped. I see now that the cell with embedded commas is being interpreted as more than one cell.
Sorry I don’t know how to fix that.
Maybe regenerate the source file and use something other than commas as a separator within the field.