XML in Excel using API of Zoho CRM

[rycliff]

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:

uri	                               no	FL                    	val
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	LEADID
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	SMOWNERID
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Lead Owner
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	First Name
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Last Name
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Phone
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Lead Status
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	SMCREATORID
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Created By
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	MODIFIEDBY
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Modified By
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Created Time
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Modified Time
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Description
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Email Opt Out
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Practice Area
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	1 Source Type
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	2 Source
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Location of Incident
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Attorney We Referred to
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Date Created
/crm/private/xml/Leads/getCVRecords	1	*the actual data*	Date Appointment Scheduled
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	LEADID
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	SMOWNERID
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Lead Owner
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	First Name
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Last Name
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Phone
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Lead Status
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	SMCREATORID
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Created By
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	MODIFIEDBY
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Modified By
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Created Time
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Modified Time
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Description
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Email Opt Out
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Practice Area
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	1 Source Type
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	2 Source
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Location of Incident
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Attorney We Referred to
/crm/private/xml/Leads/getCVRecords	2	*the actual data*	Date Created
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	LEADID
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	SMOWNERID
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Lead Owner
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	First Name
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Last Name
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Phone
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Lead Status
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	SMCREATORID
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Created By
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	MODIFIEDBY
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Modified By
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Created Time
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Modified Time
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Email Opt Out
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Practice Area
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	1 Source Type
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	2 Source
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Location of Incident
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Attorney We Referred to
/crm/private/xml/Leads/getCVRecords	3	*the actual data*	Date Created
/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.

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

 

[Norie]

You could probably just copy the header column and paste+tranpose it somewhere.

Then do the same for the data column, pasting it under the pasted header column.

That could be done manually, if you have a lot of records you could use code but I think more information to help with that.

There is another potential option - find an add-in that will handle files in XML format and 'convert' them to Excel or CSV files.

 

[rycliff]

Thanks Norie for the response. 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 want 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?

 

[rycliff]

Does anyone know the solution or can they point me in the right direction? Is there an XML-based solution?

A non-VBA, Excel-based idea I had was to create dynamic named ranges that capture every Nth row in the data column. That way, I could highlight every row of the same data. I could then array-enter each named range in the appropriate column of a separate table and have the data populate that way.

I haven't tried this yet. What do you guys think?