Data Import from Other Programs to WizCount

Contents

Introduction

File Format

Accounts Interface

Journal Entries Interface

Bank Statements Interface

Items Interface

Document Interface

Introduction

WizCount furnishes utility programs, which enable data import from other programs.  The data entry is based on an intermediate ASCII format file called Interface which connects between the other programs and WizCount.

WizCount has the capacity for reading data files edited in a variable format.  The variable format, compatible with WizCount, is based upon a Parameters file and enables selective and flexible data transfer.  Through the assistance of the Parameters file, the length of each record in the Interface file, which fields are to be transferred and the location and length of each field in the Interface record are determined.

Back to the top

File Format

Method of Transfer

The Interface file is accompanied by a Parameters file in which the format of the record in the Interface file is defined.

Parameters File

The file name is identical to the Interface name except that it ends with the suffix PRM.  This file is in ASCII format and may be edited through an editor.

In the first line of the file, enter the number that indicates the length of the record in the Interface file.

The record length includes the characters CR+LF that are located at the end of each record.

Starting from the second line onwards, the location of the appropriate field must be entered in the Interface file.  Each Interface will be explained later, as well as which field is appropriate for each line in the Parameters file.

The first number (from the left) indicates the beginning of the field and the second number (on the right), its end.  When the field appropriates for the line in the Parameters file is not transferred in the Interface record, enter two zeroes (0 0) on the appropriate line in the Parameters file.

Remarks may be entered into the Parameters file.  The sign “;” (semicolon) must be entered prior to each remark.  The program ignores everything written to the right of this symbol.

The advantages of the flexible method are:

  • The fields in the Interface file are organized according to the user’s needs.
  • You may execute a partial data transfer from the Interface record.
  • The field length, which is transferred, is flexible.
  • Fields that are not transferred receive the default values of WizCount.  The default values are identical to those in force at the time the data is entered into WizCount.

Fields

A table of fields appears later in the detailed explanation in each Interface; these fields may be transferred through the Interface.

The maximum length and content of each field appears in the table.  Fields longer than the maximum length entered in the table cannot be transferred.

A list of field types that may be transferred in Interface appears below:

Alphanumerical field: In an alphanumeric field, any character combination containing characters with ASCII values higher than 31 may be transferred.

When transferring, WizCount pushes all the field contents to the appropriate side, according to the field type defined in WizCount.

Decimal Field:  In a decimal field, customary numbers with decimal fractions are entered.  WizCount has the capability of reading decimal fields in several forms:

  • A Decimal field without a decimal point:  the program determines the location of the decimal according to the decimal accuracy customary for that field in WizCount.  For example, if the value “1230256” is to be transferred to a quantity field, WizCount converts this number into “1,230.256”.
  • A numeric field with a decimal point:  the location of the decimal point in the Interface file determines the decimal value.  For example, if the value “3456.2” is transferred to a quantity field, WizCount converts this number into “3,456.200”.
  • A decimal field with commas between the figures: WizCount ignores the commas between the figures.
  • Numbers with a positive (+) or negative (-) integer sign: the sign must be to the left of the number, although not necessarily touching it.

Two numbers, which indicate the maximum width of each decimal field, appear in the tables in the explanations for Interface.

The right number indicates the number of figures permitted for transfer to the right of the decimal; the left number indicates the number of figures permitted for transfer to the left of the decimal.

For example, if 6.3 is entered, the number “123,456.789” may be transferred.

Date field:  A date field, which is transferred to WizCount, must be in the DDMMYY format or the DD/MM/YY format.

WizCount checks the legality of this date (to see if it is permissible for transactions, if the date is non-existent, etc.)

When a reference date or a value date is transferred, it must be within the settings defined in the Definitions category in WizCount.

Transfer Procedure

In the process of transferring, WizCount checks for data integrity.  If WizCount finds irregularities in the Interface file, an errors report will be displayed without transferring the records that contain the errors.

The general and unique integrity checks in each Interface will be explained later.

At the end of the transfer, if even one record was transferred to WizCount, the program changes the suffix of the Interface file either from DAT or DOC to BAK.  The name of the Parameters file does not change.

Back to the top

Accounts Interface

The Accounts Interface in WizCount enables the import of accounts from other programs to WizCount.  The Interface enables opening new accounts and updating data in existing accounts.

Interface File Name:  HESHIN.DAT

Parameters File Name:  HESHIN.PRM

Parameters File Format:

 

In the first line, enter the record size in the Interface file.

In Lines 2-49, enter the location of the fields into the record according to the following table:

Fields that can be imported into an Account
Line Field Name Maximum Length Contents Remarks
2 Account Key 15 Alphanumeric
3 Account Name 50 Alphanumeric
4 Category Code long Numeric
5 Cutoff 5 Alphanumeric
6 Telephone 30 Alphanumeric
7 Address 50 Alphanumeric
8 District 15 Alphanumeric
9 City/Town 20 Alphanumeric
10 Post Code 5 Numeric
11 Occupation 15 Alphanumeric
12 Accountant Code 4 Alphanumeric
13 Details 50 Alphanumeric
14 Additional Date 1 8 Date
15 Additional Date 2 8 Date
16 Additional Amount 1 9.2 Decimal
17 Additional Amount 2 9.2 Decimal
18 Additional Amount 3 9.2 Decimal
19 Additional Amount 4 9.2 Decimal
20 Maximum Credit 9.2 Decimal
21 Currency of Maximum Credit 5 Currency As per currency table
22 Maximum Obligation 9.2 Decimal
23 Currency of Maximum Obligation 5 Currency As per currency table
24 % Total Discount 2.2 Decimal
25 Display Message 50 Alphanumeric
26 Corporate Account 15 Alphanumeric
27 Agent 2 Numeric
28 % Deduction at Source 2.2 Decimal
29 Valid Until 8 Date
30 Licensed dealer num 20 Numeric
31 Bank Code 3 Numeric
32 Branch Code 5 Numeric
33 Bank Account num. 20 Numeric
34 Previous Annual Sales 9.2 Decimal
35 Currency, Previous Annual Sales 5 Currency As per currency table
36 Previous Annual Purchases 9.2 Decimal
37 Currency, Previous Annual Purchases 5 Currency As per currency table
38 Email Address 50 Alphanumeric
39 Fax 30 Alphanumeric
40 Country 20 Alphanumeric
41 Installment Code long Numeric
42 VAT exempt byte Numeric
43 Rate Difference Account 15 Alphanumeric
44 Main Currency 5 Alphanumeric
45 Balance Code long Numeric
46 Late Payment short Numeric
47 Main Account long Numeric
48 Cost Code 5 Alphanumeric
49 File 50 Alphanumeric

Special Checks

  • Each record must include an account key field.
  • The field “% deduction at source” may not be a negative number.

Back to the top

Journal Entries Interface

The Journal Entries Interface enables the import of journal entries from other programs to WizCount.  The transactions are transferred into temporary files in WizCount; afterwards, the user may check and correct them prior to entering them into the permanent file.

Interface file name:        MOVEIN.DOC

Parameters file name:    MOVEIN.PRM

Parameters File Format:

 

In the first line, enter the record size in the Interface file.

 

In Lines 2-24, enter the location of the fields in the record according to the following table:

Fields that can be imported into Journal Entries
Line Field Name Maximum Length Contents Remarks
2 Transaction Type Code 3 Alphanumeric
3 1st Reference long Numeric
4 2nd Reference long Numeric
5 Reference Date 8 Date
6 Value Date 8 Date
7 Cost Code 5 Alphanumeric
8 Currency Code 5 Currency According to Currency table
9 Details 50 Alphanumeric
10 Debit Account 1 15 Alphanumeric
11 Debit Account 2 15 Alphanumeric
12 Credit Account 1 15 Alphanumeric
13 Credit Account 2 15 Alphanumeric
14 Debit Amount 1in Local Currency 9.2 Decimal
15 Debit Amount 2in Local Currency 9.2 Decimal
16 Credit Amount 1in Local Currency 9.2 Decimal
17 Credit Amount 2in Local Currency 9.2 Decimal
18 Debit Amount 1in Foreign Currency 9.2 Decimal
19 Debit Amount 2in Foreign Currency 9.2 Decimal
20 Credit Amount 1in Foreign Currency 9.2 Decimal
21 Credit Amount 2in Foreign Currency 9.2 Decimal
22 Date 3 8 Date
23 Reference 3 long Numerical
24 Quantity 9.2 Decimal
25 File 50 Alphanumeric
26 Additional remarks 50 Alphanumeric
27 Additional remarks 2 50 Alphanumeric
28 Branch 9 Alphanumeric
29 VAT registration number 9 Alphanumeric

Special Checks and Remarks:

 

  • All the decimal amounts, which appear in the transaction’s record, are saved in WizCount as Two-place decimals.
  • Transactions may not be transferred without an account.  Each transaction must include at least a debit account or a credit account.
  • Transactions without amounts cannot be transferred.  Each transaction must have at least one sum.
  • An amount cannot be transferred without an account; however, accounts without amounts (informative accounts) may be transferred.
  • When a transaction has a debit sum and a credit sum, the transaction must be balanced, i.e. the debit amounts must be equal to the credit amounts.
  • It is possible not to transfer dates; WizCount will then enter the date from the computer system as the dates of the transactions.
  • Value and reference dates must be within the date limitations defined by the user in the Definitions category in WizCount.
  • A transaction, which includes more than two accounts, must also include a transaction type code.
  • The transaction type is only informative and does not actually split the amounts in the transaction.
  • If a transaction type is not transferred, a second debit or credit account key may not be entered.
  • It is recommended to use negative amounts for cancellation of transactions in accounting.

 

 

Journal transactions that refer to invoices

The following examples assume the VAT rate is 18% and should be modified if the VAT rate is different.

The structure of the journal transactions of invoices should be as follows:
Document Debit account % Credit account %
Sale invoice Customer 118 Income VAT on output 100 118
Sale invoice – credit Income VAT on output 100 18 Customer 118
Sale invoice for 0 % VAT Customer 100 VAT exempt income 100
Sale invoice for 0 % VAT – credit VAT exempt income 100 Customer 100
Purchase invoice Expense VAT on input 100 18 Vendor 118
Purchase invoice – credit Vendor 118 Expense VAT on input 100 18
Purchase invoice 2/3 VAT Expense VAT on input 106 12 Vendor 118
Purchase invoice 1/4 VAT Expense VAT on input 113.5 4.5 Vendor 118

Export invoice: insert the export document official number (if exists) in the 3rd Reference field. If there is no such document the field should be empty.

Import: make sure that the accounting index includes a special account for VAT on input for import (on top of the VAT on input account).  This account is the debit account in the journal transaction that refers to the VAT paid for the imported items. The document should be registered in the 2nd Reference field.

Sales to and from the Palestinians Authority: the journal transactions should be according to the above mentioned guidelines expect for a different VAR rate.

Sales to chance customers: when the debit account of sales is chance customers, the maximal amount of each sale is 5,000 NIS (not including VAT). If the amount is higher, the debit account should be of a customer account included in the index.

Sales from cash register: The number of slips (on top of their amount) should be inserted in Reference filed. If several transactions refer to the same Z, insert the number of slips in one of the transactions and 0 in the others.

Petit cash: The structure of the Petit Cash transaction is like the structure of a purchase invoice. Insert the number of invoices in the 2nd Reference field.

Back to the top 

Bank Statements Interface

The Bank Statements Interface enables the import of bank statements from other programs or directly from the bank computer via modem into WizCount.  WizCount furnishes a flexible format for the Interface file based on a Parameters file.

Before entering data into the Interface file, WizCount performs data integrity checks; should WizcCount detect errors, an errors report is displayed without entering erroneous data into the file.

If the Interface file is intact and the account already has a bank statement in WizCount, the program checks that the imported file is the continuation of the existing bank statement in WizCount.

Below are details about Interface in flexible format:

Interface file name:        BANKIN.DAT

Parameters file name:    BANKIN.PRM

Parameters File Format:

 

In the first line, enter the record size into the Interface file.

In lines 2-7, enter the location of the fields in the record according to the following table:

Fields that can be imported into Bank Statements Interface
Line Field Name Maximum Length Contents
2 Reference 5 Numeric
3 Date 8 Date
4 Details 8 Alphanumeric
5 Debit Amount 9.2 Decimal
6 Credit Amount 9.2 Decimal
7 Cumulative Balance 9.2 Decimal

Special Checks and Remarks:

 

  • A date field must be in each line in the Interface record
  • If, in the Interface file, there is only one amount, its location is entered in the Parameters file on Line 5, which is designated for the debit amount.  In this case, enter 0 0 in Line 6 in the Parameters file (designated for the credit amount).
  • In transactions with one amount field, a zero amount cannot be entered.  In these transactions, negative amounts will be entered on the debit side and positive amounts on the credit side.
  • If both a debit amount field and also a credit amount field are transferred in a record, a debit sum or a credit sum must also appear in each transaction in the Interface file.
  • It is impossible to transfer, in the same transaction, both a debit and a credit amount.  The transfer of transactions with a debit amount of zero and a credit amount of zero is not permitted.
  • The cumulative balance field includes the cumulative balance of the previous line plus the current transaction.  This field serves as a continuous check against the existing bank statement for the account in WizCount.  If this field is not transferred, no continuous check is performed.

Back to the top

Items Interface

The Items Interface enables the import if items from other programs to WizCount.  The Interface enables you to open new items or to update existing ones.

Interface file name:        ITEMIN.DAT

Parameters file name:    ITEMIN.PRM

Parameters File Format:

 

In the first line, enter the record size into the Interface file.

In Lines 2-65, enter the location of the fields in the Interface record according to the following table:

Fields that can be imported into an Item
Line Field Name Maximum Length Contents Remarks
2 Item Key 20 Alphanumeric
3 Item Name 100 Alphanumeric
4 Extra Name 100 Alphanumeric
5 Category Code 100 Numeric
6 Filter 10 Alphanumeric
7 Unit of Measure 5 Alphanumeric
8 Weight 6.3 Decimal
9 Volume 6.3 Decimal
10 Bar Code Number 16 Alphanumeric
11 Location 16 Alphanumeric
12 Price 1 6.3 Decimal
13 Currency, Price 1 2 Currency Code As per currency table
14 Price 2 6.3 Decimal
15 Currency, Price 2 2 Currency Code As per currency table
16 Price 3 6.3 Decimal
17 Currency, Price 3 2 Currency Code As per currency table
18 Price 4 6.3 Decimal
19 Currency, Price 4 2 Currency Code As per currency table
20 Price 5 6.3 Decimal
21 Currency, Price 5 2 Currency Code As per currency table
22 Price 6 6.3 Decimal
23 Currency, Price 6 2 Currency Code As per currency table
24 Price 7 6.3 Decimal
25 Currency, Price 7 2 Currency Code As per currency table
26 Price 8 6.3 Decimal
27 Currency, Price 8 2 Currency Code As per currency table
28 Discount Code 5 Alphanumeric
29 % Sale Discount 2.2 Decimal
30 V.A.T. Exemption 1 “Y” for yes and “N” for no
31 Purchasing Unit of Measure 4 Alphanumeric
32 Minimum Purchase Quantity 9.3 Decimal
33 Stock Level 9.3 Decimal
34 Ordering Point 8.3 Decimal
35 % Customs Duties 3.2 Decimal
36 Customs Classifications No. 9 Alphanumeric
37 Alternate Item for above 1 “Y” for yes and “N” for no
38 Production/Delivery Time 4.2 Decimal
39 % Retail Purchase Tax 3.2 Decimal
40 % Wholesale Purchase Tax 3.2 Decimal
41 Additional Amount 1 9.3 Decimal
42 Additional Amount 2 9.3 Decimal
43 Additional Amount 3 9.3 Decimal
44 Additional Amount 4 9.3 Decimal
45 Additional Amount 5 9.3 Decimal
46 Additional Amount 6 9.3 Decimal
47 Additional Amount 7 9.3 Decimal
48 Additional Amount 8 9.3 Decimal
49 Additional Date 1 8 Date
50 Additional Date 2 8 Date
51 Additional Date 3 8 Date
52 Additional Date 4 8 Date
53 Last Purchase Date 8 Date
54 Last Purchase Price 6.3 Decimal
55 Currency, Last Purchase 2 Currency Code As per currency table
56 Stock Count Date 8 Date
57 Stock Count Difference 8.3 Decimal
58 Stock Balance 8.3 Decimal
59 Balance, Customer Orders 8.3 Decimal
60 Balance, Purchase Orders 8.3 Decimal
61 List Price 6.3 Decimal
62 Currency 5 Alphanumerical
63 Default Warehouse long Numerical
64 File 50 Alphanumerical
65 Quantity in Package 9.3 Decimal

Special Checks and Remarks:

  • Each record must include an item key field
  • Negative numbers may not be transferred into the following fields: % Customs Duties, Weight, Volume, Purchase Quantity, Stock Level, Ordering Point, % Purchase Tax (Retail/Wholesale), Last Purchase Price.
  • The ordering point must be less than or equal to the stock level.

Back to the top

Document Interface

This utility enables data import of documents – invoices, delivery notes, orders, quotations etc.

When importing the data the user determines –

  1. If the account (customer or supplier) in transaction to be imported does not exists in WizCount database, should the program automatically open a new account (or reject the transaction).
  2. If the item in transaction to be imported does not exists in WizCount database, should the program automatically open a new item (or reject the transaction).

If the automatic opening option is selected, the account or item name will be: "Open in data import from another program."

If the rejection option is selected, WizCount will issue a report listing all the rejected transactions.

The name of the data file: IMOVEIN.DOC

The name of the parameter file: IMOVEIN.PRM

The structure of the parameter file

The first line presents the size of the records in the data file.

Lines 2 – 52 list the position of the fields (in the record) according to the following table:

Fields that can be imported into a Document
Line # Field name Maximal length Type Notes
2 Account ID 15 Alphanumeric
3 Document No. 9 Numeric
4 Document type 2 Numeric See table below
5 Account name 50 Alphanumeric
6 Address 50 Alphanumeric Street & No
7 City 50 Alphanumeric
8 Reference 2 9 Numeric
9 Reference data 10 Date
10 Due date 10 Date
11 Salesperson 9 Numeric
12 Warehouse 9 Numeric
13 Remarks 250 Alphanumeric
14 Warehouse – origin 9 Numeric See remark #2 below.
15 Salesperson – origin 9 Numeric See remark #2 below
16 List price – Not Active 1 Numeric Not Active
17 % total discount 2.2 Decimal
18 % VAT 2.2 Decimal
19 Number of copies 2 Numeric
20 Document currency 5 Currency Select from the currency table
21 Document Rate 5.4 Decimal
22 Item ID 20 Alphanumeric
23 Quantity 9.3 Decimal Not 0
24 Price 9.3 Decimal
25 Currency 5 Currency Select from the currency table
26 % Line/Item discount 2.2 Decimal
27 Rate 5.4 Decimal
28 Item name 100 Alphanumeric
29 Unit measure 5 Alphanumeric
30 Sale tax 3.2 Decimal
31 Alternative ID 20 Alphanumeric
32 Commission % 2.2 Decimal
33 Quantity in box 6.3 Decimal
34 VAT exempt 1 Numeric 1 – exempt0 – not exempt
35 Phone 30 Alphanumeric
36 Additional date 10 Date
37 Remarks 250 Alphanumeric
38 Reference 3 9 Numeric
39 Cost accounting code 5 Alphanumeric
40 Expiry date 1 10 Date
41 Batch 20 Alphanumeric
42 Location 20 Alphanumeric
43 Expiry date 2 10 Date
44 Serial number 20 Alphanumeric
45 Additional text 1 50 Alphanumeric
46 Additional text 2 50 Alphanumeric
47 Additional text 3 50 Alphanumeric
48 Additional text 4 50 Alphanumeric
49 Additional text 5 50 Alphanumeric
50 Additional number 1 8 Decimal
51 Additional number 2 8 Decimal
52 Additional number 3 8 Decimal
53 Distribution line 9 Numeric
54 Line No 9 Numeric
55 Line No – base doc 9 Numeric
56 Additional remark in transaction 1 50 Alphanumeric
57 Additional remark in transaction 2 50 Alphanumeric
58 Additional amount in transaction 1 9.3 Decimal
59 Additional amount in transaction 1 9.3 Decimal
60 VAT registration no. 9 Numeric
61 Base doc ID 9 Numeric
62 Email address 50 Alphanumeric

Special Checks and Remarks:

  • Manufacturing report cannot be imported.
  • In the document Trasfer from one wharehouse to another, the transaction refer to the inward. The fields Warehouse and Salesperson (11,12) refer to the target (to), while the fields Warehouse – origin and Salesperson– origin (14,15) refer to the origin (from). WizCount will automatically add the transaction for the outward.
  • The following fields should be included in each transactions: Item ID, Quantity, Document No, Document type.
  • The value of the Quantity field should not be 0.
  • When importing the following documents, the Account ID should be included in each transaction: Invoice, Order, Delivery note, Quotation (all types of these documents).
  • A document that functions as both invoice and receipt cannot be imported (the invoice and the receiept should be imported by two files).
  • The document type determines whether the quantity enters or exits. Therfore the quantity should be positive, unless the document cancel a previous one.
  • The price is the price prior discounts.

Document types

Document types
Code Document Remarks
1 Invoice
3 Salesperson Invoice A copy of an original invoice issued by another system
5 Credit Invoice
6 Export Invoice No VAT
7 Purchase Invoice
8 Credit Purchase Invoice
12 Transfer from one warehouse to another
14 Opening balance
15 Stock taking
16 General enterance to inventory
17 General exit from inventory
18 Enterance to Bonded warehouse
19 Exit from Bonded warehouse
20 Delivery-note
21 Salesperson delivery-note
22 Items returning by customer
23 Purchase deliver-note
24 Items returning to supplier
30 Customer order
31 Salesperson customer order
32 Order for export
33 Lay-by invoice
34 Purchase order
35 Import order
40 Quotation
41 Purchase quotation
מבנה מסד נתונים חשבשבת תוכנת ERP

לחזרה לתמיכה בבתי תוכנה לחצו כאן >