Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
I_NeedMorePower
Helper III
Helper III

Connecting Power BI with Sage 50 Accounting

Hi dears,

 

I'm trying to get the data directly from Sage 50 Accounting, but i'm struggling on finding a way to do that.

I have seen the Pervasive ODBC Engine way but when I did the set up, it was listed in the power bi ODBC connecter but when I tried to connect after providing the username and password, it gave me an error saying:

"The specified DSN contains an archticture mismatch between the driver (32) bit pervasive text engine and application (64) bit"

I did the set up for the ODBC through the following path:

"C:\windows\SysWOW64\odbcac32"

But as I mentioned it gave me the error I typed above.

Any other way to connect to Sage 50 Accounting DB while being free? I don't want to use a paid tool to achieve this.

 

Thanks in advance!

12 REPLIES 12
MartinFT
New Member

There is a reporting tool called XLGL and it can pull a CSV file with every piece of sales data you can think of. He's already done it for me. Every single field that Sage 50 CA tracks is available.  Pierre-Alain can definitely help you. Pierre Alain Carrier <pacarrier@logicimtech.com> 

johndynammics
Regular Visitor

Hi , My simple suggestion is you should try to Microsoft Partner. They are definetly help you.... By the way you can visit us Power BI implementatioin partner

MacLeod
Regular Visitor

Sage 50 and Power Query

Good day:

We are a small Business Intelligence consulting company based in Nova Scotia and have worked with many accounting packages including Sage 50.

The “easiest” way to get data from Sage 50 seems to be an export in CSV format. The older versions of Sage 50 will likely restrict the data download to a year at a time, but this really isn’t a constraint; prior years need only be done once, and the current year will need to be downloaded every time an update is required. The download is quick; saving and renaming the file is painless.

You will also need three additional files: a calendar that contains every date from the earliest Sage record, a list of all Account Codes appearing in the Sage records, and a list of all Project Codes in the Sage records (assuming that you are using project codes). Best practice is to keep these files in a spreadsheet (we usually call it the warehouse file). Missing Accounts and Projects create the dreaded “blank” row in the reports.

Place the Sage exports in a single folder, ensuring that they are the only files in this folder (While Power Query can sort this out, might as well conform to “best practices” from the start.).

The CSV downloads are ugly to look at, but very strict formatting rules are followed by Sage, so the files are quite easy to work with.

Use Power Query to combine and restructure the files for use in a Power Pivot or Power BI Desktop report, adding in the Calendar, the Accounts list, and the Projects list. Our latest client has also added a Budget table (spreadsheets used to calculate Departmental and Project budgets), and a Forecast table (with updates to the Budget on a quarterly basis).

Their reporting requirements are to track Actual expenses by Department and Project versus Budget and Forecast, and track these against prior year actual values.

If you are interested in a sample Power PBI Desktop file (uses fake data, based on a charity), feel free to contact me. We are a consulting company, but if you keep your questions simple enough, I would be happy to give a bit of my time for free.

Dan

v-luwang-msft
Community Support
Community Support

Hi @I_NeedMorePower ,


Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

Hi v-luwang,

Actually I just told them to use excel exports as data sources for now and wait till they migrate to D365. 🤷🏻‍♂️

Is Sage 50 UK the same as Sage 50 Accounting?

SAGE 50 CA and SAGE 50 UK are not the same. Completely different. They just share a marketing name. I don't know what is was called before, but I do know.... 

SAGE 50 US version -> Peachtree accounting. 

SAGE 50 CA version -> Bedford Accounting then Simply Accounting, Then SAGE 50

v-luwang-msft
Community Support
Community Support

Hi @I_NeedMorePower ,

You could connect to Sage 50 UK as an ODBC Data Source with Power BI Desktop.

For more information, please check the links here:

Using Microsoft Power BI with Sage Intelligence Reporting

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

 

I_NeedMorePower
Helper III
Helper III

Hi spartaBI,

 

The ODBC driver I have was installed along with Sage 50 Accounting.

I'm not sure there is a 64 bit version of this driver though 🤔

@I_NeedMorePower hmm..
I actually just took a wild guess.. I think you should contact their support agents.
WDYT?

The bit depth of the OBDC driver, Excel or PowerBI and Sage50 must be the same.

Since you cannot simply choos the bit-depth for SAGE50, you may have to switch to the 32-bit versions of the others.

Dissappointing in some ways, but I did succesfully connect after changing my O365 and PowerBI Desktop to the 32-bit versions.

This has landed me at the learning curve for the DB Table structure.... Oh! MY!

SpartaBI
Community Champion
Community Champion

@I_NeedMorePower hey, try to download the 64 bit version of the ODBC from where you brough the 32 and also unuinstall the 32 from your computer

BTW, usually ODBC drivers will cost money.


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.