Import/Convert into excel sheet..!!!

**MTHassan** · 11-09-2010, 04:00 PM

How to convert below values into excel worksheet cell (column & row) by using excel macro.. like the below...plz help....

{data:[
{"compname":"1STBSRS","sector":"Investment","cat2":"DA","nav":139.55,"fvml":"100/50","lagm":"2010-04-01","ldiv":"15% : 609"},
{"compname":"1STICB","sector":"Investment","cat2":"DA","nav":3217.58,"fvml":"100/5","lagm":"2010-08-12","ldiv":"400% : 610"},
{"compname":"1JANATAMF","sector":"Investment","cat2":"DA","nav":.00,"fvml":"10/500","lagm":"2010-11-09","ldiv":" : "},
{"compname":"2NDICB","sector":"Investment","cat2":"DA","nav":480.33,"fvml":"100/5","lagm":"2010-08-12","ldiv":"200% : 609"},
{"compname":"3RDICB","sector":"Investment","cat2":"DA","nav":415.05,"fvml":"100/5","lagm":"2010-08-12","ldiv":"140% : 609"},
{"compname":"4THICB","sector":"Investment","cat2":"DA","nav":586.36,"fvml":"100/10","lagm":"2010-08-12","ldiv":"125% : 609"},
{"compname":"5THICB","sector":"Investment","cat2":"DA","nav":508.06,"fvml":"100/10","lagm":"2010-08-12","ldiv":"100% : 609"},
{"compname":"6THICB","sector":"Investment","cat2":"DA","nav":196.03,"fvml":"100/10","lagm":"2010-08-12","ldiv":"75% : 609"},
{"compname":"7THICB","sector":"Investment","cat2":"DA","nav":271.70,"fvml":"100/50","lagm":"2010-08-12","ldiv":"70% : 609"},
{"compname":"8THICB","sector":"Investment","cat2":"DA","nav":215.35,"fvml":"100/50","lagm":"2010-08-12","ldiv":"65% : 609"},
{"compname":"ABBANK","sector":"Bank","cat2":"DA","nav":298.68,"fvml":"100/5","lagm":"2010-07-15","ldiv":"20%, 25%B : 1209"},
],recordType : 'object'}

Excel sheet:

A B C D E F G
1 compname sector cat2 nav fvml lagm ldiv
2 1STBSRS Investment DA 139.55 100/50 40269 0.15
3 1STICB Investment DA 3217.58 100/5 40402 4

**TonyB51** · 11-09-2010, 09:24 PM

Hi,

A very basic way is to use 2 functions.

"Data" - "Text to Columns", using "," as a delimiter.

Plus use Control H to Replace All unwanted data in columns.

Then just modify to suit.

Cheers

Tony

**MTHassan** · 11-10-2010, 01:22 AM

"Data" - "Text to Columns" is not helping properly...
I need a macro to convert it to column...

**arthurbr** · 11-10-2010, 03:46 AM

Record a macro using Data - text to columns

**TMS** · 11-10-2010, 05:10 AM

You need to use two delimiters; put a tick in the box for comma and then, in other, put ":" without the quote marks.

If you output to the next column, that is, if your data is in column then output to column B, you will not overwrite the data at this stage ... that means you can redo it if you need to.

You can also, in the dialogue, select alternate columns and skip the importation of them.

Record the actions you take and you will have a raw VBA macro which you can tweak.

Regards