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:

LineField NameMaximum LengthContentsRemarks
2Account Key15Alphanumeric 
3Account Name50Alphanumeric 
4Category CodelongNumeric 
5Cutoff5Alphanumeric 
6Telephone30Alphanumeric 
7Address50Alphanumeric 
8District15Alphanumeric 
9City/Town20Alphanumeric 
10Post Code5Numeric 
11Occupation15Alphanumeric 
12Accountant Code4Alphanumeric 
13Details50Alphanumeric 
14Additional Date 18Date 
15Additional Date 28Date 
16Additional Amount 19.2Decimal 
17Additional Amount 29.2Decimal 
18Additional Amount 39.2Decimal 
19Additional Amount 49.2Decimal 
20Maximum Credit9.2Decimal 
21Currency of Maximum Credit5CurrencyAs per currency table
22Maximum Obligation9.2Decimal 
23Currency of Maximum Obligation5CurrencyAs per currency table
24% Total Discount2.2Decimal 
25Display Message50Alphanumeric 
26Corporate Account15Alphanumeric 
27Agent2Numeric 
28% Deduction at Source2.2Decimal 
29Valid Until8Date 
30Licensed dealer num20Numeric 
31Bank Code3Numeric 
32Branch Code5Numeric 
33Bank Account num.20Numeric 
34Previous Annual Sales9.2Decimal 
35Currency, Previous Annual Sales5CurrencyAs per currency table
36Previous Annual Purchases9.2Decimal 
37Currency, Previous Annual Purchases5CurrencyAs per currency table
38Email Address50Alphanumeric 
39Fax30Alphanumeric 
40Country20Alphanumeric 
41Installment CodelongNumeric 
42VAT exemptbyteNumeric 
43Rate Difference Account15Alphanumeric 
44Main Currency5Alphanumeric 
45Balance CodelongNumeric 
46Late PaymentshortNumeric 
47Main AccountlongNumeric 
48Cost Code5Alphanumeric 
49File50Alphanumeric 

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:

LineField NameMaximum LengthContentsRemarks
2Transaction Type Code3Alphanumeric 
31st ReferencelongNumeric 
42nd ReferencelongNumeric 
5Reference Date8Date 
6Value Date8Date 
7Cost Code5Alphanumeric 
8Currency Code5CurrencyAccording to
Currency table
9Details50Alphanumeric 
10Debit Account 115Alphanumeric 
11Debit Account 215Alphanumeric 
12Credit Account 115Alphanumeric 
13Credit Account 215Alphanumeric 
14Debit Amount 1in Local Currency9.2Decimal 
15Debit Amount 2in Local Currency9.2Decimal 
16Credit Amount 1in Local Currency9.2Decimal 
17Credit Amount 2in Local Currency9.2Decimal 
18Debit Amount 1in Foreign Currency9.2Decimal 
19Debit Amount 2in Foreign Currency9.2Decimal 
20Credit Amount 1in Foreign Currency9.2Decimal 
21Credit Amount 2in Foreign Currency9.2Decimal 
22Date 38Date 
23Reference 3longNumerical 
24Quantity9.2Decimal 
25File50Alphanumeric 
26Additional remarks50Alphanumeric 
27Additional remarks 250Alphanumeric 
28Branch9Alphanumeric 
29VAT registration number9Alphanumeric 

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:

DocumentDebit account%Credit account%
Sale invoiceCustomer118Income
VAT on output
100
118
Sale invoice – creditIncome
VAT on output
100
18
Customer118
Sale invoice for 0 % VATCustomer100VAT exempt income100
Sale invoice for 0 % VAT – creditVAT exempt income100Customer100
Purchase invoiceExpense
VAT on input
100
18
Vendor118
Purchase invoice – creditVendor118Expense
VAT on input
100
18
Purchase invoice 2/3 VATExpense
VAT on input
106
12
Vendor118
Purchase invoice 1/4 VATExpense
VAT on input
113.5
4.5
Vendor118

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:

LineField NameMaximum LengthContents
2Reference5Numeric
3Date8Date
4Details8Alphanumeric
5Debit Amount9.2Decimal
6Credit Amount9.2Decimal
7Cumulative Balance9.2Decimal

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:

LineField NameMaximum LengthContentsRemarks
2Item Key20Alphanumeric 
3Item Name100Alphanumeric 
4Extra Name100Alphanumeric 
5Category Code100Numeric 
6Filter10Alphanumeric 
7Unit of Measure5Alphanumeric 
8Weight6.3Decimal 
9Volume6.3Decimal 
10Bar Code Number16Alphanumeric 
11Location16Alphanumeric 
12Price 16.3Decimal 
13Currency, Price 12Currency CodeAs per currency table
14Price 26.3Decimal 
15Currency, Price 22Currency CodeAs per currency table
16Price 36.3Decimal 
17Currency, Price 32Currency CodeAs per currency table
18Price 46.3Decimal 
19Currency, Price 42Currency CodeAs per currency table
20Price 56.3Decimal 
21Currency, Price 52Currency CodeAs per currency table
22Price 66.3Decimal 
23Currency, Price 62Currency CodeAs per currency table
24Price 76.3Decimal 
25Currency, Price 72Currency CodeAs per currency table
26Price 86.3Decimal 
27Currency, Price 82Currency CodeAs per currency table
28Discount Code5Alphanumeric 
29% Sale Discount2.2Decimal 
30V.A.T. Exemption1“Y” for yes and “N” for no 
31Purchasing Unit of Measure4Alphanumeric 
32Minimum Purchase Quantity9.3Decimal 
33Stock Level9.3Decimal 
34Ordering Point8.3Decimal 
35% Customs Duties3.2Decimal 
36Customs Classifications No.9Alphanumeric 
37Alternate Item for above1“Y” for yes and “N” for no 
38Production/Delivery Time4.2Decimal 
39% Retail Purchase Tax3.2Decimal 
40% Wholesale Purchase Tax3.2Decimal 
41Additional Amount 19.3Decimal 
42Additional Amount 29.3Decimal 
43Additional Amount 39.3Decimal 
44Additional Amount 49.3Decimal 
45Additional Amount 59.3Decimal 
46Additional Amount 69.3Decimal 
47Additional Amount 79.3Decimal 
48Additional Amount 89.3Decimal 
49Additional Date 18Date 
50Additional Date 28Date 
51Additional Date 38Date 
52Additional Date 48Date 
53Last Purchase Date8Date 
54Last Purchase Price6.3Decimal 
55Currency, Last Purchase2Currency CodeAs per currency table
56Stock Count Date8Date 
57Stock Count Difference8.3Decimal 
58Stock Balance8.3Decimal 
59Balance, Customer Orders8.3Decimal 
60Balance, Purchase Orders8.3Decimal 
61List Price6.3Decimal 
62Currency5Alphanumerical 
63Default WarehouselongNumerical 
64File50Alphanumerical 
65Quantity in Package9.3Decimal 

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:

Line #Field nameMaximal lengthTypeNotes
2Account ID15Alphanumeric 
3Document No.9Numeric 
4Document type2NumericSee table below
5Account name50Alphanumeric 
6Address50AlphanumericStreet & No
7City50Alphanumeric 
8Reference 29Numeric 
9Reference data10Date 
10Due date10Date 
11Salesperson9Numeric 
12Warehouse9Numeric 
13Remarks250Alphanumeric 
14Warehouse – origin9NumericSee remark #2 below.
15Salesperson – origin9NumericSee remark #2 below
16List price – Not Active1NumericNot Active
17% total discount2.2Decimal 
18% VAT2.2Decimal 
19Number of copies2Numeric 
20Document currency5CurrencySelect from the currency table
21Document Rate5.4Decimal 
22Item ID20Alphanumeric 
23Quantity9.3DecimalNot 0
24Price9.3Decimal 
25Currency5CurrencySelect from the currency table
26% Line/Item discount2.2Decimal 
27Rate5.4Decimal 
28Item name100Alphanumeric 
29Unit measure5Alphanumeric 
30Sale tax3.2Decimal 
31Alternative ID20Alphanumeric 
32Commission %2.2Decimal 
33Quantity in box6.3Decimal 
34VAT exempt1Numeric1 – exempt0 – not exempt
35Phone30Alphanumeric 
36Additional date10Date 
37Remarks250Alphanumeric 
38Reference 39Numeric 
39Cost accounting code5Alphanumeric 
40Expiry date 110Date 
41Batch20Alphanumeric 
42Location20Alphanumeric 
43Expiry date 210Date 
44Serial number20Alphanumeric 
45Additional text 150Alphanumeric 
46Additional text 250Alphanumeric 
47Additional text 350Alphanumeric 
48Additional text 450Alphanumeric 
49Additional text 550Alphanumeric 
50Additional number 18Decimal 
51Additional number 28Decimal 
52Additional number 38Decimal 
53Distribution line9Numeric 
54Line No9Numeric 
55Line No – base doc9Numeric 
56Additional remark in transaction 150Alphanumeric 
57Additional remark in transaction 250Alphanumeric 
58Additional amount in transaction 19.3Decimal 
59Additional amount in transaction 19.3Decimal 
60VAT registration no.9Numeric 
61Base doc ID9Numeric 
62Email address50Alphanumeric 

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

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

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

דילוג לתוכן