. Split a comma delimited string to a table in BIRT Report

 

You are creating a BIRT report and I need to split a comma delimited string from a dataset into multiple columns in a table.

The data looks like:

256,1400.031,-70.014,1,4.544,0.36,10,31,30.89999962,0
256,1400,-69.984,2,4.574,1.36,10,0,0,0
...

The data is stored this way in the database and you can’t change it but you need to be able to display it as a table.

The easiest way is to create a computed column in the dataset for each field. For example if the merged field from database is named “mergedData” you can split it with this kind of expression:

First field (computed column) expression:

var tempArray=row["mergedData"].split(",");
tempArray[0];

Second field:

var tempArray=row["mergedData"].split(",");
tempArray[1];

etc..

If the objects in the delimited list is variable,how do we know what each column represents and how do we go on next computations? If there are the column header labels that have completely variable length in comma delimited list that corresponds to the data. How do we put the header lable on top of the data. esProc can help this in the easiest way like this:


A
1 =file("/commaDelimited.txt").read()
2 =A1.import@c()
3 a,b,c,d,e,f,g,h,i,j
4 =A3.split@c()
5 =concat(A3,"\n",A1)
6 =A5.import@tc()

A1: Getting a String from a File,replace of demonstrating getting a String from the Database.

The result of A2:

_1          _2          _3          _4          _5          _6          _7          _8          _9          _10
256         1400.031    -70.014     1           4.544       0.36        10          31          30.89999962 0
256         1400.0      -69.984     2           4.574       1.36        10          0           0.0         0

The results of A6:

a           b           c           d           e           f           g           h           i           j
256         1400.031    -70.014     1           4.544       0.36        10          31          30.89999962 0
256         1400.0      -69.984     2           4.574       1.36        10          0           0.0         0

For detail esProc integration with BIRT, see How to Call an SPL Script in BIRT.