Pull and format RSS feed into Excel

I want to pull an RSS feed and process it in Excel VBA, and provide the client the format that they need.

The RSS feed is a text feed and I'm not sure how to get it to format into columns and rows. It appears to use colons for delimiters, but i can't get it to replicate what the client did, pulling it into an Excel worksheet.

The feed is at https://regencydev.adeptia.com:443/adeptia/wsapi/rest/MarketingDataFeed/GetData?User=ShopCtr.

The format I want is like in the attached file, which is a sample..
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
The problem is that there are multiple delimiters in the feed, namely "[", "{","," and ":"

So you need to split by each in turn, while also avoiding splitting URLs at their colon.

The attached file does more or less what you want, I think.

It pulls the response from the feed and stores it in the "Raw response" worksheet for convenience. It then calls routine "ParseIt".

If you are developing/changing the code, therefore, it is more convenient JUST to call the ParseIt routine, which will use whatever is stored in the raw response sheet. Downloading the RSS seems to take about 20 seconds (at least from here).

The ParseIt routine strips out unwanted characters, replaces the colons in urls with temporary "~" characters, progressively splits the response into chunks and puts each in its own line, putting the colons back in the urls.

It seems to me that it might be better to organise the file at a "property" level, then a "unit" level, however, using indententation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougfosterNYCAuthor Commented:
Neil, you ROCK!  Thanks so much.  Yes, there are multiple delimiters.  Your parsing routine works perfectly...

Thanks again.
dougfosterNYCAuthor Commented:
Hi Neil.  

I have found an issue as I have built the automated routine out.. I may have to open a new request.  Hopefully you'll get this comment..

The routine is getting tricked if there is a comma in the text.  So the store "A.L. HARRELL, III, O.D." is parsing two extra times.  That throws everything out of whack.  

Is there a way to trap for these situations?


dougfosterNYCAuthor Commented:
I did do a work around, by replacing comma and space with an alternate text and then switching it back, like with the URL colons... so far it looks like all commas in text are followed by a space...
Neil FlemingConsultant and developerCommented:
Yes, that's what I'd have suggested. turn them into "~" characters or whatever. It's not 100% foolproof but 99.9% of the time the text comma will have a space after it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.