Solved

PIPE delimited text to Print format

Posted on 2011-09-15
12
414 Views
Last Modified: 2012-05-12
We receive a PIPE delimited text file on a daily basis and I need a way to parse this to a page for printing. File looks like:

 10||RecordID||LastName||FirstName|...
 20||RecordID||InsCompany1||PolicyNum1|...
 20||RecordID||InsCompany2||PolicyNum2|...
 30||RecordID||DateOfService||Procedure||Units||Charge|...
 10||RecordID||LastName||FirstName|...
 20||RecordID||InsCompany1||PolicyNum1|...
 30||RecordID||DateOfService||Procedure||Units||Charge|...

and so forth. Each "Patient" record begins with '10'. Each line is 512 characters long.

Need an automated process to read and format the file.

Any suggestion is welcome.
0
Comment
Question by:wcotis60
  • 5
  • 4
  • 3
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36546603
DO you want to do a report or just setup a file for printing?

mlmcc
0
 

Author Comment

by:wcotis60
ID: 36546617
I need the result to be in a defined report with the fields laid out:

    PatientID   LastName   FirstName and so on from the "10" row
    InsCompany1   PolicyNum1 and so on from the "20" row
           (There will be 1 to 5 "20" rows per patientID)
    DateOfService    Procedure Charge and so on from the "30" row
          (There will be 1 to 6 "30" rows per patientID)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36546980
The data looks like this? or are there " "  around the fields?

 10||0001||Smith||Joe|...
 20||0001||Ins1||Ins1-1234|...
 20||0001||Ins2||Ins2-2123|...
 30||0001||10 Sep 2011||Hangnail||1||100.00|...
 10||0002||Jones||Kay|...
 20||0002||Ins3||Ins3-3214|...
 30||0002||11 Sep 2011||TongueTied||2||250.00|...

This is what you want?
0001    Smith  Joe
   Ins1   Ins1-1234
   Ins2   Ins2-2123
   10 Sep 2011  Hangnail   1   100.00
0002    Jones   Kay
   Ins3   Ins3-3214
   11 Sep 2011  TongueTied  2   250.00


mlmcc
0
 

Author Comment

by:wcotis60
ID: 36549126
Yes, sorry my earlier response was so obtuse.
0
 
LVL 34

Expert Comment

by:James0628
ID: 36553715
Do you want any totals (eg. for a patient) or anything like that, or is the report just reformatting the data in the file?  My first thought was to create a group on the patient (which would make patient totals easier), but maybe all you need is a separate detail section for each record type (with formulas to extract the fields from each record).

 What do you think mlmcc?

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36554510
I don't think Crystal can handle it.  I tried usiing it as an input but I didn't see a CSV option so I just used it as a text file.

It took the first line as the field name and the rest were limited to the length of the econd line.

Can you modify the routine that produces the file to add a first line as a field name.
You could then parse it with SPLIT and put the fields on a report.

mlmcc
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:James0628
ID: 36555232
Hmm.  Been a while since I used a text file as a datasource.  I hadn't thought about CR trying to get the field name(s) from the file.

 Would CR put the entire record in one field, or would it see the spaces as field breaks?

 James
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 333 total points
ID: 36555265
From my example data the field name was  10||0001||Smith||Joe|

The rest of the data was single field per row limited in length by the length of the first record
 20||0001||Ins1||Ins1-1234|
 20||0001||Ins2||Ins2-2123|
 30||0001||10 Sep 2011||Hang
 10||0002||Jones||Kay|
 20||0002||Ins3||Ins3-3214|
 30||0002||11 Sep 2011||Tong

I don't have the CSV or Tab Separated Text driver loaded and my CD is elsewhere.  I used the Text driver under the Access/Excel option

I figured I could split based on the || to get the fields.

He says his rows/records are all 512 characters so that might work.

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 167 total points
ID: 36565906
I have the feeling that the CSV and Tab Separated options may not work well with this data, since the records come in 3 types with different data.  If the type of data in a column varies, I suppose CR would just use the string data type for that column, in which case that shouldn't be a problem.  But what if the different types of record have a different number of columns ("|" symbols)?  I think that might be a problem.

 Another potential problem with using the CSV or Tab option is that, if CR tries to set the data type for each column based on the data in the file, then one day CR might decide to interpret a column differently because the data was a little different.  I have no idea if that would, or could, happen.  It just occurred to me that it might.

 Which is all just my long-winded way of saying that getting the record as one string seems "safest".  :-)

 James
0
 

Author Comment

by:wcotis60
ID: 36573954
mlmcc & James0628,
I appreciate your comments, seems through my trials of some of yours and other sources this can not be done directly with Crystal.

Going to have to do a little VB work to manipulate the data.

Thank you for the suggestions, there is no real solution...

    What about the points??
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 333 total points
ID: 36575700
AS it states in the EE help, sometimes the answer is it can't be done.  If the comments were helpful in reaching that conclusion then you should award appropriately.  You can choose your final comment as the solution

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 36579502
I didn't get into this other stuff before because it looked like it might be doable in CR using the file as is, but if not ...

 If you don't want totals or anything like that and really just want to reformat the text file (primarily replace the delimiters with some spaces and maybe do a little more tweaking), you could probably use a text editor utility like sed or awk (Unix utilities, but there are Windows versions available), or something more sophisticated like Perl.  Removing the record type and replacing the delimiters with spaces would be easy.  Not showing the RecordID on some lines and indenting some lines would be trickier, but probably doable.

 Another option might be to actually split the file into 3 separate files, one for each record type (10, 20 and 30).  A simple utility (like the Unix grep, or maybe the Windows FIND command) could do that in a batch file.  Having each type of record in a separate file might make a CR report more viable, assuming that the fields were consistent for each record of a given type.

 You could also try importing those 3 text files into separate Excel sheets (in a single Excel file or 3 separate files) and using those in your report.  You may need to add a heading row to each sheet, and there could be issues with how CR interprets the data in the columns, but it could work.  That's just another idea that I was toying with, but it didn't seem likely to work with all 3 record types (with presumably different columns) in one text file.  But if you separate the record types, maybe it would work.

 Just some other ideas I had.

 James
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now