Solved

SQL Parser Process for FISERV's Report 0062-001 - Daily Cash Audit Trail Report...?

Posted on 2011-02-13
11
507 Views
Last Modified: 2013-11-15
FISERV is a mortgage / financial industry system that tracks all activity. There is a report that it produces called the "0062-001 Daily Cash Audit Trail Report" that reports on all daily cash payments.

Does anyone know of or have a SQL Process built that will "parse" the text and perhaps load the data from this report into a TABLE of data?

Thanks
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34883665
Monarch used to be the standard many years ago for parsing reports, but I have not used it in over 15 years.  Check it out here:
http://www.datawatch.com/_products/monarch_pro.php
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34885690
>> Does anyone know of or have a SQL Process built that will "parse" the text and perhaps load the data from this report into a TABLE of data

Ok, follow the steps below:

1. Start a Profiler trace
2. Generate the Report now
3. Queries used for generating the report would be captured in Proflier trace.
4. Suppose this is your SELECT statement

SELECT *
FROM some_table

then change this to

SELECT *
INTO ur_table
FROM some_table

to get all data from that report load into a particular table of your choice.

Note:
1. You need sysadmin or ALTER TRACE privileges to run the profiler trace.
2. Drop your temporary table ur_table each and every time to get it work.
0
 
LVL 17

Author Comment

by:MIKE
ID: 34888924
This report is a "proprietary" report of Fiserv's the SQL is NOT accessible. All that I have is an exported format in .TXT format that I'm trying to input into a TABLE. I've achieved it to some degree but I'm certain that I'm not the only one that has to deal with this report and was hoping that someone has a process in place that will parse the .TXT correctly.

I was hoping anyway....
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34893562
>> This report is a "proprietary" report of Fiserv's the SQL is NOT accessible.

That's internally in SQL Server but if anyone has sysadmin privilege granted on your server, then they can enable profiler trace and capture what is happening on the server. If the report is started when profiler trace is running, then it will exactly let you know what query is being executed so that we can use it to insert records to a temp table.

I believe instead of parsing the *.txt file, my approach would be more easier and scalable
0
 
LVL 17

Author Comment

by:MIKE
ID: 34933567
Sorry, I'm not making myself clear....

The report is NOT accessible to SQL Server in any way shape or form. It is delivered by the FISERV system in .TXT form onto a pickup location. FISERV is very adept at keeping their proprietary software hidden and secret from public.

I actual believe it is a DB2 platform, IBM old mainframe type setup if I'm not mistaken, it is actually looks alot like the old "blue screen" systems of yesteryear....  : )

So anyway,...the actual SQL that processes this report is NOT available in any form or fashion.

I was hoping that someone in the Mortgage Information Technology Industry has already dealt with this type of .TXT parsing for this specific report and could share their process or stored proc....

Thanks
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34949477
CRXIuser2005,

Since I am not familiar about FISERV, I have requested Moderators to include more zones for this question so that you can get some valuable responses from other experts..

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34952722
It seems your choices are quite clear:
1. Build your own customized report parser as we have done in my shop or
2. Purchase a third party product that does the same.

Your choice will depend on your expertise and the time you have to develop.  Cost will and should not be a factor.
0
 
LVL 17

Author Comment

by:MIKE
ID: 34953152
AC,

Ever heard the term "sharing is caring"...?  : ) Just kidding,... the thing is I knew this before I asked the question....

Thanks for your input though.

CR
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34953383
>>Ever heard the term "sharing is caring"...? <<
I wish I could.  But it is a massive project involving several tables, many stored procedures and a .NET project. All of which I did not write any code.
0
 
LVL 17

Author Comment

by:MIKE
ID: 34953453
I know AC, just kidding,...I'm sure your process is now "Proprietory" anyway,...right...?

Thanks, we'll figure it out...
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question