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 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 AbtracOnLine.
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.
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 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