Import Master File from Text File Guide

(1)

Masteritec Sdn. Bhd. 1

Import Master File from Text File Guide

1. Area

Name Type

1. Area Code Varchar(20)

2. Description Varchar(200)

Text File Format:

Area

Area Code|Description

Example Text File:

Area

IP|Ipoh

KL|Kuala Lumpur

2. Cash Book Type

Name Type

1. Cash Book Type Code

Varchar(20)

Text File Format:

Cash Book Type

Cash Book Type Code|Description

Example Text File:

Cash Book Type

Rental|Rental

Petrol Expenses|Petrol Expenses

Entertainment|Entertainment

(2)

3. Cash Type

Name Type

1. Cash Type Code Varchar(20)

3. Note/Coin Note/Coin Default Note

4. Value Decimal(20,2)

Text File Format:

Cash Type

Cash Type Code|Description|Note/Coin|Value

Example Text File:

Cash Type

RM50.00|RM50.00|Note|50.00

RM100.00|RM100.00|Note|100.00

4. Cashier

Name Type

1. Cashier Code Varchar(20)

3. Default Cash In Decimal(20,2) Default 0.00

4. Item Maximum Discount (%) Decimal(20,2) Default 0.00 5. Footer Maximum Discount

(%)

Decimal(20,2) Default 0.00

Text File Format:

Cashier

Cashier Code|Description|

Default Cash In| Item Maximum Discount| Footer Maximum Discount

Example Text File:

Cashier

CashierA|Cashier A|100.00|20.00|10.00

CashierB|Cashier B|200.00|25.00|15.00

(3)

*Note:

After import cashier into Master Accounting, you need to manual set the

password & the access right in Cashier screen. By Default all imported cashier

access right is set to false.

5. Category

Name Type

1. Category Code Varchar(20)

Text File Format:

Category Code|Description

Example Text File:

CategoryA|Category A

CategoryB|Category B

6. Chart of Account

Name Type

1. Account Code Varchar(20)

3. Account Type SALES/SALESADJ/COS/OI/EXP/TAX/EOI/EOE/APP/FA/OA/

CA/CL/EQ/LTL Default Sales SALES  Sales

SALESADJ Sales Adjustment COS  Cost of Sales

OI  Other Income EXP  Expenses TAX  Taxation

EOI  Extra Ordinary Income EOE  Extra Ordinary Expenses APP  Appropriation Account FA Fixed Assets

OA  Other Assets CA  Current Assets CL  Current Liabilities

(4)

EQ  Equity

LTL  Long Term Liabilities

4. Special Type APL/BANK/CASH/CREDITORCONTROL/DEBTORCONTRO

L/FAD/MANUFACTURING/BALANCESTOCK/CLOSINGSTO CK/OPENINGSTOCK/NONE Default NONE

APL  Retained Earning Account (only one account is allowed)

BANK  Bank Account CASH  Cash Account

CREDITORCONTROL  Creditor Control Account DEBTORCONTROL  Debtor Control Account FAD  Accumulated Depreciation Account. MANUFACTURING  Manufacturing Account BALANCESTOCK  Balance Stock Account CLOSINGSTOCK  Closing Stock Account OPENINGSTOCK  Opening Stock Account NONE  None

5. DRCR DR/CR Default DR

6. Cost Centre Code Varchar(20) 7. Default GST Type –

Supply

Varchar(20) Default Empty 8. Default GST Type –

Purchase

Varchar(20) Default Empty

9. MSIC Code Varchar(5) Default Empty

10. Currency Code Varchar(20) Default Local Currency

Text File Format:

Chart of Account

Account Code|Description|Account Type|Special Type|DRCR|Cost Centre Code|Default

GST Type

–

Supply|Default GST Type

–

Purchase|MSIC Code|Currency Code

Example Text File:

Chart of Account

5008|Project Sales|SALES|NONE|DR||SR|TX||MYR

3050|CIMB Bank|CA|BANK|DR||SR|TX||MYR

*Note:

Please make sure all Master File Code like Cost Centre Code, GST Type

Code, MSIC Code & Currency Code exists in Master Accounting Software before

import the Chart of Account.

(5)

7. Cost Centre

Name Type

1. Cost Centre Code Varchar(20)

Text File Format:

Cost Centre

Cost Centre Code|Description

Example Text File:

Cost Centre

Cost Centre A|Cost Centre A

Cost Centre B|Cost Centre B

8. Counter

Name Type

1. Counter Code Varchar(20)

3. Location Code Varchar(20) Default Empty 4. Department Code Varchar(20) Default Empty 5. POS Debtor Code Varchar(20)

Text File Format:

Counter

Counter Code|Description|Location Code|Department Code|POS Debtor Code

Example Text File:

Counter

Counter 1|Counter 1|||3000-P0001

Counter 2|Counter 2|||

*Note:

Please make sure all Master File Code like Location Code, Department

Code & POS Debtor Code exists in Master Accounting Software before import

the Counter.

For POS Debtor Code in text format is allowed to set as empty. But

after you import the counter into Master Accounting, you need go to counter

screen to set back the POS Debtor Code.

(6)

9. Country

Name Type

1. Country Code Varchar(50)

Text File Format:

Country

Country Code|Description

Example Text File:

Country

MY|Malaysia

SG|Singapore

10. Creditor/Supplier

Name Type

1. Creditor Code Varchar(20)

2. Name Varchar(200)

3. Name 2 Varchar(200)

4. Register No Varchar(30) Default Empty

5. Address 1 Varchar(200) Default Empty

9. City Varchar(50) Default Empty

10. Postal Code Varchar(10) Default Empty

11. State Varchar(50) Default Empty

12. Country Code Varchar(50) Default Empty

13. Contact Person Varchar(200) Default Empty

14. Phone 1 Varchar(30) Default Empty

16. Fax 1 Varchar(30) Default Empty

18. Email 1 Varchar(200) Default Empty

20. Home Page Varchar(200) Default Empty

21. Business Nature Varchar(200) Default Empty

(7)

Text File Format:

Creditor/Supplier

Creditor Code|Name|Name 2|Register No|Address 1|Address 2|Address 3|Address

4|City|PostalCode|State|Country Code|Contact Person|Phone 1|Phone 2|Fax 1|Fax

2|Email 1|Email 2|Home Page|Business Nature|Suspended|Control Account Code|Area

Code|Category Code|Group Code|Term Code|Staff Code|Currency Code|GST

Exemption No|GST Exemption Expired Date|GST Register No|Last GST Verified Date|GST

Type Code|GST Register Date|Self-Bill Invoice Approval No|Self-Bill Invoice Approval

Date

Example Text File:

Creditor/Supplier

4000-A0001|GoodWill Sdn Bhd||12345||||||||||||||||||N|4000||||||MYR||||||||

4000-B0001|Best Sdn Bhd||12345||||||||||||||||||N|4000||||||MYR||||||||

*Note:

Please make sure all Master File Code like Country Code, Control

Account Code, Area Code, Category Code, Group Code, Term Code, Staff

Code, Currency Code & GST Type Code exists in Master Accounting Software

before import the Creditor/Supplier.

Y  Yes N  No

23. Control Account Code Varchar(20)

24. Area Code Varchar(20) Default Empty

25. Category Code Varchar(20) Default Empty

26. Group Code Varchar(20) Default Empty

27. Term Code Varchar(20) Default Empty

28. Staff Code Varchar(20) Default Empty

30. GST Exemption No Varchar(30) Default Empty

31. GST Exemption Expired Date Date (DD/MM/YYYY) Default Empty

32. GST Register No Varchar(30) Default Empty

33. Last GST Verified Date Date (DD/MM/YYYY) Default Empty

34. GST Type Code Varchar(20) Default Empty

35. GST Register Date Date (DD/MM/YYYY) Default Empty

36. Self-Bill Invoice Approval No Varchar(50) Default Empty

(8)

11. Currency

Name Type

1. Currency Code Varchar(20)

2. Symbol Varchar(5)

4. Short Name Varchar(10)

5. Buying Rate Decimal(20,8) Default 0.00000000

6. Selling Rate Decimal(20,8) Default 0.00000000

Text File Format:

Currency

Currency Code|Symbol|Name|Short Name|Buying Rate|Selling Rate

Example Text File:

Currency

SGD|SGD $|Singapore Dollar|SG Dollar|2.654|2.554

USD| USD $|USD Dollar|USD Dollar|3.1|3.33

*Note:

Only for foreign currency.

12. Debtor/Customer

Name Type

1. Debtor Code Varchar(20)

3. Name 2 Varchar(200)

4. Register No Varchar(30) Default Empty

14. Contact Person Position Varchar(200) Default Empty

(9)

Text File Format:

Debtor/Customer

Debtor Code|Name|Name 2|Register No|Address 1|Address 2|Address 3|Address

4|City|Postal Code|State|Country Code|Contact Person|Contact Person Position|Phone

1|Phone 2|Fax 1|Fax 2|Email 1|Email 2|Home Page|Business

Nature|Suspended|Control Account Code|Area Code|Category Code|Group Code|Term

Code|Staff Code 1|Staff Code 2|POS|Currency Code|Department Code|Cash

Debtor|GST Exemption No|GST Exemption Expired Date|GST Register No|Last GST

Verified Date|GST Type Code|GST Register Date

21. Home Page Varchar(200) Default Empty

22. Business Nature Varchar(200) Default Empty

23. Suspended Y/N Default N

Y  Yes N  No

24. Control Account Code Varchar(20)

26. Category Code Varchar(20) Default Empty

27. Group Code Varchar(20) Default Empty

28. Term Code Varchar(20) Default Empty

29. Staff Code 1 Varchar(20) Default Empty

30. Staff Code 2 Varchar(20) Default Empty

31. POS Y/N Default N

Y  Yes N  No

33. Department Code Varchar(20) Default Empty

34. Cash Debtor Y/N Default N

Y  Yes N  No

35. GST Exemption No Varchar(30) Default Empty

36. GST Exemption Expired Date Date (DD/MM/YYYY) Default Empty

37. GST Register No Varchar(30) Default Empty

38. Last GST Verified Date Date (DD/MM/YYYY) Default Empty

39. GST Type Code Varchar(20) Default Empty

(10)

Example Text File:

Debtor/Customer

3000-A0001|Advance Sdn

Bhd||A123456|||||||||||||||||||N|3000|||||||N|MYR||N||||||

3000-B0001|BBB Sdn

Bhd||B123456|||||||||||||||||||N|3000|||||||N|MYR||N||||||

*Note:

Please make sure all Master File Code like Country Code, Control

Account Code, Area Code, Category Code, Group Code, Term Code, Staff

Code, Currency Code, Department Code & GST Type Code exists in Master

Accounting Software before import the Debtor/Customer.

13. Department/Project

Name Type

1. Department Code Varchar(20)

3. Debtor Code Varchar(20) Default Empty

4. Staff/Agent Code Varchar(20) Default Empty

5. Start Date Date (DD/MM/YYYY) Default Empty

6. Due Date Date (DD/MM/YYYY) Default Empty

7. Contract Amount Decimal(20,2) Default 0.00 8. Manpower A/C

Code

Varchar(20) Default Empty 9. Manpower Amount Decimal(20,2) Default 0.00

10. Status Code Varchar(20) Default Empty

12. Actual End Date Date (DD/MM/YYYY) Default Empty

13. Petty Cash Decimal(20,2) Default 0.00

14. Remark 1 Varchar(200) Default Empty

(11)

Text File Format:

Department

Department Code|Description|Debtor Code|Staff/Agent Code|Start Date|Due

Date|Contract Amount|Manpower A/C Code|Manpower Amount|Status Code|Area

Code|Actual End Date|Petty Cash|Remark 1|Remark 2|Remark 3|Remark 4|Remark

5|Remark 6|Remark 7|Remark 8

Example Text File:

Department

P0001|Customize Project for CRM|||01/09/2014||||||||||||||||

P0002|Customize Project for POS|||01/10/2014||||||||100.00||||||||

*Note:

Please make sure all Master File Code like Debtor/Customer Code,

Staff/Agent Code, A/C Code, Status Code & Area Code exists in Master

Accounting Software before import the Department/Project.

14. Group

Name Type

1. Group Code Varchar(20)

2. Description Varchar(200) 3. HQ Debtor

Code

Text File Format:

Group

Group Code|Description|HQ Debtor Code

Example Text File:

Group

GroupA|Group A|3000-C0001

GroupB|Group B|

(12)

15. Job

Name Type

1. Job Code Varchar(20)

3. Debtor Code Varchar(20) Default Empty

4. Staff/Agent Code Varchar(20) Default Empty

5. Start Date Date (DD/MM/YYYY) Default Empty

6. Due Date Date (DD/MM/YYYY) Default Empty

7. Contract Amount Decimal(20,2) Default 0.00 8. Manpower A/C

Code

Varchar(20) Default Empty 9. Manpower Amount Decimal(20,2) Default 0.00

10. Status Code Varchar(20) Default Empty

12. Actual End Date Date (DD/MM/YYYY) Default Empty

13. Petty Cash Decimal(20,2) Default 0.00

Text File Format:

Job

Job Code|Description|Debtor Code|Staff/Agent Code|Start Date|Due Date|Contract

Amount|Manpower A/C Code|Manpower Amount|Status Code|Area Code|Actual End

Date|Petty Cash|Remark 1|Remark 2|Remark 3|Remark 4|Remark 5|Remark 6|Remark

7|Remark 8

Example Text File:

Job

Job0001|Customize Job for CRM|||01/09/2014||||||||||||||||

Job0002|Customize Job for POS|||01/10/2014||||||||100.00||||||||

*Note:

Please make sure all Master File Code like Debtor/Customer Code,

Staff/Agent Code, A/C Code, Status Code & Area Code exists in Master

Accounting Software before import the Job.

(13)

16. Journal Book Type

Name Type

1. Journal Book Type Code

Varchar(20)

Text File Format:

JournalBook Type

JournalBook Type Code|Description

Example Text File:

Journal Book Type

Type A|Type A

Type B|Type B

17. Member

Name Type

1. Member Code Varchar(20)

3. Name 2 Varchar(200) Default Empty

4. Gender MALE/FEMALE/UNKNOW Default MALE

5. Date of Birth Date (DD/MM/YYYY) Default Empty

6. IC No Varchar(30) Default Empty

13. Email Varchar(200) Default Empty

14. Occupation Varchar(30) Default Empty

15. Member Type Code

16. Date Joined Date (DD/MM/YYYY) Default Empty

17. Last Renew Date (DD/MM/YYYY) Default Empty

18. Valid Member Year

Integer Default 1

19. Date Expired Date (DD/MM/YYYY) Default Empty

(14)

Text File Format:

Member

Member Code|Name|Name 2|Gender|Date of Birth|IC No|Address 1|Address

2|Address 3|Address 4|Phone 1|Phone 2|Email|Occupation|Member Type Code|Date

Text File Format:

Promoter

Promoter Code|Description|Commission

Example Text File:

Promoter

Ali|Ali|20.00

(16)

21. Sales Team

Name Type

1. Sales Team Code Varchar(20)

Text File Format:

Sales Team

Sales Team Code|Description

Example Text File:

Sales Team

Sales Team A|Sales Team A

Sales Team B|Sales Team B

22. Staff/Agent

Name Type

1. Staff Code Varchar(20)

4. Gender MALE/FEMALE/UNKNOW Default

MALE

5. Date of Birth Date (DD/MM/YYYY) Default Empty

6. IC No Varchar(30) Default Empty

7. EPF No Varchar(30) Default Empty

8. SOCSO No Varchar(30) Default Empty

9. Tax No Varchar(30) Default Empty

16. Email Varchar(200) Default Empty

17. Date Joined Date (DD/MM/YYYY) Default Empty

18. Date Left Date (DD/MM/YYYY) Default Empty

19. Sales Team Code Varchar(20) Default Empty

20. Agent Role Code Varchar(20) Default Empty

(17)

22. Item Maximum Discount (%) Decimal(20,2) Default 0.00

Text File Format:

Staff/Agent

Staff Code|Name|Name 2|Gender|Date of Birth|IC No|EPF NO|SOCSO NO|Tax

NO|Address 1|Address 2|Address 3|Address 4|Phone 1|Phone 2|Email|Occupation

|Date Joined|Date Left|Sales Team Code|Agent Role Code|Footer Maximum

Discount|Item Maximum Discount

Stock Class

Stock Class Code|Description

Example Text File:

Stock Class

Class A|Class A

Class B|Class B

29. Stock Group

Name Type

1. Stock Group Code Varchar(20)

Text File Format:

Stock Group

Stock Group Code|Description

Example Text File:

Stock Group

Stock Group A|Stock Group A

Stock Group B|Stock Group B

(21)

30. Stock Location

Name Type

1. Stock Location Code Varchar(20)

Text File Format:

Stock Location

Stock Location Code|Description|Address 1|Address 2|Address 3|Address 4|City|Postal

Code|State|Country Code|Phone 1|Phone 2|Fax 1|Fax 2|Contact Person

Example Text File:

Stock Location

Kajang|Kajang|||||||||0129670999||||Jimmy

Puchong|Puchong|||||||||0129678888||||Peter

*Note:

Please make sure all Master File Code like Country Code exists in Master

(22)

31. Stock Mode

Name Type

1. Stock Model Code Varchar(20)

Text File Format:

Stock Model

Stock Model Code|Description

Example Text File:

Stock Model

Samsung Galaxy|Samsung Galaxy

Samsung Tab|Samsung Tab

32. Stock/Service Item

Master Fields

Name Type

1. Record Type Default M

2. Stock Code Varchar(30)

5. Service Y/N Default N

Y  Yes N  No

6. Base UOM Varchar(20)

7. Sales Price 1 (Base UOM) Decimal(20, 6) Default 0.000000 8. Sales Price 2 (Base UOM) Decimal(20, 6) Default 0.000000 9. Sales Price 3 (Base UOM) Decimal(20, 6) Default 0.000000 10. Sales Price 4 (Base UOM) Decimal(20, 6) Default 0.000000 11. Sales Price 5 (Base UOM) Decimal(20, 6) Default 0.000000

12. Sales Price 1 Discount Varchar(100) Default Empty

(23)

18. Purchase Price (Base UOM) Decimal(20, 6) Default 0.000000 19. Reorder Level (Base UOM) Decimal(20, 6) Default 0.000000

20. Reorder Qty Decimal(20, 6) Default 0.000000

21. Reorder UOM Varchar(20) Default Empty

22. Default Sales Qty Decimal(20, 6) Default 1

23. Default Sales UOM Varchar(20) Default Empty

24. Default Purchase Qty Decimal(20, 6) Default 1

25. Default Purchase UOM Varchar(20) Default Empty

26. Stock Category Code Varchar(20) Default Empty

27. Stock Group Code Varchar(20) Default Empty

28. Stock Class Code Varchar(20) Default Empty

29. Stock Brand Code Varchar(20) Default Empty

30. Stock Model Code Varchar(20) Default Empty

31. Stock Size Varchar(20) Default Empty

32. Stock Color Varchar(20) Default Empty

33. Stock Weight Varchar(20) Default Empty

34. Stock Ref 1 Varchar(20) Default Empty

42. Barcode Varchar(30) Default Empty

43. Minimum Qty Decimal(20, 6) Default 0.000000

44. Maximum Qty Decimal(20, 6) Default 0.000000

45. Stock Shelf Varchar(100) Default Empty

46. Default GST Type – Supply Varchar(20) Default Empty

47. Sales Tax (%) Decimal(20, 2) Default 0.00

48. Default GST Type – Purchase Varchar(20) Default Empty

49. Purchases Tax (%) Decimal(20, 2) Default 0.00

50. MSIC Code Varchar(5) Default Empty

51. Alternate Code Varchar(30) Default Empty

Multiple UOM

Name Type

1. Record Type Default D-UOM

2. UOM Varchar(20)

3. UOM Name Varchar(200) Default Empty

4. Rate/Base UOM Decimal(20, 6) Default > 0

5. Sales Price Decimal(20, 6) Default 0.000000

6. Minimum Price Decimal(20, 6) Default 0.000000

(24)

Text File Format:

Stock/Service Item

M|Stock Code|Name|Name 2|Service|Base UOM|Sales Price 1|Sales Price 2|Sales Price

3|Sales Price 4|Sales Price 5|Sales Price 1 Discount|Sales Price 2 Discount|Sales Price 3

Discount|Sales Price 4 Discount|Sales Price 5 Discount|Minimum Price|Purchase

Price|Reorder Level|Reorder Qty|Reorder UOM|Default Sales Qty|Default Sales

UOM|Default Purchases Qty|Default Purchases UOM|Stock Category Code|Stock Group

Code|Stock Class Code|Stock Brand Code|Stock Model Code|Stock Size|Stock

Color|Stock Weight|Stock Ref 1|Stock Ref 2|Stock Ref 3|Stock Ref 4|Stock Ref 5|Stock

Ref 6|Stock Ref 7|Stock Ref 8|Barcode|Minimum Qty|Maximum Qty|Stock

Shelf|Default GST Type - Supply|Sales Tax|Default GST Type - Purchase|Purchases

Tax|MSIC Code|Alternate Code

D- UOM|UOM|UOM Name|Rate/Base UOM|Sales Price|Minimum Price|Purchases Price

Example Text File:

Stock/Service

M|S00001|Samsung Galaxy

Note||N|Unit|2400.00|0.00|0.00|0.00|0.00|10%|||||0.00|1800.00|5|2|Unit|1|Unit|

1|Unit||||||||||||||||||0|0||SR|6.00|TX|6.00|46521|

M|S00002|Samsung Galaxy Note

2||N|Unit|1800.00|0.00|0.00|0.00|0.00|10%|||||0.00|1500.00|5|2|Unit|1|Unit|1|U

nit||||||||||||||||||0|0||SR|6.00|TX|6.00|46521|

D-UOM|Double|Double Unit|2|3500.00|3200.00|3000.00

D-UOM|Triple|Triple Unit|3|5000.00|4500.00|4800.00

M|S00003|Samsung Galaxy S5

2||N|Unit|1900.00|0.00|0.00|0.00|0.00|5%|||||0.00|1500.00|5|2|Unit|1|Unit|1|U

nit||||||||||||||||||0|0||SR|6.00|TX|6.00|46521|

*Note:

Please make sure all Master File Code like Stock Category Code, Stock

Group Code, Stock Class Code, Stock Brand Code, Stock Model Code, GST

Type Code & MSIC Code Code exists in Master Accounting Software before

import the Stock/Service.

(25)

33. Supervisor

Name Type

1. Supervisor Code Varchar(20)

3. Position Varchar(200) Default Empty

Text File Format:

Supervisor

Supervisor Code|Description|

Position

Example Text File:

Supervisor

Super|Super|Administrator

Susan|Susan|

Peter|Peter Low|Manager

*Note:

After import supervisor into Master Accounting, you need to manual set

the password & the access right in Supervisor screen. By Default all imported

supervisor access right is set to True.

34. Term

Name Type

1. Term Code Varchar(20) 2. Description Varchar(200)

3. Term Type Day/Month/Month End Default Day 4. Due In Integer Default 0 (Based on Term Type)