Excel Import

Import - Using Excel Spread Sheet

Read this entire section before beginning any import from Excel into TAC.

Make sure you review the Warning Section below before importing site and customer records.

You can import site and customer records into the TAC system either one site at a time or all sites contained in an Excel spread sheet.

Steps for populating TAC site and customer records from an Excel spread sheet:

Create spread sheet, populating each row with a service location (site). The spread sheet must be set up as described in the section below Spread Sheet Specifications.
Make a backup copy of the data base prior to importing the data. See backup of TAC data in help guide.
Run import utility File\Import from th TAC Main Menu. Select the Pick File button and open the Excel spread sheet. This populates a grid display of the spread sheet.
Importing the information can be done either one at a time or all sites.

One at a time: Select the site by clicking on the row in the grid. This will populate the screen fields form the sheet. Now click the Import Site button and the site has been created.
All sites: Select the Import Sites button and all sites will be imported into the TAC system.

Verify import by printing the spread sheet and then printing the site or customer reports and verifying the entries and associated balances.

Warning

The Excel import does not check for duplicates sites. For each line in the Excel spread sheet, it creates one customer record and one site record along with opening balances for current, d30, d60 and d90 time periods. If the account number is already on file, the customer record will not be added, but the site will with its associated opening balance invoices.

If you run the import more then once against the same spread sheet duplicate sites will be added and have to be removed individually or restore the files from the backup and repeat the import.

If the account code is left blank, the TAC system will automatically generate a new code based upon the first 10 printable characters of the billing name. Only one customer record is created for each unique customer code.

Spread Sheet Specifications

All columns are general except column “rem_date” with is a date format. The columns of the Excel spread sheet must be as in the table below. A sample of the spread sheet is contained in the system reports folder located as follows:

C:\Program files\Clear Computing, Inc.\TAC 2004\Data\report

C:\Program files\Clear Computing, Inc.\TAC 2004\DataSample\report

A second copy is made on your PC every time the TAC program is run, and is located in the folder location:

C:\Documents and Setting\All Users\Application Data\Clear Computing\report\letterhead

Col	Field Name	Description
A	Account	1-10 character customer or account code
B	Market	1- 10 character code to report sales activity
C	Billname	35 character billing name
D	Biilladdr1	Billing address line 1 / Attention to
E	Billaddr2	Billing Street address
F	Billstate	Billing State or province
G	Billcity	Billing city
H	Billzip	Billing zip or postal code
I	Billphone	Billing phone
J	Billfax	Billing fax
K	Billcontact	Billing contact name
L	Sitename	Service (Site) name
M	Siteaddr1	Service address line 1/ Attention to
N	Siteaddr2	Service address line
O	Sitecity	Service city
P	Sitestate	Service state or province
Q	Sitezip	Service zip code
R	Sitephone	Service phone
S	Sitefax	Service fax
T	Sitecontact	Service contact
U	Totaldue	Total of money due
V	Current	Portion of total due in last 30 days
W	D30	Portion of total due from 32 to 60 days old
X	D60	Portion of total due from 60 to 90 days
Y	D90	Portion of total due from 90+ days
Z	Rem_date	Reminder date in format mm/dd/yyyy
AA	Rem_intyr	Reminder interval in years (decimal)
AB	Directions	Direction to service location
AC	Notes	Notes about the customer
AD	Taxrate	Sales tax rate in percent
AE	Taxexempt	Tax exempt number
AF	Division	Division code, If blank defaults to user division code
		Version 2.0 equipment fields – if all blank no equipment added
AG	note	Free text 2000 words describing equipment
AH	serial	Serial number if blank used site number
AI	type	Type code from reference file 07 if blank “UNKN”
AJ	hose	Text filed length hose needed
AK	size	Size of equipment i.e. 1000
AL	cover	Text description of cover 10 characters)
AM	condition	Text description 60 characters
AN	location	Text description 60 characters
AO	depth	Text description of depth if buried
AP	Status	10 character status code (uppercase)
AQ	Salecredit	10 character code for commission assignment (upper cae)
AR	Township	10 character code for township (uppercase)