sage pastel accountingezines.pastel.co.za/.../spa_how_to_import_customer_supplier_documents.pdf ·...
TRANSCRIPT
In Sage Pastel Accounting while you can import customer, supplier and inventory master
information, you can also import certain batches like journals, cashbooks and customer and
supplier documents.
This means that you can create the batch in excel as a .csv file and then import the .csv excel file
into Sage Pastel accounting.
While importing a .csv file may be quicker you will need to make sure that you have created the
import file in the correct layout.
IMPORATNT INFORMATION: Do not input special characters on the import file i.e. “$@!’
There are two types of records:
Headers
Details
The header appears first, followed by the details for that record.
Header Record:
Ref. Field Type Format Excel Ref.
1 Header Character The word "Header" A
2 Document Number
Character 8 characters maximum, ignored when importing
B
3 Deleted Character Y=Deleted, <space>=not deleted, ignored when importing
C
4 Print Status Character Y=Printed, <space>=not printed D
5 Customer Code Character 6 characters maximum E
6 Period Number Numeric 1-13 F
7 Date Character DD/MM/YYYY G
8 Order Number Character 25 characters maximum H
9 Inc/Exc Character Y=Inclusive, N=Exclusive I
10 Discount Numeric J
11 Invoice Message 1-3
Character 3 separate fields of 30 characters maximum each
K-M
12 Delivery Address 1-5
Character 5 separate fields of 30 characters maximum each
N-R
13 Sales Analysis Code
Character 5 characters maximum S
14 Settlement Terms
Numeric 0-32 T
15 Document Date Character DD/MM/YYYY, Expiry/Delivery/Due date
U
16 Telephone Character 16 characters maximum V
17 Fax Number Character 16 characters maximum W
18 Contact Person Character 16 characters maximum X
19 Exchange Rate Numeric 1 in foreign currency, 7.6 maximum Y
20 Freight Method Character 10 characters maximum Z
21 Ship/Deliver Character 16 Characters maximum AA
22 Additional Costs Character Y=Additional, N=Normal, supplier invoices only
AB
23 Email Status Character Y=Emailed, <space>=not emailed AC
Detail Record:
Ref. Field Type Format Excel Ref.
1 Detail Character The word "Detail" A
2 Cost Price Numeric
9.4 maximum
Note: When you import, you MUST set this field to zero. The system always uses the then current average cost price when you import lines.
B
3 Quantity Numeric 9.4 maximum C
4 Unit Selling Price
Numeric 9.4 maximum D
5 Inclusive Price
Numeric 9.4 maximum E
6 Unit Character 4 characters maximum F
7 Tax Type Character 00-30 G
8 Discount Type
Character 0=None, 1=Settlement, 2=Invoice, 3=Both H
9 Discount Percentage
Character Omit decimals, for example 12.5% = 1250 I
10 Code Character 15 characters maximum J
11 Description Character 40 characters maximum K
12 Line Type Character 4=Inventory, 6=GL, 7=Remarks L
13 Projects Code
Character 5 characters maximum M
14 Store Character 3 characters maximum N
Before creating your .csv file in excel:
Go to Setup| Periods
The ‘Setup Period’ screen will display:
In the Per column you will see numbers from 1-12 or 1-13 depending on if you have setup a
13th period
These numbers represent your periods in a .csv file
Make a note of the Start and End dates for the Per column
Click OK
Open Excel to create the batch file as per the Sage Pastel Accounting layout
The batch files should look similar to the below example:
NOTE: Each document will have only 1 header but can have multiple details = lines on the
document.
Before importing the batch file:
After entering the necessary fields as per the layout file select the date fields in column G
and U
To select only the cells with dates hold CTRL on your keyboard and click the cell
Only the cells selected will be highlighted:
Right click and select Format Cells
The ‘Format Cells’ screen will display:
Select the Number Tab
Click Custom
Under Type enter dd/mm/yyyy
Click Ok
Click File| Save As
The ‘Save As’ screen will display:
Select where you wish to save the .csv file
By File name enter a name for the file i.e. Supplier doc Aug2016.csv
By Save as Type ensure you select the option CSV (Comma delimited)(*.csv)
Importing a customer/ supplier documents into Sage Pastel Accounting:
Select Process Customers/Suppliers
By Document Type select the document you wish to import
Click Batch| Import
The ‘Export/Import’ screen will display:
Click the magnifying glass by Import File Name
The ‘Select a file’ screen will display:
Navigate to where you saved the .csv import file
Click on the file and click Open
The ‘Export/Import’ screen will display:
The file path will now display under Import File Name
Click on Process to import the batch
A message will display advising X documents have been imported:
Open each document to ensure the information is correct i.e. Period, Date customer
etc.
NOTE: The document numbers you enter on the import file are ignored when importing. Sage
Pastel Accounting will allocate the numbers automatically however you will still need to have this field
filled for the import to work.