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, 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 excel to tidy your exported information, and how to export data from reports and other screens in both AbtracOnLine and Abtrac5.

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.

Abtrac5 - Print Preview > Export Report

Abtrac5 – Print Preview > Export Report

If the file isn’t exporting as expected, then try these tips to clean-up and reformat your report or data layout.

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:

Abtrac5 - Custom Excel export file

Abtrac5 – Custom Excel export file

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.

Export Data

From Performance Reports > Export Data you can access Abtrac5 data from the database tables directly.

Abtrac5 - Export Data

Abtrac5 – Export Data

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.

AbtracOnline

To export data from reports in AbtracOnLine from Performance Reports choose> Print Preview:

AbtracOnLine - Print Preview > Export Report

AbtracOnLine – Print Preview > Export Report

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.

If the file isn’t exporting as expected, then try these tips to clean-up and reformat your report or data layout.

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:

6_CustomExport

AbtracOnLine – Custom .CSV export file

Export 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’s 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’ll 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’s done check the date, decimal place formatting etc.
  • You’ll 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’ve made these manual checks and saved the file…

Excel_GotoSpecial.png

  • From Excel choose the Home tab, click Find & Select button in the Editing section.
  • Select 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