Non-XML Structure as a Datasource

[Former Member]

Hi,

i want to connect non-XML structure data to crystal report following is the link of non-structure XML data which i want to connect to crystal report xi version

http://153.71.45.103:8080/imagerequest/RetrieveCheckData?fromAccountNumber=1&toAccountNumber=2402823...

if above link dosnt work then check below attachment

how should i do this

kind regards,

Ganesh

xml source.txt.zip

[JWiseman]

hi Ganesh, i've attached a report that parses out the .txt file you are using. to open the report, extract the contents and change the .txt extension to .rpt.

the method to connect to these types of structured files that there is no driver that crystal can use that will define the format is to:

1) bring in the .txt file as one field that is a char 256 or larger.

2) then you use a series of formulae to first create a usable string that can be converted to an array

3) and then parse out that string in individual formulae that will act as the database fields.

report creation steps:

1) create a new ODBC (32 bit) connection using the Microsoft Text Driver...name the dsn "nonxmlsource" so that you can use this dsn with the attached sample

2) define the structure of the text datasource as follows...one single Field (F1) that is 256 characters long...essentially you are saying that each entire line in the text file is a single record for the singular field F1.

3) now have a look at the attached report...have a look at the report's record selection formula to see how it eliminates any lines that don't contain the data

4) second, have a look at the 1_FieldArray formula which creates a usable string that will be consumed later. this formula eliminates everything except for the values you need later and sets up the string as a character separated string.

5) look at the individual formulae on the report that consume the string created in the fieldarray formula. they take the character separated string and use the split function to grab the appropriate piece.

cheers,

jamie

nonxmlparser.txt.zip

[JWiseman]

hi Ganesh,

i've made the above way more difficult than it should be...sorry. easy way is this:

1) use your file with a .xml extension instead of .txt as i implied before

2) in crystal create a new report and then Create New Connection and then select Database Files

3) your xml file will show up here

4) for tables, insert Items table

let me know if this works for you,

jamie

[Former Member]

Hi Jamie,

I want same file as a from url this file is open in internet explorer http://153.71.45.103:8080/imagerequest/RetrieveCheckData?fromAccountNumber=1&toAccountNumber=2402823... this is the link i need to give in cr instead of copying the data in text file i want direct connectivity of data in cr

how can i do this?

[JWiseman]

hi Ganesh,

unfortunately i can't connect to that url or the one in the initial post from several different machines.

you've probably already tried the xml connector in crystal but it is very picky as to the format and i believe that it requires a restful service. what you may wish to try is the datadirect odbc xml driver. here is the location for the 5.3 drivers. here's how to use this driver.

let us know if it works or not,

jamie

[former_member11696]

Hi Ganesh,

CR requires a structure of some type defined. It only needs it one time so if you can export the data with the structure in an XSD format or embedded within the XML file at least one time then you can create the report.

Structure will have to stay the same or the reports will fail but it should work.

Another way may be to use ODBC and define the structure in the ODBC Administrator.

Either way it needs a structure defined at least once.

Don

[Former Member]

Hi Don,

where i find the odbc driver to define the structure ?

[former_member11696]

Try Googling for one.

Is there any way to export to CSV format? MDAC installs an ODBC driver for that format also.

You still need a Structure defined even with an ODBC driver.

What/where is this xml file coming from? Where ever it is it should have the option to export the structure also....

Or you may have to right an app to do it...

Don