I am exporting an excel file into a csv text and trying to parse this data using delimited flatfile reader. The file is , delimited (dumped from excel using the save as feature).
One of the string inside has , by itself as it is a long comment string.
I need an optional way of specifying the fact that these strings are within double quotes ie "STRING" and STRING itself will contain some commas which should be skipped.
e.g. orderid, comment, itemname are the three fields
File is delimited by ,
comment in some cases is within comma and double quotes (when there is a comment which contains comma) or it is delimited just by comma from orderid.
I am wondering whether the format parameter for fields will help here.
Any pointers will be greatly appreciated.
thanks
mohan s.
One of the string inside has , by itself as it is a long comment string.
I need an optional way of specifying the fact that these strings are within double quotes ie "STRING" and STRING itself will contain some commas which should be skipped.
e.g. orderid, comment, itemname are the three fields
File is delimited by ,
comment in some cases is within comma and double quotes (when there is a comment which contains comma) or it is delimited just by comma from orderid.
I am wondering whether the format parameter for fields will help here.
Any pointers will be greatly appreciated.
thanks
mohan s.
-
Hello,
delimited reader defines only line and field delimiter, more complicated structure isn't possible. To be able to skip some substring you need append a reformat component.
Try something like this:
<Node id="REF" type="REFORMAT">
<attr name="transform">
import org.jetel.*;
public class reformatOrders extends DataRecordTransform{
DataRecord source,target;
int counter;
String message;
public boolean transform(DataRecord[] _source, DataRecord[] _target){
source=_source[0]; target=_target[0];
try{
SetVal.setInt(target,"orderid",GetVal.getInt(source,"orderid"));
message = GetVal.getString(source,"messagecomment")
// some operation over message
SetVal.setString(target,"messagecomment",message);
}catch(Exception ex){
message=ex.getMessage()+" ->occured with record :"+counter;
throw new RuntimeException(message);
}
counter++;
return true;
}
}
</attr>
</Node>
ja -
Hi Mukunku,
I have noticed your post concerning this topic and it appears that this thread is more than 10 years old. The good news is that Clover has evolved since then and the functionality that handles RFC 4180 compliant CSV files is now there. Feel free to review the attached example graph as a demonstration of how this can be approached. Worth noting is that FlatFileReader is capable of extracting the metadata automatically with no additional manual changes needed. Hope this helps.
Kind regards,
Please sign in to leave a comment.
Comments 4