Exporting Abtrac Data and exporting reports to Excel

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

This image has an empty alt attribute; its file name is 2020-10-29_preview-with-export-options.png

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.

Custom Exports

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.

Export 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)

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

This image has an empty alt attribute; its file name is 2020-10-29_excel-delete-cells.png

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