double quotes in a field value in flat file datasource

hello all, 
i am currently using BIRT eclipse designer to design a xls report using a flat file datasource(PSV file). 
The problem i'm facing is that in my datasource file i have fields containing double quotes(") for eg: 
 
SOURCE_TYPE|DATA_VALUE 
medical|"$"128.50 
medical|"$"524.20 
pharmacy|"$"6982.50 
 
and while creating dataset from birt report desinger it gives following error: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot fetch the next data row.
org.eclipse.datatools.connectivity.oda.OdaException: Invalid flat file format

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)

at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)

at org.eclipse.birt.data.engine.odaconsumer.ResultSet.fetch(ResultSet.java:143)

at org.eclipse.birt.data.engine.executor.cache.OdiAdapter.fetch(OdiAdapter.java:214)

at org.eclipse.birt.data.engine.executor.cache.RowResultSet.next(RowResultSet.java:105)

at org.eclipse.birt.data.engine.executor.transform.SimpleResultSet.<init>(SimpleResultSet.java:90)

at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:996)</pre>
 
i know this error is due to that double quote in the DATA_VALUE field cause as i remove those quotes it runs fine.So how to handle such data values.

Find more posts tagged with

Comments

kclark

You would need to use an escape to allow for the double quotes so instead of 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>pharmacy|"$"6982.50</pre>
 
it would need to be 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>pharmacy|\"$\"6982.50</pre>
 
You could to a find replace in excel to quickly change over all double quotes to \"

naam.nites

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="110613" data-time="1350483992" data-date="17 October 2012 - 07:26 AM">
You would need to use an escape to allow for the double quotes so instead of 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>pharmacy|"$"6982.50</pre>
 
it would need to be 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>pharmacy|\"$\"6982.50</pre>
 
You could to a find replace in excel to quickly change over all double quotes to \" </blockquote>
 
 
That could be the solution but the problem is that we have PSV file generated and are not allowed to change it so we can't change anything on it. So what we should be doing is read the PSV datasource as it is but escape the character(") in runtime. Is there anyway to do it by using expression?

kclark

You could read the data into an array and add the escape that way then use that array as a scripted dataset.

naam.nites

sorry for the late reply, being new to birt i studied about the scriped datasource. Looked at this example too (<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/544-sample-birt-scripted-data-source-example/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/544-sample-birt-scripted-data-source-example/</a>) 
but i'm confused about how to use the CSV file for scripted datasource. If you have any examples it would be a great help. 
 
Thanks. 
<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="110655" data-time="1350567505" data-date="18 October 2012 - 06:38 AM">
You could read the data into an array and add the escape that way then use that array as a scripted dataset. </blockquote>

kclark

naam, <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1259-iterate-over-csv-files-to-change-flat-file-dataset/'>here's a devshare</a> that iterates over csv files you might want to take a look at. 
 
It uses the following in to get the CSV 
 
open() 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.io);
directory = new File(params["fileDirectory"]);
files = directory.listFiles();
csvfiles = [];
for( i=0; i<files.length; i++ ){
if( files[i].toString().endsWith(".csv") ){
csvfiles.push(files[i].getName());
}

}
idx=0;

</pre>
 
fetch() 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if( idx < csvfiles.length ){
row["filename"] = csvfiles[idx];
idx++;
return true;
}else{
return false;
}
</pre>

naam.nites

Thanks kclark for the help finally solved the problem

. This is how i sloved it: 
First i created a scripted datasource then created a dataset using that datasource. 
In the dataset i created a open and fetch script to read the data into local variable on which then i replaced the special characs. 
 
open(): 
importPackage(Packages.java.io); 
directory = new File(params["dataSource"]); 
lines=[]; 
report = new BufferedReader(new FileReader(directory+"\\report.txt")); 
line = null; 
while ((line = report.readLine()) != null) { 
var tarr = []; 
tarr = line.split("\\,"); 
lines.push(tarr); 
} 
idx=0; 
 
fetch(): 
if( idx < lines.length ){ 
row["SOURCE_TYPE"] =lines[idx][0]; 
row["TABLE_NAME"] =lines[idx][1]; 
row["COLUMN_NAME"] =lines[idx][2]; 
row["DATA_VALUE"] =lines[idx][3].replace("\"",""); 
idx++; 
return true; 
}else{ 
return false; 
} 
 
Thanks kclark

. Finally got it. 
 
<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="111204" data-time="1352126640" data-date="05 November 2012 - 07:44 AM">
naam, <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1259-iterate-over-csv-files-to-change-flat-file-dataset/'>here's a devshare</a> that iterates over csv files you might want to take a look at. 
 
It uses the following in to get the CSV 
 
open() 
<pre class='_prettyXprint _lang-auto _linenums:0'>
importPackage(Packages.java.io);
directory = new File(params["fileDirectory"]);
files = directory.listFiles();
csvfiles = [];
for( i=0; i<files.length; i++ ){
if( files[i].toString().endsWith(".csv") ){
csvfiles.push(files[i].getName());
}

}
idx=0;

</pre>
 
fetch() 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if( idx < csvfiles.length ){
row["filename"] = csvfiles[idx];
idx++;
return true;
}else{
return false;
}
</pre></blockquote>