Hi All,
I have csv file which is comma seperated having data like :
CSV FILE:
121,11852,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000","Unauthorized Trading (e.g. Violating guidelines, Hiding losses, Securities Valuation)",1,
121,11853,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000",Fraud and Collusion,2,
121,11854,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000",Embezzlement,3,
In this file for few columns actual data contains comma e.g. column no 9,10 in first row. ("100,000,000","Unauthorized Trading (e.g. Violating guidelines, Hiding losses, Securities Valuation)").
For columns where actual data contains comma those columns are prefixed and suffixed by double quote in csv (" ").
I want to replace commas which comes in between double quotes with semicolon. (In short where actual column data contains comma i want it to replace with semicolon) and also those double quotes should get deleted afterwards.
Output should be like this where comma should be only file delimiter:
121,11852,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000,Unauthorized Trading (e.g. Violating guidelines; Hiding losses; Securities Valuation),1,
121,11853,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000,Fraud and Collusion,2,
121,11854,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000",Embezzlement,3,
Please reply