Parsing a text file

[Bennie47250]

Using Crystal Reports Ver 7.0
Reading a Comma delimited text file

Wanting to create a report that reads this file and having a problem with the text file. Wondering if Crystal has a function I can use to overcome the problem.

All of the fields, except the last one has the fields separated by commas and also surrounded with double quote (“) marks. The last field, which is the detail field, has several bits of information in it but these bits are separated by commas. For the report I would only like the part of the detail field up to the first comma to be in one field and then from the first comma to the second comma to be in another field. Can Crystal do this?

Data Example
Looks like this:
&quot;10/25/02&quot;,&quot;0XX0000XX&quot;,&quot;USD&quot;,&quot;28.27&quot;,&quot;&quot;,&quot;&quot;,&quot;LARRY GOING,119085 ,<br> 452412, 28.27<br>&quot;
&quot;10/25/02&quot;,&quot;0XX0000XX&quot;,&quot;USD&quot;,&quot;191.01&quot;,&quot;&quot;,&quot;&quot;,&quot;BOB BROOKS ,018829 ,<br> 435414, -195.00 162610, 195.00<br>&quot;

Would like my Crystal report to look like this:

10/25/02 28.27 LARRY GOING 119085 452412 28.27
10/25/02 191.01 BOB BROOKS 18829 425414 195.00
10/25/02 191.01 BOB BROOKS 18829 162610 195.00

If Crystal can’t handle, I was thinking I could throw the data in to an Access database but I would need to address the same issue with the data.

Appreciate your thoughts!

Bennie

 

[HowardHammerman]

No problem,

Bring the entire file into Crystal and give a field name to the last field. Call it &quot;combo&quot;.

then use Crystal functions and operators to pick out the info you need.

INSTR provides the position number of a string. For example, INSTR({table.combo},&quot;.&quot provides the position of the first period.

Use the [ and ] operators to pick out your fields. For example, {table.combo}[4 to 8] will pick out the 4th to 8th characters.

Use the VAL function to turn text into numbers.

You will end up creating three formula fields. Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[caymanuser]

Howard
I am not the originator of this message, but have a similar problem. I need to read a pure text file as data (Not comma delimited in fact it is &quot;SWIFT&quot; messages) I can handle the reformatting but as it is not a database what instruction do I use to &quot;get it into&quot; CR.

Regards

 

[HowardHammerman]

Using any current version of Windows you can create an ODBC data source name (DSN) that will read a text file.

Go into control panel and find ODBC. In Windows 2000 it is under Administrative tools and is called &quot;Data Sources (ODBC)&quot;. In other versions of the operating system it has slightly different names.

Click on &quot;Add&quot; and pick &quot;Microsoft Text Driver&quot;. Click &quot;Finish&quot; and follow the prompts. It will lead you through picking your text file and then even parcing your data if you want it to. Or you can just bring everything in as one record.

Save and close the ODBC DSN.

Now, in Crystal create a report using the ODBC DSN you created. Proceed with the parcing, etc.

Call if you need help. Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[caymanuser]

Howard,

Many thanks, it worked

Regards