SQL Text Import

Introduction

Is External Shareware Program which able to import master file & transactions to SQLAccounting in

• Text or CSV format
• XML format - SQL XML Import

This Program also able to Export XML Format from Different SQL Accounting Database.

Pros

1. Easy to generate.
2. About 80% of modules is covered.
3. Support GST

Cons

1. Only supported Semi-Comma(;)
2. Only can do New/Insert action.
3. Description3 not support multi line.
4. Maintain Stock Item not covered.
5. Is Batch update.
6. Not support UDF (User Define Fields)
7. Same DocNo for Different Supplier not cover
8. No Transfer Status(eg DO to IV Status)

Notes 1. FileName can be any name. 2. File Extension can be .txt or .csv 3. 1 DocType/BizObject 1 txt file. Wanted FREEWARE?...Try Generate each Text file max to 100 records

Guideline & Example

SQLAcc-TextImport-Guide.pdf
SQLAcc-TextImport-Example

Below is the most commonly use tables & other may refer to the pdf link above

Maintain Customer

Below is also applicable for

• Maintain Customer - AR_Customer
• Maintain Supplier - AP_Supplier

Example file -> GST-AR_Customer.txt

Format
MASTER;CODE;CONTROLACCOUNT;COMPANYNAME;COMPANYNAME2;COMPANYCATEGORY;AREA;AGENT;BIZNATURE;CREDITTERM;CREDITLIMIT;CURRENCYCODE;ALLOWEXCEEDCREDITLIMIT;PRICETAG;TAX;TAXEXEMPTNO;TAXEXPDATE;REGISTERNO;GSTNO;REMARK;CREATIONDATE;REGISTERNO2; DETAIL;CODE;BRANCHNAME;ADDRESS1;ADDRESS2;ADDRESS3;ADDRESS4;ATTENTION;PHONE1;PHONE2;FAX1;FAX2;EMAIL;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
CODE	String	10	Y		Customer/Supplier Account Code
CONTROLACCOUNT	String	10	Y		Customer/Supplier Control Account Code
COMPANYNAME	String	100	Y		Customer/Supplier Name
COMPANYNAME2	String	100	N		Customer/Supplier Name2
COMPANYCATEGORY	String	10	Y	----	Customer/Supplier Category Code
AREA	String	10	Y	----
AGENT	String	10	Y	----	Salesman Code
BIZNATURE	String	100	N		Customer/Supplier Natural of Business
CREDITTERM	String	10	Y		Credit Terms (eg 30 Days, C.O.D., etc)
CREDITLIMIT	FMTBcd	2	Y	0	Credit Term Limit Amount
CURRENCYCODE	String	10	Y	----	Customer/Supplier Currency Code
ALLOWEXCEEDCREDITLIMIT	String	1	Y	T	T - True F - False
PRICETAG	String	10	N		Customer/Supplier Price Tag Code
TAX	String	10	N		Customer/Supplier Default GST Code (eg ZR, NR, etc)
TAXEXEMPTNO	String	50	N		Customer/Supplier Tax Exemption No
TAXEXPDATE	Date	0	N		In DD/MM/YYYY format Customer/Supplier Tax Exemption Expired Date
REGISTERNO	String	20	N		Customer/Supplier Company Registration No.
GSTNO	String	25	N		Customer/Supplier Company GST No.
REMARK	String	80	N
CREATIONDATE	Date	0	N		In DD/MM/YYYY format Customer/Supplier Account Created Date
REGISTERNO2	String	30	N		Customer/Supplier Company New Registration No. Only Available in Version 875.782 & above
DETAIL			Y	DETAIL	Fixed/Hardcode
CODE	String	10	Y		Customer/Supplier Account Code
BRANCHNAME	String	100	Y	1st Branch Address - BILLING 2nd & more Branch Address - BRANCH2, BRANCH3, etc	Customer/Supplier Branch Address Name
ADDRESS1	String	40	N		Customer/Supplier Address 1
ADDRESS2	String	40	N		Customer/Supplier Address 2
ADDRESS3	String	40	N		Customer/Supplier Address 3
ADDRESS4	String	40	N		Customer/Supplier Address 4
ATTENTION	String	70	N		Customer/Supplier Content Person Name
PHONE1	String	30	N		Customer/Supplier Name Phone 1
PHONE2	String	30	N		Customer/Supplier Name Phone 2
FAX1	String	30	N		Customer/Supplier Fax 1
FAX2	String	30	N		Customer/Supplier Fax 2
EMAIL	String	200	N		Customer/Supplier EMAIL Address

[top]

Customer Invoice

Below is also applicable for

• Customer Invoice - AR_IV
• Customer Debit Note - AR_DN
• Supplier Invoice - AP_PI
• Supplier Debit Note - AP_SD

Example file -> GST-AR_IV.txt

Format
MASTER;DOCNO;DOCNOEX;DOCDATE;POSTDATE;CODE;TERMS;DESCRIPTION;AREA;AGENT;PROJECT;CURRENCYRATE;DOCAMT;CANCELLED;TAXDATE; DETAIL;DOCNO;ACCOUNT;DESCRIPTION;PROJECT;TAX;TAXAMT;TAXINCLUSIVE;AMOUNT;CODE;TAXRATE;TARIFF;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
DOCNO	String	20	Y		Invoice/Debit Note Document Number
DOCNOEX	String	20	N		Additional Document Number
DOCDATE	Date	0	Y		In DD/MM/YYYY format
POSTDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
CODE	String	10	Y		Customer/Supplier Account Code
TERMS	String	10	Y		Credit Terms (eg 30 Days, C.O.D., etc)
DESCRIPTION	String	150	Y	See Remark	Description to be shown at Customer/Supplier Statement Default Value For AR_IV -> Sales AR_DN/AP_SD -> Debit Note AR_CN/AP_SC -> Credit Note AP_PI -> Purchase
AREA	String	10	Y	----
AGENT	String	10	Y	----	Salesman Code
PROJECT	String	20	Y	----
CURRENCYRATE	FMTBcd	6	Y	1
DOCAMT	FMTBcd	2	Y	0	Document Amount
CANCELLED	String	1	Y	F	T - True F - False
TAXDATE	Date	0	N		In DD/MM/YYYY format For Supplier Module Only The Date must be Latest or Equal to DocDate
DETAIL			Y	DETAIL	Fixed/Hardcode
DOCNO	String	20	Y		Invoice/Debit Note Document Number
ACCOUNT	String	10	Y		GL Account to be Debit/Credit
DESCRIPTION	String	80	Y		Product Description
PROJECT	String	20	Y	----
TAX	String	10	N		Tax Code (eg SR, TX, ZRL, etc)
TAXAMT	FMTBcd	2	Y	0
TAXINCLUSIVE	Integer	0	Y	0	1 - True 0 - False
AMOUNT	FMTBcd	2	Y	0	eg Product Qty * Unit Price (Excluding GST)
CODE	String	10	Y		Customer/Supplier Account Code
TAXRATE	String	20	N		Tax Rate
TARIFF	String	20	C		Tariff or HS Code Mandatory - If is SST Registered Person

[top]

Customer Payment

Below is also applicable for

• Customer Payment - AR_PM -> GST-AR_PM.txt
• Supplier Payment - AP_SP -> GST-AP_SP.txt
• Customer Refund - AR_CF -> GST-AR_CF.txt
• Supplier Refund - AP_SF -> GST-AP_SF.txt

Format
MASTER;DOCNO;COMPANYCODE;DOCDATE;POSTDATE;DESCRIPTION;AREA;AGENT;PAYMENTMETHOD;CHEQUENUMBER;PROJECT;PAYMENTPROJECT;CURRENCYRATE;BANKCHARGE;DOCAMT;UNAPPLIEDAMT;CANCELLED;NONREFUNDABLE; DETAIL;DOCNO;DOCTYPE;KODOCNO;KOAMT;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
DOCNO	String	20	Y		OR/PV Document Number
COMPANYCODE	String	10	Y		Customer Code
DOCDATE	Date	0	Y		In DD/MM/YYYY format
POSTDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
DESCRIPTION	String	150	Y	Payment For Account	Description to be shown at Customer/Supplier Statement
AREA	String	10	Y	----
AGENT	String	10	Y	----	Salesman Code
PAYMENTMETHOD	String	10	Y		GL Bank/Cash Account Code
CHEQUENUMBER	String	20	N
PROJECT	String	20	Y	----
PAYMENTPROJECT	String	20	Y	----
CURRENCYRATE	FMTBcd	6	Y	1
BANKCHARGE	FMTBcd	2	Y	0
DOCAMT	FMTBcd	2	Y	0	Document Amount
UNAPPLIEDAMT	FMTBcd	2	Y	0	Payment Amt - OffSet Invoice Amt
CANCELLED	String	1	Y	F	T - True F - False
NONREFUNDABLE	Integer	0	Y	0	For Customer Payment Only 1 - True 0 - False
DETAIL			C	DETAIL	Fixed/Hardcode
DOCNO	String	20	C		OR/PV Document Number C = May set to empty if no Knock Off
DOCTYPE	String	2	C		Knock Off/Off set Document Type IV - Customer Invoice DN - Customer Debit Note PI - Supplier Invoice SD - Supplier Debit Note PM - Customer Payment (For Customer Refund) CN - Customer Credit Note (For Customer Refund) SP - Suppler Payment (For Supplier Refund) SC - Supplier Credit Note (For Supplier Refund) C = May set to empty if no Knock Off
KODOCNO	String	20	C		Knock Off/Off set Invoice/Debit Note Number C = May set to empty if no Knock Off
KOAMT	FMTBcd	2	Y	0	Knock Off/Off set Invoice/Debit Note Amount

[top]

Customer Credit Note

Below is also applicable for

• Customer Credit Note - AR_CN -> GST-AR_CN-002.txt
• Supplier Credit Note - AP_SC

Format
MASTER;DOCNO;DOCNOEX;DOCDATE;POSTDATE;CODE;DESCRIPTION;AREA;AGENT;PROJECT;CURRENCYRATE;DOCAMT;UNAPPLIEDAMT;CANCELLED;TAXDATE; DETAIL;DOCNO;ACCOUNT;DESCRIPTION;PROJECT;TAX;TAXAMT;TAXINCLUSIVE;AMOUNT;CODE;TAXRATE;TARIFF; DETAIL1;DOCNO;DOCTYPE;KODOCNO;KOAMT;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
DOCNO	String	20	Y		CN Document Number
DOCNOEX	String	20	Y		Additional Document Number
DOCDATE	Date	0	Y		In DD/MM/YYYY format
POSTDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
CODE	Date	10	Y		Customer Code
DESCRIPTION	String	150	Y	Payment For Account	Description to be shown at Customer/Supplier Statement
AREA	String	10	Y	----
AGENT	String	10	Y	----	Salesman Code
PROJECT	String	20	Y	----
CURRENCYRATE	FMTBcd	6	Y	1
DOCAMT	FMTBcd	2	Y	0	Document Amount
UNAPPLIEDAMT	FMTBcd	2	Y	0	Payment Amt - OffSet Invoice Amt
CANCELLED	String	1	Y	F	T - True F - False
TAXDATE	Date	0	N		In DD/MM/YYYY format The Date must be Latest or Equal to DocDate
DETAIL			Y	DETAIL	Fixed/Hardcode
DOCNO	String	20	Y		CN Document Number
ACCOUNT	String	10	Y		GL Account to be Debit/Credit
DESCRIPTION	String	80	Y		Product Description
PROJECT	String	20	Y	----
TAX	String	10	N		Tax Code (eg SR, TX, ZRL, etc)
TAXAMT	FMTBcd	2	Y	0
TAXINCLUSIVE	Integer	0	Y	0	1 - True 0 - False
AMOUNT	FMTBcd	2	Y	0	eg Product Qty * Unit Price (Excluding GST)
CODE	String	10	Y		Customer/Supplier Account Code
TAXRATE	String	20	N		Tax Rate
TARIFF	String	20	C		Tariff or HS Code Mandatory - If is SST Registered Person
DETAIL1			Y	DETAIL1	Fixed/Hardcode
DOCNO	String	20	C		OR/PV Document Number C = May set to empty if no Knock Off
DOCTYPE	String	2	C		Knock Off/Off set Document Type IV - Customer Invoice DN - Customer Debit Note PI - Supplier Invoice SD - Supplier Debit Note C = May set to empty if no Knock Off
KODOCNO	String	20	C		Knock Off/Off set Invoice/Debit Note Number C = May set to empty if no Knock Off
KOAMT	FMTBcd	2	Y	0	Knock Off/Off set Invoice/Debit Note Amount

[top]

Sales Invoice

Below is also applicable for

• Sales Quotation - SL_QT
• Sales Order - SL_SO
• Sales Delivery Order - SL_DO
• Sales Invoice - SL_IV
• Sales Debit Note - SL_DN
• Sales Cash Sales - SL_CS -> GST-SLCS.txt
• Purchase Request - PH_PQ
• Purchase Order - PH_PO
• Goods Received Note - PH_GR
• Purchase Invoice - PH_PI -> GST-PH_PI.txt
• Purchase Debit Note - PH_SD
• Purchase Cash Purchase - PH_CP

Example file
-> SST-SL-003.txt (Sales)
-> GST-SL-005.txt (Sales) & GST-PH-003.txt (Purchase)

Format

MASTER;DOCNO;DOCNOEX;DOCDATE;POSTDATE;CODE;COMPANYNAME;ADDRESS1;ADDRESS2;ADDRESS3;ADDRESS4;PHONE1;FAX1;ATTENTION;AREA;AGENT;PROJECT;TERMS;CURRENCYRATE;DESCRIPTION;CANCELLED;DOCAMT;VALIDITY;DELIVERYTERM;CC;DOCREF1;DOCREF2;DOCREF3;DOCREF4;BRANCHNAME;DADDRESS1;DADDRESS2;DADDRESS3;DADDRESS4;DATTENTION;DPHONE1;DFAX1;TRANSFERABLE;D_AMOUNT;P_PAYMENTMETHOD;P_CHEQUENUMBER;P_BANKCHARGE;P_AMOUNT;P_PAYMENTPROJECT;TAXDATE;EIVDATETIME;POSTCODE;CITY;STATE;COUNTRY;DPOSTCODE;DCITY;DSTATE;DCOUNTRY;SALESTAXNO;SERVICETAXNO;TIN;IDTYPE;IDNO;TOURISMNO;SIC;INCOTERMS;SUBMISSIONTYPE;IRBM_UUID;IRBM_LONGID;IRBM_STATUS; DETAIL;DOCNO;NUMBER;ITEMCODE;LOCATION;PROJECT;DESCRIPTION;DESCRIPTION2;DESCRIPTION3;QTY;UOM;SUOMQTY;UNITPRICE;DELIVERYDATE;DISC;TAX;TAXAMT;AMOUNT;PRINTABLE;ACCOUNT;TRANSFERABLE;REMARK1;REMARK2;TAXINCLUSIVE;BATCH;CODE;TAXRATE;TARIFF;TAXEXEMPTIONREASON;IRBM_CLASSIFICATION;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
DOCNO	String	20	Y		Document Number
DOCNOEX	String	20	N		Additional Document Number
DOCDATE	Date	0	Y		In DD/MM/YYYY format
POSTDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
CODE	String	10	Y		Customer/Supplier Code
COMPANYNAME	String	100	Y		Customer/Supplier Name
ADDRESS1	String	40	Y		Customer/Supplier Address 1
ADDRESS2	String	40	Y		Customer/Supplier Address 2
ADDRESS3	String	40	Y		Customer/Supplier Address 3
ADDRESS4	String	40	Y		Customer/Supplier Address 4
PHONE1	String	30	Y		Customer/Supplier Name Phone 1
FAX1	String	30	Y		Customer/Supplier Fax 1
ATTENTION	String	70	Y		Customer/Supplier Content Person Name
AREA	String	10	Y	----
AGENT	String	10	Y	----	Salesman Code
PROJECT	String	20	Y	----
TERMS	String	10	Y		Credit Terms (eg 30 Days, C.O.D., etc)
CURRENCYRATE	FMTBcd	6	Y	1
DESCRIPTION	String	150	Y	See Note Tab	Description to be shown at Customer/Supplier Statement
CANCELLED	String	1	Y	F	T - True F - False
DOCAMT	FMTBcd	2	Y	0	Document Amount
VALIDITY	String	150	N		Additional Document Number
DELIVERYTERM	String	150	N		Additional Document Number
CC	String	150	N		Additional Document Number
DOCREF1	String	25	N		Additional Document Number
DOCREF2	String	25	N		Additional Document Number
DOCREF3	String	25	N		Additional Document Number
DOCREF4	String	25	N		Additional Document Number
BRANCHNAME	String	100	Y	BILLING	Fixed/Hardcode
DADDRESS1	String	40	Y		Customer/Supplier Delivery Address 1
DADDRESS2	String	40	Y		Customer/Supplier Delivery Address 2
DADDRESS3	String	40	Y		Customer/Supplier Delivery Address 3
DADDRESS4	String	40	Y		Customer/Supplier Delivery Address 4
DATTENTION	String	70	Y		Customer/Supplier Delivery Contact Person
DPHONE1	String	30	Y		Customer/Supplier Delivery Phone 1
DFAX1	String	30	Y		Customer/Supplier Delivery Fax 1
TRANSFERABLE	String	1	Y	T	Fixed/Hardcode
D_AMOUNT	FMTBcd	2	Y	0	Fixed/Hardcode
P_PAYMENTMETHOD	String	10	N		GL Bank/Cash Account Code
P_CHEQUENUMBER	String	20	N
P_BANKCHARGE	FMTBcd	2	Y	0
P_AMOUNT	FMTBcd	2	Y	0
P_PAYMENTPROJECT	String	20	Y	----
TAXDATE	Date	0	N		In DD/MM/YYYY format For Purchase Module Only The Date must be Latest or Equal to DocDate
EIVDATETIME	DateTime	0	N		E-Invocing Submited Date time in the UTC timezone eg 19/07/2024 7:17:43 AM
POSTCODE	String	10	Y		Customer/Supplier Postcode
CITY	String	50	Y		Customer/Supplier City
STATE	String	50	Y		Customer/Supplier State
COUNTRY	String	2	Y		Customer/Supplier County
DPOSTCODE	String	10	Y		Customer/Supplier Delivery Postcode
DCITY	String	50	Y		Customer/Supplier Delivery City
DSTATE	String	50	Y		Customer/Supplier Delivery State
DCOUNTRY	String	2	Y		Customer/Supplier Delivery County
SALESTAXNO	String	25	Y
SERVICETAXNO	String	25	Y
TIN	String	14	Y
IDTYPE	Integer	0	Y	0	0 - Empty 1 - New Reg No 2 - NRIC 3 - Passport 4 - ARMY
IDNO	String	20	Y
TOURISMNO	String	17	Y
SIC	String	10	Y		For Purchase Only Malaysia Standard Industrial Classification (MSIC) Codes https://sdk.myinvois.hasil.gov.my/codes/#msic-codes
INCOTERMS	String	3	Y
SUBMISSIONTYPE	Integer	0	Y	17	0 = None 17 = E-Invoice / Self Billing(for Purchase) 18 = Consolidate
IRBM_UUID	String	26	Y
IRBM_LONGID	String	50	Y
IRBM_STATUS	Integer	0	Y	0	E-Invoicing Submited Status https://sdk.myinvois.hasil.gov.my/faq/ eg 2
DETAIL			Y	DETAIL	Fixed/Hardcode
DOCNO	String	20	Y		Document Number
NUMBER	String	5	N
ITEMCODE	String	30	N		Product Code From Maintain Item
LOCATION	String	20	Y	----	Product Location to be IN/OUT, Code Follow Maintain Location Code
PROJECT	String	20	Y	----
DESCRIPTION	String	200	Y		Product Description
DESCRIPTION2	String	200	N
DESCRIPTION3	Blob	8	N
QTY	FMTBcd	4	Y	1
UOM	String	10	C		Unit of Measurement C = Y If using ITEMCODE
SUOMQTY	FMTBcd	4	Y	0	Fixed/Hardcode
UNITPRICE	FMTBcd	8	Y
DELIVERYDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
DISC	String	20	N
TAX	String	10	N
TAXAMT	FMTBcd	2	Y	0
AMOUNT	FMTBcd	2	Y		Qty * Unit Price (Excluding GST)
PRINTABLE	String	1	Y	T	Fixed/Hardcode
ACCOUNT	String	10	C		GL Account to be Debit/Credit Mandatory if item code field empty
TRANSFERABLE	String	1	Y	T	Fixed/Hardcode
REMARK1	String	200	N
REMARK2	String	200	N
TAXINCLUSIVE	Integer	0	Y	0	1 - True 0 - False
BATCH	String	30	N		Batch No Code Follow Maintain Batch Code
CODE	String	10	Y		Customer/Supplier Code
TAXRATE	String	20	N		Tax Rate
TARIFF	String	20	C		Tariff or HS Code Mandatory - If is SST Registered Person and Post without Item Code
TAXEXEMPTIONREASON	String	300	C		Mandatory if Tax code is Tax Exemption
IRBM_CLASSIFICATION	String	3	Y

[top]

Sales Credit Note

Below is also applicable for

• Sales Credit Note - SL_CN
• Purchase Return - PH_SC

Example file
-> SST-SLCN-003withKO.txt (Sales)

Format

MASTER;DOCNO;DOCNOEX;DOCDATE;POSTDATE;CODE;COMPANYNAME;ADDRESS1;ADDRESS2;ADDRESS3;ADDRESS4;PHONE1;FAX1;ATTENTION;AREA;AGENT;PROJECT;TERMS;CURRENCYRATE;DESCRIPTION;CANCELLED;DOCAMT;VALIDITY;DELIVERYTERM;CC;DOCREF1;DOCREF2;DOCREF3;DOCREF4;BRANCHNAME;DADDRESS1;DADDRESS2;DADDRESS3;DADDRESS4;DATTENTION;DPHONE1;DFAX1;TRANSFERABLE;D_AMOUNT;P_PAYMENTMETHOD;P_CHEQUENUMBER;P_BANKCHARGE;P_AMOUNT;P_PAYMENTPROJECT;TAXDATE; DETAIL;DOCNO;NUMBER;ITEMCODE;LOCATION;PROJECT;DESCRIPTION;DESCRIPTION2;DESCRIPTION3;QTY;UOM;SUOMQTY;UNITPRICE;DELIVERYDATE;DISC;TAX;TAXAMT;AMOUNT;PRINTABLE;ACCOUNT;TRANSFERABLE;REMARK1;REMARK2;TAXINCLUSIVE;BATCH;CODE;TAXRATE;TARIFF; DETAIL1;DOCNO;DOCTYPE;KODOCNO;KOAMT;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
DOCNO	String	20	Y		Document Number
DOCNOEX	String	20	N		Additional Document Number
DOCDATE	Date	0	Y		In DD/MM/YYYY format
POSTDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
CODE	String	10	Y		Customer/Supplier Code
COMPANYNAME	String	100	Y		Customer/Supplier Name
ADDRESS1	String	40	Y		Customer/Supplier Address 1
ADDRESS2	String	40	Y		Customer/Supplier Address 2
ADDRESS3	String	40	Y		Customer/Supplier Address 3
ADDRESS4	String	40	Y		Customer/Supplier Address 4
PHONE1	String	30	Y		Customer/Supplier Name Phone 1
FAX1	String	30	Y		Customer/Supplier Fax 1
ATTENTION	String	70	Y		Customer/Supplier Content Person Name
AREA	String	10	Y	----
AGENT	String	10	Y	----	Salesman Code
PROJECT	String	20	Y	----
TERMS	String	10	Y		Credit Terms (eg 30 Days, C.O.D., etc)
CURRENCYRATE	FMTBcd	6	Y	1
DESCRIPTION	String	150	Y	See Note Tab	Description to be shown at Customer/Supplier Statement
CANCELLED	String	1	Y	F	T - True F - False
DOCAMT	FMTBcd	2	Y	0	Document Amount
VALIDITY	String	150	N		Additional Document Number
DELIVERYTERM	String	150	N		Additional Document Number
CC	String	150	N		Additional Document Number
DOCREF1	String	25	N		Additional Document Number
DOCREF2	String	25	N		Additional Document Number
DOCREF3	String	25	N		Additional Document Number
DOCREF4	String	25	N		Additional Document Number
BRANCHNAME	String	100	Y	BILLING	Fixed/Hardcode
DADDRESS1	String	40	Y		Customer/Supplier Delivery Address 1
DADDRESS2	String	40	Y		Customer/Supplier Delivery Address 2
DADDRESS3	String	40	Y		Customer/Supplier Delivery Address 3
DADDRESS4	String	40	Y		Customer/Supplier Delivery Address 4
DATTENTION	String	70	Y		Customer/Supplier Delivery Contact Person
DPHONE1	String	30	Y		Customer/Supplier Delivery Phone 1
DFAX1	String	30	Y		Customer/Supplier Delivery Fax 1
TRANSFERABLE	String	1	Y	T	Fixed/Hardcode
D_AMOUNT	FMTBcd	2	Y	0	Fixed/Hardcode
P_PAYMENTMETHOD	String	10	N		GL Bank/Cash Account Code
P_CHEQUENUMBER	String	20	N
P_BANKCHARGE	FMTBcd	2	Y	0
P_AMOUNT	FMTBcd	2	Y	0
P_PAYMENTPROJECT	String	20	Y	----
TAXDATE	Date	0	N		In DD/MM/YYYY format For Purchase Module Only The Date must be Latest or Equal to DocDate
DETAIL			Y	DETAIL	Fixed/Hardcode
DOCNO	String	20	Y		Document Number
NUMBER	String	5	N
ITEMCODE	String	30	N		Product Code From Maintain Item
LOCATION	String	20	Y	----	Product Location to be IN/OUT, Code Follow Maintain Location Code
PROJECT	String	20	Y	----
DESCRIPTION	String	200	Y		Product Description
DESCRIPTION2	String	200	N
DESCRIPTION3	Blob	8	N
QTY	FMTBcd	4	Y	1
UOM	String	10	C		Unit of Measurement C = Y If using ITEMCODE
SUOMQTY	FMTBcd	4	Y	0	Fixed/Hardcode
UNITPRICE	FMTBcd	8	Y
DELIVERYDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
DISC	String	20	N
TAX	String	10	N
TAXAMT	FMTBcd	2	Y	0
AMOUNT	FMTBcd	2	Y		Qty * Unit Price (Excluding GST)
PRINTABLE	String	1	Y	T	Fixed/Hardcode
ACCOUNT	String	10	C		GL Account to be Debit/Credit Mandatory if item code field empty
TRANSFERABLE	String	1	Y	T	Fixed/Hardcode
REMARK1	String	200	N
REMARK2	String	200	N
TAXINCLUSIVE	Integer	0	Y	0	1 - True 0 - False
BATCH	String	30	N		Batch No Code Follow Maintain Batch Code
CODE	String	10	Y		Customer/Supplier Code
TAXRATE	String	20	N		Tax Rate
TARIFF	String	20	C		Tariff or HS Code Mandatory - If is SST Registered Person and Post without Item Code
DETAIL1			Y	DETAIL1	Fixed/Hardcode
DOCNO	String	20	C		OR/PV Document Number C = May set to empty if no Knock Off
DOCTYPE	String	2	C		Knock Off/Off set Document Type IV - Customer Invoice DN - Customer Debit Note PI - Supplier Invoice SD - Supplier Debit Note C = May set to empty if no Knock Off
KODOCNO	String	20	C		Knock Off/Off set Invoice/Debit Note Number C = May set to empty if no Knock Off
KOAMT	FMTBcd	2	Y	0	Knock Off/Off set Invoice/Debit Note Amount

[top]

Journal Voucher

Example file
-> SST-GL_JE-003.txt
-> GST-GL_JE-003.txt

Format
MASTER;DOCNO;DOCDATE;POSTDATE;DESCRIPTION;CANCELLED; DETAIL;DOCNO;CODE;DESCRIPTION;REF;PROJECT;DR;LOCALDR;CR;LOCALCR;TAX;TAXAMT;TAXINCLUSIVE;TAXRATE;TARIFF;

Table Fields Properties
Field Name	Field Type	Field Size	Mandatory	Default Value	Remarks
MASTER			Y	MASTER	Fixed/Hardcode
DOCNO	String	20	Y		JV Document Number
DOCDATE	Date	0	Y		In DD/MM/YYYY format
POSTDATE	Date	0	Y	Same As DocDate	In DD/MM/YYYY format
DESCRIPTION	String	150	Y		Description to be shown at GL Ledger
CANCELLED	String	1	Y	F	T - True F - False
DETAIL			Y	DETAIL	Fixed/Hardcode
DOCNO	String	20	Y		JV Document Number
Code	String	10	Y		GL Account to be Debit/Credit
DESCRIPTION	String	80	Y		Description to be shown at GL Ledger
REF	String	25	N		Additional Document Ref. No.
PROJECT	String	20	Y	----
DR	FMTBcd	2	Y	0	Debit Foreign Currency Value
LOCALDR	FMTBcd	2	Y	0	Debit Local Currency Value
CR	FMTBcd	2	Y	0	Credit Foreign Currency Value
LOCALCR	FMTBcd	2	Y	0	Credit Local Currency Value
TAX	String	10	N		Tax Code (eg SR, TX, ZRL, etc)
TAXAMT	FMTBcd	2	Y	0
TAXINCLUSIVE	Integer	0	Y	0	1 - True 0 - False
TAXRATE	String	20	N		Tax Rate
TARIFF	String	20	C		Tariff or HS Code Mandatory - If is SST Registered Person

[top]

Special Character

User Input	Text Output
SalesOrder	SalesOrder
Sales Order 5' 12"	"Sales Order 5' 12"""
SalesOrder6'13"	"SalesOrder6'13"""
SalesOrder6" x 13"	"SalesOrder6"" x 13"""
Sales Order	"Sales Order"
Sales Order 5' 12	"Sales Order 5' 12"
Sales Order 12"	"Sales Order 12"""
Sales;Order 12	"Sales;Order 12"

01. One module One file (i.e. Sales Invoice have Sales Invoice text file and Sales Debit Note have Sale Debit Note file).
02. Make sure NO empty line on the last line of the files.

[top]

Import Program

• Version (5.11.3.65) - 12 Aug 2024
• Evaluation Limit : Unlimited
• SQLAccTxtXMLImp
• MD5 : A97C2D8A1CE36371DB7CD49D0DE5FC65

• Version (4.4.0.15) - 11 May 2016 - (Deprecated)
• SQLAccTxtImp
• MD5 : EEF179664E1DB5B171590AC07A86AC39

History New/Updates/Changes

--Build 64--

• Fixed Not Connected to SDK when manually Import.

--Build 64--

• Enable Support Import E-Invoicing for SL/PH (For Txt Import).
• Enable Quick Column Customise for Grid.
• Fixed Auto Import XML error.

--Build 63--

• Upgrade to Version 5.11
• Upgrade compiler to D28.
• Enable Export & Import Supplier Deposit(XML Only).

--Build 62--

• Fixed XML Untick Records not load all.

--Build 61--

• Fixed XML Replace for Payment KO become 1 KO.

--Build 60--

• Fixed XML Replace for Credit Note Error CurrencyRate can't Change due to Knock off.
• Fixed out of memory when Get File.

--Build 59--

• Add Header Description for Sales & Purchase.
• Fixed Header Description for Sales & Purchase not support Unicode.

--Build 58--

• Export Stock - Add Customer/Supplier Price Tag filter by Company or Price Tag.
• Export Stock - Fixed Filter Category not working.

--Build 57--

• Upgrade to Version 5.10.
• Add Export XML for Maintain Item.
• Move Fast Export XML to Export XML Menu.
• Remove Params Tag Value on Export XML.
• Format XML file Output for more easy to Read.

--Build 56--

• Fixed Auto Import Hang if setting incorrect password.
• Fixed XML Import not support Unicode.

--Build 55-

• Fixed File Not move to Done Folder after Done import.

--Build 54--

• Fixed Verify Customer Invoice No. should excluded Company Code.
• Fixed Auto Import unable to Login due to password.
• Fixed Download from FTP zip file unable to unzip.

--Build 53--

• Fixed unable to untick record for Text Import.
• Fixed Auto Run Not Force Kill SQLAcc after import.
• Fixed Auto Import Company Name not shown in Lookup.
• Fixed FTP - Not able move to Archive Folder.
• Auto Import should by pass file not .ZIP.
• Enable Editing for all Grid.
• Add Option Get File From FTP.

--Build 52--

• Upgrade to Version 5.9.
• Add Auto kill SQLAcc function for Auto Posting.
• Enable with Knock off when Import Sales Credit Note for Text Import.

--Build 51--

• Fixed Fast XML Export no caption.
• Fixed Fast Import error when in Limited user.

--Build 50--

• Fixed JV Verify not excluding Debtor, Creditor & Stock code.

--Build 49--

• Fixed Import XML Credit Note without Knock Off Info. Error.

--Build 48--

• Fixed Import XML Payment without KOTaxDate Field Error.

--Build 47--

• Fixed Import Maintain Item for XML prompt Key Violation due to IsBase not in 1st Row.

--Build 46--

• Upgrade to Version 5.8.3.
• Enable support Import Customer Deposit for XML.
• Add Tools | Fast XML Export... function.
• Add Help | Get Text Guideline & Example... function.
• Add Help | Get XML Guideline & Example... function.
• Change Fast XML Import Icon.

--Build 45--

• Add Show Checked Only, Show UnChecked Only & Show All Options.
• Fixed Import Text Unapplied shown 0 even is No Knock Off Info when Detail had only 1 Record.
• Fixed Import XML Knockoff not import the KOTAXDATE.

--Build 44--

• Fixed XML Import Barcode always in Base UOM.

--Build 43--

• Enable Support Version 782 & above (New ROC).

--Build 42--

• Fixed Maintain Customer/Supplier Verify shown Invalid CurrencyCode even is valid.
• Enable Multi Select for Sales,Purchase & Stock Import Grid.

--Build 41--

• Fixed Maintain Supplier XML Error if had Bank Information.
• Fixed Maintain Customer/Supplier Verify shown Invalid CurrencyCode even is valid.

--Build 40--

• Fixed Customer/Supplier Invoice XML Master/Detail not link
• Fix History Year still shown as 2018.

--Build 39--

• Upgrade to Version 5.8.2.
• Fix slow when open import if lot of Customer/Supplier.
• Change new icon.
• Enable support Running In Windows Limited User.

--Build 38--

• Upgrade to Version 5.8.
• Fixed Option lost after uninstall & install again.
• Total rewrite the Auto Schedule Import to Support download from simple FTP.

--Build 37--

• Update Fast Import Seq - Should Import SL then only AR
• Fixed Get XML master detail not correctly link
• Fixed Schedule Import Access Violation.

--Build 36--

• Fixed XML Tariff & Bank not import.
• Fixed Action Column too small.

--Build 35--

• Fixed XML Tariff not import if had Tax Code.

--Build 34--

• Fixed XML for Customer/Supplier Payment unable to import for SQL Accounting Version 768.

--Build 33--

• Upgrade to Version 5.7.1.
• Txt Import - Add TaxRate & Tariff.

--Build 32--

• Fix Import XML without TaxRate field error.

--Build 31--

• Fix Import XML TaxRate Not import

--Build 30--

• Trim all value for text import before Get & Import.

--Build 29--

• Fix unable to import Maintain Customer/Supplier for XML for SQLAcc Version 756 & above

--Build 28--

• Fix unable to load non archive file XML file.

--Build 27--

• Fix GL PV & OR Verify Status not correct.
• Fix GL PV & OR Detail & Area not posted.

--Build 26--

• Fix Missing Register Database Option.
• Fix Scrollbar not shown.

--Build 25--

• Upgrade Version to 5.7.
• Fix Fast Import keep blinking on get Zip file.
• Add Maintain Stock Group Import - TXT
• Add Auto Import Settings function - XML
• Add Customer Refund Import
• Add Supplier Refund Import
• Set Auto Untick for Error in Verify for Fast Import.

--Build 24--

• Fix Currency Rate only get 4 decimal point.
• Enable Replace Action for Payment & Credit Note for XML Import.

--Build 23--

• Fix Prompt error on Import Text SO.

--Build 22--

• Fix XML Import for SL/PH item code will "gone" due to CompanyItemCode field.
• Fix Fast Import Bad variable Type error on Some Case.

--Build 21--

• Add checking CompanyName & Remark for XML.
• Add support Import TaxDate for AP, PH & Customer & Sales CN.

--Build 20--

• Upgrade to Version 5.6.
• Upgrade to XE25;
• Remove Text Import Limit.
• Fixed XML Detail Field checking not correct shown status.

--Build 19--

• Fixed Access Violation on Import Sales CN & PR For Text.

--Build 18--

• Fixed Error Bad variable type For XML.

--Build 17--

• Fixed Unable to Import Contra For XML.

--Build 16--

• Upgrade to Version 5.5
• Remove Auto Clear Posted Records with options.
• Enable Support SQLAcc Unicode database.
• Add Tool | Options... funtion.
• Combine Import Project Source Code with AgentArea Source Code.
• Add Import Maintain Terms.
• Add Import Maintain Currency.
• Add Record Range for Maintain Agent, Area, Terms & Project.
• Enable Import from XML for Maintain Agent, Area, Terms, Currency, Project & Company Category.
• Add Import Maintain Stock Category.
• Add Fast XML Import.
• Enable Import from XML for Customer & Supplier Module.
• Enable Import from XML for Sales & Purchase Module.
• Enable Import from XML for Stock Issue, Received & Adjustment.
• Fixed Import DO Error.
• Fixed Number Not Import.
• Enable Import from XML for GL OR & GL PV.
• Enable Import from XML for GL Journal Voucher.
• Enable support multi line for Description3 using #13.
• Enable Import Same DocNo for Supplier & Purchase Module.
• Fixed Import Detail Not Follow Text Detail sequence.

[top]

Todo & Know Bugs

• Add Customer & Supplier Contra - TXT
• Add GST Gift, Stock Transfer & Job Order - TXT & XML
• Same DocNo but diff. customer verify not correct

Useful tools

• http://www.diffnow.com/

Installation

1. Install sqlaccounting
2. Create Sample Data / Login to the Database wanted to import
3. Login (username & password is Admin)
4. Install the Import Program (Username & Password is sqlutility)
5. Run the Import Program

[top]

Settings

Options

Menu: Tools | Options

Options	Description
DefaultType	Default File Type on open For Selecting file Dialog TXT -> For Text Import XML -> For XML Import
DefaultPath	Reserved for Future use
ClearAfterPost	Auto Delete the success posted 0 -> False 1 -> True
DisplayFormat	Display Format for all Float/Currency type field(s)
CheckProfile	Check against with Profile.txt before load the XML data For XML Import Only Only Alert if not match

[top]

Auto Import Settings

Menu: File | Auto Import Settings...
Only available

• Build 25 & above
• Support XML Only
• Network Server setting (i.e. not support for Standalone)

For Build 38 & above

01. Select SQL Accounting DCF file

02. Select SQL Accounting Database to import & export

03. Enter the SQL Accounting User Name & Password

04. Select/Tick to Enable Download From FTP (available in Version Build 32 & above)

05. Click Next

The following Steps if Download From FTP is Enable/Tick/Selected

06. Enter the Host/IP Address for FTP

07. Enter the FTP Port No

08. Enter the FTP User Name & Password

09. Enter the FTP Folder to download From

10. Select the Action to Do After Download

11. Click Test Connection...

12. Click Next

Action	Description
Delete the file(s) in FTP Folder	Will delete the file(s) after download
Move to Archive/YYYYMMDD Folder	Will Auto Create YYYYMMDD & move to this folder after done download Make sure in the FTP folder had the folder Archive (eg download/Archive)
Leave it As It(I will self Manually Delete or Move it)	Do nothing after Download

The file will download to the Pending Folder which is set at the Last Steps

13. Select the Pending folder where Zip files keep to import to SQL Accounting

14. Select the Done folder to move after done import the file

Field	Description
Last Import Date	Last Import Date and Time. System will auto update
Keep Last Log Line	To shown last how many line for Log

15. Click Finish Button to Close.

[top]

For Build 25 till 37

01. Select the SQL Accounting DCF file.

02. Select the Database to import

03. Click Test Connection... button to test Firebird setting

04. Click Next button

05. Enter Database login User Name

06. Enter Database login Password

07. Click Test Password... button to test User Name & Password

08. Select Pending Folder where the file to be import

09. Select Done Folder where the file had done/complete import

10. Select Log Folder where the log file to save on import status

11. Click Finish button to save the settings

[top]

Windows Schedule

01. Open Windows Control Panel | Administrative Tools | Task Scheduler

02. Click Action | Create Basic Task...

03. Enter Task Name (eg SQL Acc Auto Import) & Click Next

04. Select Daily & Click Next

05. Set the Start Date & Time to Start
06. Set 1 for Recur every field & Click Next

07. Select Start a program & Click Next

For Build 38 & above
08. Click Browse button & find the SQLAccTxtXMLImp.exe (Default at C:\eStream\Utilities\SQLAccTxtImp)
08A. Set the Add arguments(optional) as -Auto

For Build 25 till 37
08. Click Browse button & find the SQLSchAutoImp.exe (Default at C:\eStream\Utilities\SQLAccTxtImp)
09. Set the Start in(optional) path to the Exe folder (eg. C:\eStream\Utilities\SQLAccTxtImp) & Click Next

10. Tick the option Open the Properties dialog for this task when I click Finish
11. Click Finish

12. Select the option Run whether user is logged on or not
13. Click Triggers tab at top

14. Click Edit... button

15. Change the time to run
16. Click OK 2 times

17. Enter the window User Name & Password
18. Click Ok

[top]

Steps To Import

Below is Example to Import Sales Invoice From Text File

01. Click Sales | Invoice...

02. Set the Range to Import (Useful if the list too long & only available for Registered version for Text Import)

03. Click Get File button to select the Txt/csv or XML file

04. Select File Type to Import Text (or XML File for XML Import)

05. Select Your file & Click Open Button.

06. Click Verify To Check any duplicate or Valid Maintenance Code (eg Area, Agent, etc)

07. Click the Dropdown List (see below pic) to see all the error

08. Click Post To A/c to Post (Only Tick with Status show ok will post)

This is example error on the drop down status

[top]

Video

• SQL Acc - SQL Import v5

FAQ

Do the text import Description3 support multi line?

Yes if you using the Version 5 & above. All you had to do is to set each line using #13

Example

DETAIL;POS4;;;----;----;"SALES - Foods";;"Line 1#13# Line 2#13Line 3#13#13B#13C";50;UNIT;0;21.20;25/12/2016;;SR;60;1000;T;500-000;T;;;1;;

Result

Line 1
# Line 2
Line 3

B
C

[top]

My Data had Special Character/Symbol (eg Ø) how to handle it?

If you had special Character/Symbol, try convert it to UTF-8 before you export out to text file

See also

• FAQ
• SQL Accounting Linking
• SDK Live
• SQL XML Import
• SQL Acc XLS n MDB Import
• Others Customisation