Welcome to WebmasterWorld Guest from 107.20.54.98

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Convert comma format Numbers in CSV data

Convert numerals in csv

     
8:49 am on Nov 18, 2013 (gmt 0)

Full Member

5+ Year Member

joined:Aug 13, 2007
posts: 217
votes: 0


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]

10:01 am on Nov 18, 2013 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lucy24 is a WebmasterWorld Top Contributor of All Time Top Contributors Of The Month

joined:Apr 9, 2011
posts:12696
votes: 244


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
8:33 pm on Nov 19, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3123
votes: 0


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" ?
10:01 pm on Nov 19, 2013 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lucy24 is a WebmasterWorld Top Contributor of All Time Top Contributors Of The Month

joined:Apr 9, 2011
posts:12696
votes: 244


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.
11:21 pm on Nov 19, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3123
votes: 0


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].
11:31 pm on Nov 19, 2013 (gmt 0)

Senior Member

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 7, 2003
posts:4783
votes: 0


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

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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members