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

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

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

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

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

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.7.1.37) - 20 Dec 2018
• Evaluation Limit : Unlimited (XML File) & 100 Records (Text File)
• http://www.estream.com.my/downloadfile/Fairy/SQLAccTxtImpV5-setup.exe
• MD5 : 170764594A11E3533C58BF68018798B1

• 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 38-- Coming Soon...

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

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

[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

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

See also

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