Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
!Hi Guys, I need some help, Actually a lot of help I am currently working on a database, where I need to extract information from an Email Client and one of the fields I need to Extract from is the actual email which contains some important info and some irrelevant stuff Here is the example
MsgBody |
---|
Operating Company: DD01 Topic: Customer Service Request Type: Budget Billing Address 4 Blah st. City Bordan State: PA Zip: 22222 Country: USA |
Operating Company: CR01 Topic: Billing/Rate Requst Type: Bill Inquiry Address 85 Blah st. City Crowley State: PA Zip: 224442 Country: USA |
Sent: Friday, May 11, 2014 11:20: AM TO Rodger Forsester Request and include request Number below, Thank You for visiting. |
I need to create Fields showing The fields underneath and make sure the other mails that are useless not to go in to the fields
22222Billing/Rate
Operating Company | Topic | Request Type | Address | City | State | ZIP |
---|---|---|---|---|---|---|
DD01 | Customer Service | Budget Billing | 4 Blah st. | Bordan | PA | |
CR01 | Bill Inquiry | 85 Blah st | Crowley | PA | 22242 |
Any help would be greatly appreciated , thank you guys. First time Posting a question, so if I placed it in the wrong place forgive me most of the problems I have are answered.. this is a bit of a doozy for me.
Hello, Asad.
I believe the attached qvw does what you want. It uses subfield as was sugested.
Cheers!
Hi,
Use Subfield()
Like Suppose You want Operating Company
Then use
Subfield(Data,':',2)
Similarly for Topic
Use
Subfield(Data,':',3)
And So on
Regards,
Maybe I did something wrong but When I added it , It gave me the wrong data , there are a lot of messages that have no data pertaining to operating company but those that do all have that format in line 1 and 2 but there are a lot that are like line 3 which is useless data.
You have to use Subfield() many times as there are many spaces in the msgbody then try to use by below load script in your application
RawSource:
LOAD * Inline
[
MsgBody
Operating Company: CR01 Topic: Billing/Rate Requst Type: Bill Inquiry Address 85 Blah st. City Crowley State: PA Zip: 224442 Country: USA
Operating Company: DD01 Topic: Customer Service Request Type: Budget Billing Address 4 Blah st. City Bordan State: PA Zip: 22222 Country: USA
Sent: Friday, May 11, 2014 11:20: AM TO Rodger Forsester Request and include request Number below, Thank You for visiting.
] Where WildMatch(MsgBody,'Op*');
FinalTable:
LOAD
MsgBody,
SubField(SubField(MsgBody,':',2),' ',2) as [Operating Company],
SubField(SubField(MsgBody,':',3),' ',1) as Topic,
SubField(SubField(MsgBody,':',4),' ',4) as Address,
SubField(SubField(SubField(MsgBody,':',4),' ',7),' ',2) as City,
SubField(SubField(MsgBody,':',5),' ',2) as State,
SubField(SubField(MsgBody,':',6),' ',2) as Zip,
Trim(SubField(MsgBody,':',7)) as Country
Resident RawSource;
DROP Table RawSource;
I Tried your example on a new qvw and it Worked Perfectly how ever when I add the Where condition to the table it removes all the rows.
Thanks but you need to check the condition for MsgBody for where condition i put where condition for load only string which is start with Operating some thing like below script try with not putting this
Where WildMatch(MsgBody,'Op*');
Thank you for the guidance and Sorry but I am still having problems, Here is what I have, I apologize for the trouble. Thanks
Hi,
If possible can you share your apps.
If not then give us some dummy data where we can work.
Regards,
just shared it in first post
Hi,
Just sake of interest Why are you using
Where WildMatch(MsgBody,'Op*');
and If you are creating new Table from Existing one then use only one KeyField to associate them.
Or simply Use
Load * and then write your Subfield() expression in your script as you are trying right now.
And with out QVD I am not able to reload file.
Regards