Open .atomsvc files with Power BI

[Anonymous]

Hello guys,

I have some problems with my power BI, I can't connect my datas.

I have some .atomsvc files, when i want to connect 1 table I click on "Flux ODATA" and put the URL of the file ( like C://mydisk/table.atomsvc ), power BI tells me that I must put a link who begin with https or http but I dont know how to make it.

So I am using Power Pivot, I have my tables and their are well updated (when I update I have some other lines so it's OK).

I connect my excel/power pivot to power Bi with '' Import > Power Query, Power Pivot, Power View ''.

My tables are now in my Power BI but when I try to update them, I have an error " The URL must be an URL in http or https ".

I dont know how to made to have my tables well connected in my Power BI rapport, can you help me ?

Thanks a lot !

Menez

[thelvyn78]

This is what worked for me using Power Query to pull into Power BI:

1. Download your .atomsvc file.  Open it in notepad and find the part that says href=http://<some_long_url>

2. Copy the url out to a new notepad without the href= and without the quotes "

3. Replace the following:

&amp; with &

note the ; is part of what is being replaced.

Replace %3A with :

Replace Format=ATOM with Format=CSV

In powerquery, create a new query and select Web.

Use the new url you created. 

Select type of authentication, if needed.

Transform your data as normal.

Hope this helps someone.

[davidataylor]

This is gold.  I've been wanting to do this for years and this is the first solution I've ever seen.  Thanks for posting.

[martibo]

Thank you very much for sharing this, it helped me a lot!

Do you know what the impact is from using this method on time formatting? Currently I have the problem that my time formatting is messed up.

When I read the data via ATOM format, I get for example 01/01/2023 14:50:23. But when I transform it to CSV (with the method mentioned above), I get as output 01/01/2023 02:50:23. Without any AM or PM information. Do you know how to solve this?

[aanderson1]

This helped me this morning - thank you! Worked like a charm.

I was previously attempting a scheduled "subscribed" report from SQL Server Reporting Services" but the file path does not permit Windows File Share access to the folder. I work at a big company so access is locked down on many things, trying to bypass that, but this worked perfectly for what I needed to accomplish.

The data connection and refresh is rather slow. Compartively speaking, I could run the report, export the CSV and refresh the data in my Power BI within 5 minutes compared to the almost 15 minutes it took for the file to load - however, once I update my connections/formulas to reference the Data Feed dataset, I'll have the dashboard on an automated refresh cycle while I'm sleeping and will be able to put a few extra minutes back into each day. That's close to 20 hours a year that can be saved for me. Time to take a couple days off of work now!

[Syndicate_Admin]

Hello I just figured this out today since I havent seen an update on this post I wanted to share how I did it. 

1. Get your link from the href.
2. Replace all & with & and %3A with :in the url
3. Change ATOM to CSV
4.  Press Get Data, and then select the "Web" Connector
5. Place your url in the input box
6. You might get a credentials pop up. Use whichever credential is most appropriate.
7. And then your done.

[Anonymous]

Is there a way to connect Power BI and Power Pivot datas ? I cant actualise the data in Power Bi because they say that it's not an http link. It's strange because I can actualize these datas with Power Pivot ... Do you have an idea ? Thanks 😁

[V-pazhen-msft]

@Anonymous 

"If you do not have an application on your computer that can open a report feed, save the document for future use on a computer that has Power Pivot in Excel. The document that you save specifies an address to the report. It does not contain data from the report.

1. Open a report from Report Manager, SharePoint, or a report server.

2. On the report toolbar, click the Export as Data Feed button: 

3. Click Save to store the .atomsvc file on your computer. The file specifies the report server and location of the report file.

To use the .atomsvc file later, you can open it in Power Pivot in Excel to import the report feed. For more information about how to specify a data service document for report feeds, see Import report data using a URL to a data service document in this topic.

You can also publish this file to a data feed library on SharePoint to make it available to anyone who wants to use report feeds in other workbooks or reports. For more information about data feed libraries, see Power Pivot Data Feeds on MSDN."

Reference:
Import Data from a Reporting Services Report - Excel (microsoft.com)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello,

I already import my atomsvc files with Power Pivot and it works, but now i have to put these datas into Power Bi.

[mahoneypat]

What is your atomsvc file connecting to?  If SharePoint list or another source, can you use that connector in Power BI instead?  If not, an atomsvc file is an xml file, you perhaps you can use that connector and parse out the url, and then pass that into a Web.Contents function?

Pat

[Anonymous]

Hello Pat !

My .atomsvc is connected on a local server, i can't use the file with power BI because they tells me to put an http file. Can you tell me how to convert an .atomsvc file into a xml file ? 

I dont know how to cut the url of the .atomsvc file, here is the link when I open it in text : 

<?xml version="1.0" encoding="utf-8" standalone="yes"?><service xmlns:atom="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app"><workspace><atom:title>Suivi Non Stop</atom:title><collection href="http://smntlvdsql12/ReportServer_CRMDB?%2FALL%2FRapports%20CAMER%2FProduction%2FLocal%2FSuivi%20Non%..."><atom:title>table1</atom:title></collection></workspace></service>

Can you tell me what part i must use ?

Thanks a lot !

Menez

[Anonymous]

Any help, even small, would be highly appreciated 😁