Results 1 to 4 of 4

Thread: Macro to convert data

  1. #1

    Macro to convert data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by unwantedoz View Post
    Any help would be appreciated.
    Help already received at chandoo.

    cross posted:
    http://www.mrexcel.com/forum/excel-q...vert-data.html
    http://www.msofficeforums.com/excel-...vert-data.html
    http://forum.chandoo.org/threads/mac...rt-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

  3. #3
    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

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    375
    Articles
    0
    Excel Version
    2020
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •