Macro to convert data

[unwantedoz]

Hello, I need help making a macro. I spent an hour or two couple of hours googling how to make macros but I still have no idea how to do what I want to.

Anyway I want display a chart on a website. I will be using fusioncharts.js to make it as trying it I have found it really easy to use.

My problem is the data is not in the format it needs. It needs it in either JSON or XML as a single string format.

So the data I record is a csv which looks like this in excel:

Date Cost $
23/08/2015 78
24/08/2015 58
25/08/2015 112
26/08/2015 97

So I need a macro that will convert that simple 2 column data into either of the following:

JSON in the form:

{
"label": "23/08/2015",
"value": "78"
},
{
"label": "24/08/2015",
"value": "59"
},
{
"label": "25/08/2015",
"value": "112"
},
{
"label": "26/08/2015",
"value": "97"
}

or single string XML in the form:

<set label='23/08/2015' value='78' /><set label='24/08/2015' value='59' /><set label='25/08/2015' value='112' /><set label='26/08/2015' value='97' />

Any help would be appreciated.

 

[steve the fish]

You say your data is two column but you have pasted a one column piece of data. Which is it?

 

[unwantedoz]

I pasted a two column sample, there is a space between the date and the number. The one column shown under JSON is how I needed it to look instead of being in two columns.

The macro on this cross post do the job fine

Macro to convert data | Chandoo.org Excel Forums - Become Awesome in Excel