Convert comma format Numbers in CSV data

Convert numerals in csv

[kkonline]

Hi,
I am parsing some data and have a string of CSV with some numerals separated by comma.

Incase I split the csv based on the delimiter the numeral also gets affected.

String has data

 {futLink:\/live_market\/dynaContent\/live_watch\/get_quote\/GetQuoteFO.jsp?
 underlying=ICICIBANK&instrument=FUTSTK&expiry=28NOV2013&type=-&strike=-,
 otherSeries:[EQ],lastUpdateTime:18-NOV-2013 14:13:01,tradedDate:18NOV2013,data:
 [{extremeLossMargin:5.00,cm_ffm:1,21,433.11,bcStartDate:01-JUN-13,change:21.90,
 buyQuantity3:50,sellPrice1:1,074.00,buyQuantity4:25,
 sellPrice2:1,074.10,priceBand:No Band,buyQuantity1:5,deliveryQuantity:16,55,067,
 buyQuantity2:254,cm_adj_low:756.90,
 sellPrice5:1,074.30,quantityTraded:33,09,424,buyQuantity5:26,sellPrice3:1,074.15,
 sellPrice4:1,074.25,open:1,063.00,cm_adj_high:1,238.40,low52:756.90,
 securityVar:9.09,marketType:N,pricebandupper:1,157.30,totalTradedValue:25,674.10,
 faceValue:10.00,ndStartDate:-,previousClose:1,052.10,symbol:ICICIBANK,
 varMargin:9.09,lastPrice:1,074.00,pChange:2.08,adhocMargin:-,
 companyName:ICICI Bank Limited,averagePrice:1,077.13,secDate:14NOV2013,series:EQ,
 isinCode:INE090A01013,indexVar:-,pricebandlower:946.90,totalBuyQuantity:98,
 929,high52:1,238.40,
 purpose:ANNUAL GENERAL MEETING\/DIVIDEND RS.20\/- PER SHARE,cm_adj_low_dt:28-AUG-13,
 closePrice:-,recordDate:-,cm_adj_high_dt:28-MAY-13,totalSellQuantity:1,50,824,
 dayHigh:1,087.80,exDate:30-MAY-13,sellQuantity5:24,bcEndDate:24-JUN-13,
 ndEndDate:-,sellQuantity2:101,sellQuantity1:17,buyPrice1:1,073.90,sellQuantity4:166,
 buyPrice2:1,073.85,sellQuantity3:1,applicableMargin:14.09,buyPrice4:1,073.65,
 buyPrice3:1,073.70,buyPrice5:1,073.60,dayLow:1,059.10,deliveryToTradedQuantity:50.01,
 totalTradedVolume:23,83,566}],optLink:\/marketinfo\/sym_map\/symbolMapping.jsp
 ?symbol=ICICIBANK&instrument=-&date=-&segmentLink=17&symbolCount=2}

Where the values like 1,21,433.11 and 1,50,824 should be converted to regular numerals like 121433.11 then can use the string for further splitting.

Could you suggest any php based function or any technique to achieve the same.

[edited by: bill at 2:28 am (utc) on Nov 20, 2013]
[edit reason] fix code side-scroll [/edit]

[lucy24]

Off the top of my head:

First use a Regular Expression to replace all occurrences of
(\d),(\d)
with
$1$2
and proceed from there. Do numerals ever occur at the beginning of a pattern? If not, all you need is
,(\d)
to
$1

[penders]

That does not appear to be CSV data. It's some kind of object literal / serialised data. (If the property values were quoted, it looks as if it would be valid JavaScript?)

Not only do you have a problem with commas in numbers (that lucy24's suggestion should rather neatly solve) but there also appears to be a nested "data" array/object that itself contains commas relating to the fields in the nested object.

,data:[{extremeLossMargin:5.00,cm_ffm:1,21,433.11,bcStartDate:01-JUN-13, .....}],optLink:\/market......

Just curious... whose/what number system formats their numbers in this way: "1,21,433.11" ?

[lucy24]

I had a belated "Oops" as I realized that if numerals ever do occur at the beginning of a record, the Regular Expression I originally gave wouldn't be safe, because there are definitely numerals at the end of records. You would then have to look more closely at exactly what comes before or after
,\d\d\d\b
But if you don't have record-initial numerals, you are good to go.

Just curious... whose/what number system formats their numbers in this way: "1,21,433.11" ?

Those are lakhs. If numbers were bigger you might also see crores "12,34,56,789". I don't know what happens after you pass 10^9; I think it goes back to sets of three.

Edit: I looked it up. ICICI bank is, as expected, in India.

[penders]

At least the data as given appears to consist of

name:value

pairs and the names do at least appear to follow common identifier convention, ie. don't start with a digit.

Those are lakhs.

Ah, thanks for the enlightenment! (I actually have an account with ICICI bank(!), ... but in the UK!)

I don't know what happens after you pass 10^9; I think it goes back to sets of three.

It would seem to continue two by two, according to Wikipedia [en.wikipedia.org].

[swa66]

whose/what number system formats their numbers in this way: "1,21,433.11" ?

The Indian numbering system: [en.wikipedia.org...]