XML in Excel using API of Zoho CRM

XML in Excel using API of Zoho CRM

Hi,

I'm trying to use Zoho CRM's API to suck data directly into Excel using a web query.  The data comes in XML format.  When I pull the data into Excel, it comes like this:


  1. uri                                                  no FL                           val
  2. /crm/private/xml/Leads/getCVRecords 1 *the actual data* LEADID
  3. /crm/private/xml/Leads/getCVRecords 1 *the actual data* SMOWNERID
  4. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Lead Owner
  5. /crm/private/xml/Leads/getCVRecords 1 *the actual data* First Name
  6. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Last Name
  7. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Phone
  8. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Lead Status
  9. /crm/private/xml/Leads/getCVRecords 1 *the actual data* SMCREATORID
  10. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Created By
  11. /crm/private/xml/Leads/getCVRecords 1 *the actual data* MODIFIEDBY
  12. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Modified By
  13. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Created Time
  14. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Modified Time
  15. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Description
  16. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Email Opt Out
  17. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Practice Area
  18. /crm/private/xml/Leads/getCVRecords 1 *the actual data* 1 Source Type
  19. /crm/private/xml/Leads/getCVRecords 1 *the actual data* 2 Source
  20. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Location of Incident
  21. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Attorney We Referred to
  22. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Date Created
  23. /crm/private/xml/Leads/getCVRecords 1 *the actual data* Date Appointment Scheduled
  24. /crm/private/xml/Leads/getCVRecords 2 *the actual data* LEADID
  25. /crm/private/xml/Leads/getCVRecords 2 *the actual data* SMOWNERID
  26. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Lead Owner
  27. /crm/private/xml/Leads/getCVRecords 2 *the actual data* First Name
  28. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Last Name
  29. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Phone
  30. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Lead Status
  31. /crm/private/xml/Leads/getCVRecords 2 *the actual data* SMCREATORID
  32. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Created By
  33. /crm/private/xml/Leads/getCVRecords 2 *the actual data* MODIFIEDBY
  34. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Modified By
  35. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Created Time
  36. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Modified Time
  37. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Description
  38. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Email Opt Out
  39. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Practice Area
  40. /crm/private/xml/Leads/getCVRecords 2 *the actual data* 1 Source Type
  41. /crm/private/xml/Leads/getCVRecords 2 *the actual data* 2 Source
  42. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Location of Incident
  43. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Attorney We Referred to
  44. /crm/private/xml/Leads/getCVRecords 2 *the actual data* Date Created
  45. /crm/private/xml/Leads/getCVRecords 3 *the actual data* LEADID
  46. /crm/private/xml/Leads/getCVRecords 3 *the actual data* SMOWNERID
  47. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Lead Owner
  48. /crm/private/xml/Leads/getCVRecords 3 *the actual data* First Name
  49. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Last Name
  50. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Phone
  51. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Lead Status
  52. /crm/private/xml/Leads/getCVRecords 3 *the actual data* SMCREATORID
  53. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Created By
  54. /crm/private/xml/Leads/getCVRecords 3 *the actual data* MODIFIEDBY
  55. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Modified By
  56. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Created Time
  57. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Modified Time
  58. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Email Opt Out
  59. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Practice Area
  60. /crm/private/xml/Leads/getCVRecords 3 *the actual data* 1 Source Type
  61. /crm/private/xml/Leads/getCVRecords 3 *the actual data* 2 Source
  62. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Location of Incident
  63. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Attorney We Referred to
  64. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Date Created
  65. /crm/private/xml/Leads/getCVRecords 3 *the actual data* Date Appointment Scheduled


The way I need the data to show in Excel is each "val" value be a column header, and each "FL" value be the actual data below the respective column.  The "no" value represents the corresponding row for each data entry.

I should also note that I am not a programmer and I'm not using VBA to do this.  I go to the Data tab, select "From Web," and insert my API request that way.

 I need a solution that's automatic, not manual. I need to be able to refresh the XML web query and it come automatically properly formed. I don't know much about XML, but is there an XML schema, "transformation" or something that is meant to solve problems like this?

I need this data query to behave like a database query where you just hit the refresh button and get updated data.

Isn't there an XML-based solution to properly form the data that does not involve a VBA workaround?

Anyone know how to do this?  Please help as this has caused me endless frustration.  Thank you!