Skip to main content

How To Connect Sage 100 to Excel

Pull data directly from Sage 100 into Microsoft Excel for business intelligence analysis without any 3rd Party Add-Ins.


By: Tekoa Software
Tekoa ERP


Sage 100 ERP MAS90 MAS200 data can easily be pulled directly into Excel.  Excel can perform this functionality without any third-party add-ins or tools.  Use the Excel built-in data tools to pull live Sage 100 ERP data into a spreadsheet, then analyzed the data and write reports using Excel.

This allows you to use Excel to:
  • Create Pivot Tables of Sage MAS data
  • Quickly Filter and Sort Sage 100 data
  • Easily Analyze data
  • Build Excel Worksheets that reference live Sage 100 ERP data
1. Open a new Excel spreadsheet and go to the Data tab.  Select the From Other Sources dropdown and select From Data Connection Wizard.


2. Select ODBC DSN.


3. Select SOTAMAS90, which is the Sage 100 ERP ODBC driver name.



4. Login to Sage 100 using credentials that have access to the ODBC files you want.


5. Select any Sage 100 datafile name as the database you want.

6. Uncheck "Connect to a specific table:" and click Next.

7. Click Finish

8. The Select Table window should open.  Select the Sage 100 table you want to pull into Excel.

9. At Import Data window, accept the defaults and click 'OK'.


10. Your Sage 100 ERP data for the table you selected will appear inside Excel.

Use the data in the spreadsheet as a source for analysis and reports written on other Excel sheets. 

To refresh the spreadsheet data, click the Refresh button on the Excel Data tab.


Couple tips...  First, in older versions of Sage 100 MAS90 MAS200, the ODBC driver is 32-bit only.  Which means this will only work if you are using a 32-bit version of Excel.  In newer versions of Sage 100, there is a 64-bit ODBC driver that will work with a 64-bit Excel.  Just keep in mind that if you have an older version of MAS, you will need a 32-bit version of Excel. 

Second, using Microsoft Query, you can customize the query that Excel pulls in if you want to link Sage 100 tables together and get more interesting datasets. 

Last, this method is great for bringing transaction history into Excel and then doing pivot tables and other detailed analysis on the data.

For more information about connecting Sage 100 to Excel, Web, SharePoint and SQL, contact Tekoa Software - ERP software for today's enterprise.



Labels:

Comments

Post a Comment

Popular posts from this blog

Digital Tools and Digital Practices for Success

It has become somewhat obvious in recent years that technology is essential for the advancement of businesses. However, what's the best way to advance? A study recently conducted by Google and Deloitte Surveying, affectionately called, "The Study," found that online advertising and digital business tools have done a lot to improve the productivity of small business across the globe. In most cases the tools increased productivity by 300% . Deloitte surveyed businesses that implement Facebook, Twitter, YouTube, Instagram, and Pinterest to see if there was any difference in their productivity. This entailed the social media side of the survey. The other side of the survey analyzed the effects of things such as websites, analytics, online marketing, cloud-based communication, and other digital channels. Before diving into Deloitte's study, it would be a good idea to separate the alternate realities of social media and digital tools. According to the Meriam Webste
Post a Comment
Read more

EDI Revolution in Business Processes

Shopping now as opposed to thirty years ago is quite different. Today we have eCommerce stores (online stores), and many specialized outlets for products. But with all this input, what are some ways that your customer's overall experience can improve? One way is the use of EDI , (Electronic Data Interchange), which is an electronic communication method that provides standards for exchanging data through any electronic means. In the context of inventory, sales data can be exchanged between companies. By adhering to the same standard, two different companies or organizations, even in two different countries, can electronically exchange documents (such as purchase orders, invoices, shipping notices, etc.). EDI has existed for more than 30 years, and there are many EDI standards, some of which address the needs of specific industries or regions. It also refers specifically to a family of standards. In 1996, the National Institute of Standards and Technology defined electronic data
Post a Comment
Read more