Welcome to WebmasterWorld Guest from 54.147.20.131

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Convert comma format Numbers in CSV data

Convert numerals in csv

     

kkonline

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

5+ Year Member



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

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

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



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

8:33 pm on Nov 19, 2013 (gmt 0)

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



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

10:01 pm on Nov 19, 2013 (gmt 0)

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



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

11:21 pm on Nov 19, 2013 (gmt 0)

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



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

11:31 pm on Nov 19, 2013 (gmt 0)

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



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

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

Featured Threads

Hot Threads This Week

Hot Threads This Month