You can export any data from Abtrac to Microsoft Excel for analysis via the ‘Reports’ screen.
In some instances, for commonly exported screens and reports, the data is ‘pre-cleaned’ and will export in a similar format to the report preview.
In other instances, the export may result in ‘messy’ data, where the formatting is not as expected. This export format depends on the report data, your version of Abtrac, and your version of Microsoft.
You may need to ‘clean up’ the exported file in order to remove blanks and groupings from the original report. Read on for tips on using MS Excel to tidy up your exported information, and how to export data from reports and other screens in both AbtracOnLine and Abtrac5.
To export data from reports in Abtrac5 from Performance Reports choose > Print Preview:
The Preview ribbon for all reports includes an option to export to Excel, Word or Pdf.
Custom Excel Export
Some Reports and Screens in Abtrac5 will contain an Export to Excel button, where formatting to excel has been completed behind the scenes.
From Performance Reports and other screens look for this blue Export option to export directly to Excel:
In some cases you’ll see an option to preview the export file and can Save As from there, in others the file will save by default to either your C:\Program Files\Abtrac folder for a 32 Bit machine, or C:\Program Files (x86)\Abtrac for 64 Bit.
From Performance Reports > Export Data you can access Abtrac5 data from the database tables directly.
You can choose to export data directly from the database into a range of file formats: .xls, .xlsx, .html, .pdf, .rtf, .txt, .xml, .xps.
Please Note: Excel formats are easiest for manipulation of data, however for large data files the export may fail if there are more records in Abtrac than there are lines in your version of Excel.
- If your Abtrac5 license has expired then you can still access your data via this screen at any time, however the rest of Abtrac will remain locked until your subscription is renewed.
- Likewise, if the datafile has been tampered with on your server in any way then system will also lock and revert to this screen.
To export data from Reports in AbtracOnLine
Go to Report >> Performance Reports >> select a report and choose the dates required.
Click on the ‘Report Options’ button and select ‘Preview with Export Options’.
From the pop up that appears, select your preferences and click on the print button. The below page will appear. Here you can select from the dropdown menu for the ‘Export To’ icon as indicated below.
The Preview ribbon for all reports includes an option to export to Excel – (.xls, .xlsx, .csv) Word (as .rtf, and .txt), Pdf and other formats.
The export file will download automatically, usually to your downloads folder.
Some tables(grid views) in AbtracOnLine contain an Export button, where the formatting to .csv has been completed behind the scenes.
Look for this option to export directly.
Go to Reports >> Performance Reports >> select ‘All’ >> select ‘Bulk Edit Timesheet’ in the ‘Timesheet Entries’ section >> click on the ‘Print Preview’ button after selecting the required dates, preferences, etc.
This will take you to the ‘Timesheet Maintenance’ page, from where you can directly export the data.
Because AbtracOnLine is a cloud application, it is not possible to export from the database tables directly as you can in Abtrac5.
The AbtracOnLine backup procedures and security restrictions protect the online database, they also prevent exporting a full backup of your data.
However, as detailed above, you can export individual screens and reports to obtain copies of your data.
If you need to export files as part of a change to your system, or are downloading your site data for backup it is best to talk with the support team first. We’ll recommend a list of key items and reports to export, and discuss whether any charges will apply (that’s Section 4.c here in the terms)
In the long term, we are considering providing an option which would allow users to access historic data online, something like a read-only version of your data at a reduced monthly fee.
We’re also considering developing an add-on module which would provide you with monthly copies (.csv or .txt) of key information. But we are not able to proceed with this until we are confident your data (and that of others using AbtracOnLine) will remain secure. (If you are using online accounting software like Xero or MYOB, you will notice that they are also reluctant to provide you backup copies – for the same reasons.) As online security restrictions and technologies change we will certainly adapt AbtracOnLine to suit.
If the data exported to Excel is not as expected then try these tips:
If the layout is not as expected when exporting to Excel from Abtrac5, then try:
- exporting the file to Microsoft Word first
- highlighting the table and then
- copying(Ctrl+C) and pasting (Ctrl+P)* into an Excel spreadsheet
*Or instead try pasting as values (Ctrl+V).
- Alternately exporting to .CSV initally and then saving as .xls or .xslx can provide a cleaner result.
If your report is grouping, or inserting blank lines when exporting from AbtracOnLine to Excel, then try:
- Highlighting the exported data and pasting as values (Ctrl+V) into a new sheet
- Once it is done, check the date, decimal place formatting etc.
- You will also want to check for any columns with ‘null data’, i.e. cells that are blank in the report rather than displaying a 0.00 balance.
Once you have made these manual checks and saved the file…
- From Excel choose the Home tab, click Find & Select button in the Editing section.
- Click on Go To Special.
- Click the radio button for Blanks and click OK.
- Now, with the blank cells highlighted, click Delete in the Cells section of the Home tab, and select Delete Sheet Rows. (If you need to delete blank columns, select Delete Sheet Columns.)
Depending on the size of the workbook/report this data clean up could take up to 60 seconds. Remember to check your report totals against the AbtracOnLine Print Preview to ensure the clean up has completed with no errors.
Abtrac KB# 2058