Macro to convert data

U

unwantedoz

New member
Joined
Oct 16, 2015
Messages
2
Reaction score
0
Points
0
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.
 
unwantedoz said:
Any help would be appreciated.
Click to expand...
Help already received at chandoo.

cross posted:
http://www.mrexcel.com/forum/excel-questions/894751-macro-convert-data.html
http://www.msofficeforums.com/excel-programming/28258-macro-convert-data.html
http://forum.chandoo.org/threads/macro-to-convert-data.26066/

unwantedoz, for your information, you should always provide links to your cross posts.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
Yeah i've only just got to checking my posts to see the responses.

A post at excelforum (not enough posts to post the link)also does the job converting it to xml but it puts them all under each other instead of next to each other.

The chandoo response above does the job perfectly :)
 
If it's a csv file
If the fieldseparator is the comma

Code: 
Sub M_snb()
    msgbox = "<set label='" & Replace(Join(Split(CreateObject("scripting.filesystemobject").opentextfile("G:\OF\example.csv").readall, vbCrLf), "' /><set label='"), ",", "' value='") & "' />"
End Sub
 
You must log in or register to reply here.
Back
Top