Excel Import – Site/Customer with Work Orders

Note: Excel file name is “excel import site customer and work order version 1.xls”

 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 spreadsheet:

1. 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.
2. Make a backup copy of the database prior to importing the data. See backup of  TAC data in help guide.
3. Run import utility File\Import from the TAC Main Menu. Select the Pick File button and open the Excel spread sheet.  This populates a grid display of the spread sheet.
4. Importing the information can be done either one at a time or all sites.

1. 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.
2. All sites:  Select the Import Sites button and all sites will be imported into the TAC system.

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

How the Import works

The import checks the following

1.      Check Customer / Billing Entry

B- Column is a 1-10 character customer code.  If the column is populated, the import verifies that it is correct. If not, the import will warn that the customer code is invalid and skips the line.  If the column is empty, the import takes the billing information and adds the customer, assigning it a code. 

A – Column is a 1-6 integer site number.  If populated,  the import checks to see if it is already on file. If on file and site information is populated, then the site is updated. If not on file, no action is taken for the line entry.  If the site number is zero, the site is added with the appropriate customer number.

2.      Site equipment Columns (AH through AP)

If any columns populated, equipment entry is added with appropriate site and serial number.

3.      Work Order Columns (AT through  BH)

AT-Column is the work order number.  If populated and site, customer and work order number are valid, then the work order is inserted. If either the site number, customer number or work order number is wrong (duplicate or associated with another site), the work order is not added.   If the work order number is zero and other associated fields are populated, the system will automatically generate a unique work order number.

Spread Sheet Specifications

All columns are general except any date column i.e,  “rem_date” , wo_schedule_date, which is in 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

Or

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

 The Excel file name is “excel import site customer and work order version 1.xls”

 

Col	Field Name	Description
A	Siteno	Numeric 6
B	Custno	1-10 character customer or account code
C	Market	1- 10 character code to report sales activity
D	Billname	35 character billing name
E	Biilladdr1	Billing address line 1 / Attention to
F	Billaddr2	Billing Street address
G	Billstate	Billing State or province
H	Billcity	Billing city
I	Billzip	Billing zip or postal code
J	Billphone	Billing phone
K	Billfax	Billing fax
L	Billcontact	Billing contact name
M	Sitename	Service (Site) name
N	Siteaddr1	Service address line 1/ Attention to
O	Siteaddr2	Service address line
P	Sitecity	Service city
Q	Sitestate	Service state or province
R	Sitezip	Service zip code
S	Sitephone	Service phone
T	Sitefax	Service fax
U	Sitecontact	Service contact
V	Totaldue	Total of money due
W	Current	Portion of total due in last 30 days
X	D30	Portion of total due from 32 to 60 days old
Y	D60	Portion of total due from 60 to 90 days
Z	D90	Portion of total due from 90+ days
AA	Rem_date	Reminder date in format mm/dd/yyyy
AB	Rem_intyr	Reminder interval in years (decimal)
AC	Directions	Direction to service location
AD	Notes	Notes about the customer
AE	Taxrate	Sales tax rate in percent
AF	Taxexempt	Tax exempt number
AG	Division	Division code, If blank defaults to user division code
		Equipment fields – if all blank no equipment added
AH	note	Free text 2000 words describing equipment
AI	serial	Serial number if blank used site number
AJ	type	Type code from reference file 07 if blank “UNKN”
AK	hose	Text filed length hose needed
AL	size	Size of equipment i.e. 1000
AM	cover	Text description of cover 10 characters)
AN	condition	Text description 60 characters
AO	location	Text description 60 characters
AP	depth	Text description of depth if buried
AQ	Status	10 character status code (uppercase)
AR	Salecredit	10 character code for commission assignment (upper cae)
AS	Township	10 character code for township (uppercase)
		Work order fields – if all blank no entry
AT	Wo_number	Work order number (zero auto-assign)
AU	wo_cod_amount	C.O.D. Amount to be collected
AV	wo_driver_note	Text note for the driver specific to this work order
AW	wo_po_number	Purchase order number for this work order
AX	wo_schedule_date	Schedule date for the work to be completed
AY	wo_type	1-10 character code for type of work order, i.e., D=Delivery
AZ	wo_invoice_note	Text note to transfer to invoice when work order converted to an invoice
BA	wo_UnitType1	1-10 character code for type of unit
BB	wo_unit_quanity1	Integer number of units
BC	wo_UnitType2	1-10 character code for type of unit
BD	wo_unit_quanity2	Integer number of units
BE	wo_UnitType3	1-10 character code for type of unit
BF	wo_unit_quanity3	Integer number of units
BG	wo_UnitType4	1-10 character code for type of unit
BH	wo_unit_quanity4	Integer number of units

 

See Also

Importing from Excel (No Work Orders)  

Reports - Site  

Reports - Customer