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.