We start with an example using the General Ledger Journal Transactions screen. This screen is a typical Acumatica data entry screen with summary information at the top of the screen and detail information at the bottom. Nearly all of the detail grids have an “Export to Excel” button on the toolbar above the grid. Just click the button, and the system downloads the data in the grid to Excel on your machine.
Many of the grids also have an “Import from Excel” button. If you first export data from the grid, this will provide the columns you need to populate for your import. Add your data to the Excel workbook and save it. Click the import button. Browse to where you saved the file and upload. Generally, you can just take the defaults on the two popup screens, but if needed, these can be altered for your specific circumstances.
Next, we look at Import and Export Scenarios. The system is built so that every transaction and master table screen can be imported into. Building these import scenarios can take a little practice and training, but they are powerful and the system’s Demo database contain around 40 sample scenarios that work. Import and Export Scenarios can use a variety of data sources. Most often the data source is an Excel workbook. But you can use SQL databases, CSV file, XML files, and some specialized resources that are integrated with other systems. The sample Scenarios come with Excel Templates that simplify building the Scenario.
Reports are a good way to get information from your system. They can be sorted, summarized and polished – ready for presentation. They allow you to filter the information you want by inputting parameters. Every report in Acumatica can be exported to Excel for further analysis.
Run the report, click the Export button and select Excel. The data from the report is downloaded to your system as an Excel workbook.
Generic Inquiries are similar to reports. The demo database contains nearly 200 Generic Inquiries. Almost all the dashboard items you see have a Generic Inquiry at their foundation. Just like reports, every Generic Inquiry can be exported to Excel. When you run a Generic Inquiry, on the toolbar at the top of the grid, click the Excel icon and the system downloads the full results of the Generic Inquiry as an Excel workbook.
Lastly, you can retrieve data into Excel using OData. OData a geeky term that is a standardized protocol for web based systems to share data.
In Acumatica, any Generic Inquiry can be exposed as an OData resource, meaning it can be accessed via a tool that knows how to consume OData, like Excel. Don’t worry about security. The data is encrypted with it goes across the web and the user must log in to get the data in the first place. AND Acumatica security is in place which means a user can only get access to the information they can access based on their security access rights.
In Excel, we build a connection to an OData resource. Open a workbook, go to Data, Get external data from other sources, From OData Data Feed. The Link is the URL to your Acumatica system – appended with “/ODATA”. Enter your regular Acumatica credentials, and click Next. Select the Generic Inquiry you want to get data from and click Finish. Take the defaults on where to import the data and click OK.
If the data has changed in Acumatica, all you need to do to update the data in the spreadsheet is click Refresh All on the Data ribbon.
