Evopos Help - 2.09.090

Importing Sales and Debtors

Importing Sales and Debtors

Previous topic Next topic  

Importing Sales and Debtors

Previous topic Next topic JavaScript is required for the print function  

There are 2 main Import / Export formats

CSV Format (Single line format - For a simpler import)

Evopos Format (Multi line format - For more details - Normally used to transfer between 2 Evopos databases)

If just importing (Not creating the Export in Evopos), then arrange the data columns into the column orders specified below.

Notes:

Matching Contacts - If CustNo is blank it will try to match on the Email or Name and Address1, if not matched will create new contact record. If the CustNo is found, if Name matches up it will use that Contact, if the Name does not match it will create new Contact record with the next available Customer No. If CustNo is not found it will create a new Contact record with that Customer Number

Matching Transaction Numbers - If the Transaction number exists when importing it will give it the next available Transaction Number and put the original Transaction number in the Header Our Reference field

See General / Importing Data for details of what can affect the Import.

CSV Format - File structure

Column

Field

Description

A

InvNo

Invoice Number eg: 123456 (If you could have these numbers already set a different Division)

B

InvDate

Eg: 31-Dec-2014 or 31/12/2014 (If blank will use todays date)

C

CustNo

Eg: 123456 (See above notes to match up with the Customer Number on Contacts)

D

PaymentMethodID

1/NO/FALSE = Not Paid, 2 to 99 = Evopos Payment Method ID, Blank/Other = 2 (Cash)

E

PaidOrDueDate

Eg: 31-Dec-2014 or 31/12/2014 (If blank will use todays date)

F

Qty

Eg: 1

G

PartNo

Eg: BA12N143A

H

Description

Eg: BATTERY 12 VOLT

I

LineSell

Eg: 110.00 (Including Tax)

J

LineTax

Eg: 10.00

K

LineCost

Eg: 50.75 (Excluding Tax)

L

Name

Optional - Only used if CustNo Blank will try and match on Name and First Line of address

M

Add1

Optional - See above

 

CSV Format - Importing

It is recommended to take backup before each import in-case of any unexpected results.

Note: Import Contacts before importing Sales, Units or Jobs.

1.Create the CSV file as above. See Importing Data for important information

2.Make sure you are running Evopos 2.09.090 or later

3.In Evopos, select Accounts, then Maintenance, and select Import Sales CSV from the options

4.Select the Division you want it entered into (If the Trans number could already be used you must do this or the lines will be added to the existing invoice). Select a Date Since if you want to restrict the records it will import. Select Ok

5.Select the File to import (as detailed above)

6.On the 'Import where Contact not matched' question select 'Yes' to import the record under '*Counter Sale*' or 'No' to not import these records.

There are several variations on this Import for use in-case the individual transaction lines do not contain certain fields. These Include:

If  you do not have the CustNo field, then you can create another file that has the Invoice Number and the Customer No and name the file with CUSTNO in it eg: Sales_CUSTNO.csv

If the total of the Invoice is not the sum of the LineSell in the transaction lines, then you can create another file that has the Invoice Number and the LineSell columns and name the file with CUSTNO in it eg: Sales_CUSTNO.csv .  Note: You can combine this and the above by naming the file with both eg: Sales_CUSTNO_TOTAL.csv

If you have a separate file for invoices that are Not Paid you can import the main file with everything marked as Paid and then import a second file with just the un-paid invoices marked as Not Paid. This file name must contain the text NOTPAID eg: Sales_NOTPAID.csv

 

Evopos Format - File structure

This format uses multiple coded lines. The first filed is the Code which can be H,S,D,L or P (see below). Fields are separated by the | character

When opened in an editor such as Notepad, the data should look something like this:

H|236555|29-Jan-15 2:18:00 PM|INVOICE|1|19.5900|3.2600|9.2500|0.0020|MARK|Thank you E&EO|CASH: 19.59

S|236555|N406877|MERATI, MR BAHMAN|||||||MR BAHMAN|||||

L|236555|17|33400KWF951|WINKER ASSYR FR    *B|1.0000|14.6600|11.0000|0.0000|0.0000|0.2000||0|0|P-PRICEFILE|SPECIAL ORDER|*S-ORDER

L|236555|1|SUR1|SPECIAL ORDER|1.0000|1.6667|0.0000|0.0000|0.0000|0.2000|MARK|0|0|P-STOCK||

P|236555|N406877|MERATI, MR BAHMAN|19.5900|2|29-Jan-15 2:18:21 PM|True|29-Jan-15 2:18:00 PM|

H|236556|29-Jan-15 4:03:00 PM|INVOICE|1|10.9900|1.8300|5.8700|0.0000|MARK|Thank you E&EO|CASH: 10.99

S|236556|N405864|GOSSINK, MR LES|138 KINNELL AVE|CARDONALD|GLASGOW||G52 3RU||MR LES|07916344006||||

L|236556|1|53140KFC8900|THROTTLE GRIP|1.0000|9.1583|6.3000|0.0000|0.0000|0.2000|MARK|0|0|P-STOCK|SPECIAL ORDER|*S-ORDER

P|236556|N405864|GOSSINK, MR LES|10.9900|2|29-Jan-15 4:02:47 PM|True|29-Jan-15 4:03:00 PM|

Header record

 

Column

Field

Description

A

Type

H (= Header)

B

TransNo

Eg: 123456 (See notes above for matching)

C

TransDate

Eg: 31-Dec-2014 or 31/12/2014

D

Type

INVOICE

E

Department ID

Eg: 1-Shop Sales 2-6-Ebay, Web etc, 7-Jobs, 8-Unsd Units, 9-New Units

F

Total Incl

Eg: 110.00

G

Tax

Eg: 100.00

H

Cost

Eg: 70.00

I

Rounding

Eg: 0.00

J

Operator Name

Eg: Sally S (Short Name) - If not found will create in Operators

K

Message

Eg: Thanks for your business

L

Payment Summary

Eg: Paid 110.00 Card

 

 

Sold To (and optional Deliver To) Record

 

Column

Field

Description

A

Type

S (= Sold To) and also D (=Deliver To) if a different Delivery address is specified

B

TransNo

Eg: 123456 (See notes above for matching)

C

CustNo

Eg: 100200 (See Notes above for matching)

D

Name

Eg: SMITH, PETER

E

Add1

Eg: 123 Any Street

F

Add2

Eg: Little Corner

G

Town

Eg: SOUTHPORT

H

State/County

Eg: QLD\Dorset

I

Postcode

Eg: 4213/BH12 3GH

J

Greeting

Eg: Pete

K

Mobile

Eg: 0413 044978

L

Work Phone

Eg: 07 55303101

M

Home Phone

Eg: 07 55332124

N

Email

Eg: bill@evopos.com.au

 

Line Record

 

Column

Field

Description

A

Type

L (= Line record)

B

TransNo

Eg: 123456 (See notes above for matching)

C

StoreID

Eg: 1

D

Part Number

Eg: ABC123

E

Description

Eg: WIDGET 32MM

F

Qty

Eg: 1

G

UnitSellExcl

Eg: 100.00

H

UnitCostExcl

Eg: 70.00

I

Discount%

Eg: 5

J

Discount$

Eg: 5.00

K

Tax Rate

Eg: 20

L

Salesperson Name

Eg: SALLY S (Short Name in Operators)

M

Parent ID

Eg: 0

N

Linked ID

Eg: 0

 

Payment Record

 

Column

Field

Description

A

Type

P (= Payment record)

B

TransNo

Eg: 123456 (See notes above for matching)

C

Customer No

Eg: 123456 (See notes above for Contact Matching)

D

Name

Eg: SMITH, PETER

E

Amount

Eg: 110.00

F

Payment Method ID

Eg: 1 or 0 for not Paid (Make sure they match with the database being imported into)

G

Due Date

Eg: 21-Dec-2016

H

Paid Date

Eg: 21-Dec-2016

I

Payment Reference

Eg: Cheque No: 23335