How to convert unstructured data to fields in Crystal

I have a data record that is unstructured, much like XML, and I want to create data fields from it.

I want to use this data in a Crystal Report

For example: { "customer": "IT_news", "report_type": "pds_document_views", "readable_pub_date": "01/23/2015", "readable_timestamp": "01/27/2015 17:20:11", "datasource":  }

Should end up looking like this:

Formula Field                         Contents

customer                                 IT_news
report_type                             blank
pds_document_views            blank
readable_pub_date                1/23/2015
readable_time_stamp            01/27/2015 17:20:11
datasource                               blank

The idea is to convert an unstructured character string into specific data fields in Crystal
Who is Participating?
Try using these formulas

Name - BuildOutputs - Put this in the detail section
Local StringVar myData := {YourField};

Local StringVar Array strPairs;
Local NumberVar index;
Local NumberVar ArraySize;

Global StringVar strHeadings;
Global StringVar strData;

myData := Replace(myData,'"','');
myData := Replace(myData,'{','');
myData := Replace(myData,'}','');

strPairs := Split(myData,',');
ArraySize := UBound(strPairs);

For index := 1 to ArraySize do
    strHeadings := strHeadings + chr(13);
    strData := strData + chr(13);
    strHeadings := strHeadings + Split(strPairs[index],': ')[1];
    If UBound(Split(strPairs[index],':')) >= 2 then
          strData :=  strData + Split(strPairs[index],': ')[2]
          strData := strData + '  ';

Name - DispHeadings

Global StringVar strHeadings;

Name - DispData

Global StringVar strData;

Open in new window

Put the 2 DIsplay formulas into a section below the one with the Build formula
If all need to be in the details, then put BuildOutput into the detail section and create a second detail section for the display formulas.

DSIGRISAuthor Commented:
Thanks.   It will be a while for me to get into this, but I certainly will give it a try.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.