Opening a Saved Tab-Delimited file in a spreadsheet program

A number of screens in STP Creator allow you to export to a tab delimited file. Others allow you to Copy the table contents and paste them into a text file (such as used by NotePad). These text files will also be tab-delimited.

The purpose of these features is to enable you to view and work with the data in a spreadsheet. This allows you to do your own visual review of the data, sorting and checking of totals etc, or editing for re-import.

The Payroll Categories screen for example allows you to export all Payroll Categories to a tab-delimited text file. This can be useful when a large number of records need to be updated, and then imported.

Warning:

Opening a tab-delimited file

When a spreadsheet opens a tab-delimited text file, any field that contains numbers only may be interpreted as a number and leading zeroes will be dropped. For example the Phone field is usually a 10 digit numeric field, something like "0812341234". Without special precautions, when you open a text file with this field in a spreadsheet program, that program may interpret this field as a number, in which case it will drop the leading 0 and display 812341234.  The same thing can happen to the ABN field (if using 00000000000), as well as the postcode field.

To prevent this from happening, proceed as follows:

(Note - do not right-click on the tab-delimited file and Open-With the spreadsheet program.)

Step 1

Open the spreadsheet program first. We will use Excel in this example.

Step 2

Select File Open and navigate to the folder where the tab-delimited file is stored. (in our example the file is called PayeeExport.txt)

Step 3

On the "Files of Type" drop-down list choose, Text Files (*.prn; *.txt; *.csv). PayeeExport.txt will now be visible in the File Open dialog, select and click Open.

Step 4

The next screen will be a Step 1 of the Text Import Wizard. Select Delimited as the Original Data Type and click the Next Button.

Step 5

On the Step 2 Screen choose Tab as the Delimiter and click Next.

Step 6

On the Step 3 Screen, all columns by default have a data format of General. Select all the columns (one at a time) that are likely to have leading zeros and change their format to Text. The likely columns are ABN, PostCode, and Phone.

Step 7

Click Finish.

The file will now open with leading zeroes preserved.