SQL Text Import

Introduction

Is External Shareware Program which able to import master file & transactions to SQLAccounting in Text or CSV format.

Pros

1. Easy to generate.
2. About 80% of modules is covered.
3. Support GST
4. FREE import for Master file.

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. There is a cost for End User and Dealer. Only Free if each file record less then 100.

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

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; DETAIL;DOCNO;ACCOUNT;DESCRIPTION;PROJECT;TAX;TAXAMT;TAXINCLUSIVE;AMOUNT;CODE;

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	----
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
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

Customer Payment

Below is also applicable for

• Customer Payment - AR_PM -> GST-AR_PM.txt
• Supplier Payment - AP_SP -> GST-AP_SP.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	----
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	1 - True 0 - False
DETAIL			Y	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 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

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 Credit Note - SL_CN
• 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 Return - PH_SC
• Purchase Cash Purchase - PH_CP

Example file -> 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; 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;

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	----
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	----
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	Y		GL Account to be Debit/Credit
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

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"""

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.

Import Program

• Version (5.5.0.16) -19 Dec 2016
• Evaluation Limit :

- 100 Records per text file

- Unlimited for XML File

• http://www.estream.com.my/downloadfile/Fairy/SQLAccTxtImpV5-setup.exe
• MD5 : 90512F751F7A3C168789CA2320C9639E

• Version (4.4.0.15) - 11 May 2016
• http://www.estream.com.my/downloadfile/Fairy/SQLAccTxtImp-setup.exe
• MD5 : EEF179664E1DB5B171590AC07A86AC39

History New/Updates/Changes

--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.

--Build 15--

• Fixed Import Stock Error.
• Add Link to SQL Acc. File | Run.

--Build 14--

• Fixed Import Maintain Customer & Supplier Error

--Build 13--

• Fixed Import GL PV & OR Bad Variable Error.

--Build 12--

• Fixed Verify Customer & Supplier Payment Verify Error.

--Build 11--

• Fixed Status keep shown False even is Registered.

--Build 10--

• Upgrade to Version 4.4.
• Fix import More Description had spacing for each charactor.
• Enable Support TEncoding.UTF8.

--Build 9--

• Upgrade to Version 4.3.
• Add Application Version at Statusbar.
• Extended trial limit to 100 records.
• Remove limit for Master File Import.

--Build 8--

• Fix Unable Compare Long Address in Register Form.

--Build 7--

• Fix Unable import SL_CS if SQL Acc Tax is Inclusive.
• Enable support Nonrefundable.

Todo

• Add Customer & Supplier Refund
• Add TaxDate field for Supplier,Purchase & Customer/Sales Credit Note
• Add GST Gift

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

Steps

1. Set the Range to Import (Useful if the list too long & only available for Registred version)
2. Click Get File button to select the Txt or csv file
3. Click Verify To Check any duplicate or Valid Maintenance Code (eg Area, Agent, etc)
4. Click the Dropdown List (see below pic) to see all the error
5. Click Post To A/c to Post (Only Tick with Status show ok will post)

This is example error on the drop down status

Video

• Text Import

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

See also

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