(66 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
==Introduction== | ==Introduction== | ||
Is External Shareware Program which able to import master file & transactions to SQLAccounting in Text or CSV format. | 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=== | ===Pros=== | ||
Line 8: | Line 12: | ||
===Cons=== | ===Cons=== | ||
# Only supported Semi-Comma | # Only supported <span style="color:#0000ff">Semi-Comma(;)</span> | ||
# Only can do New/Insert action. | # Only can do New/Insert action. | ||
# Description3 not support multi line. | # Description3 not support multi line. | ||
Line 15: | Line 19: | ||
# Not support UDF (User Define Fields) | # Not support UDF (User Define Fields) | ||
# Same DocNo for Different Supplier not cover | # Same DocNo for Different Supplier not cover | ||
# No Transfer Status(eg DO to IV Status) | |||
{| style="margin: 1em auto 1em auto;" | {| style="margin: 1em auto 1em auto;" | ||
Line 34: | Line 39: | ||
==Guideline & Example== | ==Guideline & Example== | ||
[ | [https://download.sql.com.my/customer/Fairy/SQLAcc-TextImport-Guide.pdf SQLAcc-TextImport-Guide.pdf] <br> | ||
[ | [https://download.sql.com.my/customer/Fairy/Example-Text.zip SQLAcc-TextImport-Example] | ||
Below is the most commonly use tables & other may refer to the pdf link above | Below is the most commonly use tables & other may refer to the pdf link above | ||
Line 49: | Line 54: | ||
|- | |- | ||
| | | | ||
MASTER;CODE;CONTROLACCOUNT;COMPANYNAME;COMPANYNAME2;COMPANYCATEGORY;AREA;AGENT;BIZNATURE;CREDITTERM;CREDITLIMIT;CURRENCYCODE;ALLOWEXCEEDCREDITLIMIT;PRICETAG;TAX;TAXEXEMPTNO;TAXEXPDATE;REGISTERNO;GSTNO;REMARK;CREATIONDATE;<br /> | MASTER;CODE;CONTROLACCOUNT;COMPANYNAME;COMPANYNAME2;COMPANYCATEGORY;AREA;AGENT;BIZNATURE;CREDITTERM;CREDITLIMIT;CURRENCYCODE;ALLOWEXCEEDCREDITLIMIT;PRICETAG;TAX;TAXEXEMPTNO;TAXEXPDATE;REGISTERNO;GSTNO;REMARK;CREATIONDATE;REGISTERNO2;<br /> | ||
DETAIL;CODE;BRANCHNAME;ADDRESS1;ADDRESS2;ADDRESS3;ADDRESS4;ATTENTION;PHONE1;PHONE2;FAX1;FAX2;EMAIL; | DETAIL;CODE;BRANCHNAME;ADDRESS1;ADDRESS2;ADDRESS3;ADDRESS4;ATTENTION;PHONE1;PHONE2;FAX1;FAX2;EMAIL; | ||
|} | |} | ||
Line 110: | Line 115: | ||
* In DD/MM/YYYY format | * In DD/MM/YYYY format | ||
* Customer/Supplier Account Created Date | * Customer/Supplier Account Created Date | ||
|- | |||
| REGISTERNO2||String||align="center" |30||align="center" |N|||| | |||
* Customer/Supplier Company New Registration No. | |||
* Only Available in Version 875.782 & above | |||
|- style="background: #CEE0F2" | |- style="background: #CEE0F2" | ||
| DETAIL||||||align="center" |Y||align="center" |DETAIL||Fixed/Hardcode | | DETAIL||||||align="center" |Y||align="center" |DETAIL||Fixed/Hardcode | ||
Line 447: | Line 456: | ||
* Sales Invoice - SL_IV | * Sales Invoice - SL_IV | ||
* Sales Debit Note - SL_DN | * Sales Debit Note - SL_DN | ||
* Sales Cash Sales - SL_CS -> GST-SLCS.txt | * Sales Cash Sales - SL_CS -> GST-SLCS.txt | ||
* Purchase Request - PH_PQ | * Purchase Request - PH_PQ | ||
Line 454: | Line 462: | ||
* Purchase Invoice - PH_PI -> GST-PH_PI.txt | * Purchase Invoice - PH_PI -> GST-PH_PI.txt | ||
* Purchase Debit Note - PH_SD | * Purchase Debit Note - PH_SD | ||
* Purchase Cash Purchase - PH_CP | * Purchase Cash Purchase - PH_CP | ||
Line 460: | Line 467: | ||
-> SST-SL-003.txt (Sales) <br> | -> SST-SL-003.txt (Sales) <br> | ||
-> GST-SL-005.txt (Sales) & GST-PH-003.txt (Purchase) <br> | -> GST-SL-005.txt (Sales) & GST-PH-003.txt (Purchase) <br> | ||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;" | |||
! 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; | |||
|} | |||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;" | |||
! Table Fields Properties | |||
|- | |||
| align="center" |'''Field Name''' | |||
| align="center" |'''Field Type''' | |||
| align="center" |'''Field Size''' | |||
| align="center" |'''Mandatory''' | |||
| align="center" |'''Default Value''' | |||
| align="center" |'''Remarks''' | |||
|- style="background: #CEE0F2" | |||
| MASTER||||||align="center" |Y||align="center" |MASTER||Fixed/Hardcode | |||
|- | |||
| DOCNO||String||align="center" |20||align="center" |Y||||Document Number | |||
|- | |||
| DOCNOEX||String||align="center" |20||align="center" |N||||Additional Document Number | |||
|- | |||
| DOCDATE||Date||align="center" |0||align="center" |Y||||In DD/MM/YYYY format | |||
|- | |||
| POSTDATE||Date||align="center" |0||align="center" |Y||Same As DocDate||In DD/MM/YYYY format | |||
|- | |||
| CODE||String||align="center" |10||align="center" |Y||||Customer/Supplier Code | |||
|- | |||
| COMPANYNAME||String||align="center" |100||align="center" |Y||||Customer/Supplier Name | |||
|- | |||
| ADDRESS1||String||align="center" |40||align="center" |Y||||Customer/Supplier Address 1 | |||
|- | |||
| ADDRESS2||String||align="center" |40||align="center" |Y||||Customer/Supplier Address 2 | |||
|- | |||
| ADDRESS3||String||align="center" |40||align="center" |Y||||Customer/Supplier Address 3 | |||
|- | |||
| ADDRESS4||String||align="center" |40||align="center" |Y||||Customer/Supplier Address 4 | |||
|- | |||
| PHONE1||String||align="center" |30||align="center" |Y||||Customer/Supplier Name Phone 1 | |||
|- | |||
| FAX1||String||align="center" |30||align="center" |Y||||Customer/Supplier Fax 1 | |||
|- | |||
| ATTENTION||String||align="center" |70||align="center" |Y||||Customer/Supplier Content Person Name | |||
|- | |||
| AREA||String||align="center" |10||align="center" |Y||align="center" |----|| | |||
|- | |||
| AGENT||String||align="center" |10||align="center" |Y||align="center" |----||Salesman Code | |||
|- | |||
| PROJECT||String||align="center" |20||align="center" |Y||align="center" |----|| | |||
|- | |||
| TERMS||String||align="center" |10||align="center" |Y||||Credit Terms (eg 30 Days, C.O.D., etc) | |||
|- | |||
| CURRENCYRATE||FMTBcd||align="center" |6||align="center" |Y||align="center" |1|| | |||
|- | |||
| DESCRIPTION||String||align="center" |150||align="center" |Y||See Note Tab||Description to be shown at Customer/Supplier Statement | |||
|- | |||
| CANCELLED||String||align="center" |1||align="center" |Y||align="center" |F|| | |||
* T - True | |||
* F - False | |||
|- | |||
| DOCAMT||FMTBcd||align="center" |2||align="center" |Y||align="center" |0||Document Amount | |||
|- | |||
| VALIDITY||String||align="center" |150||align="center" |N||||Additional Document Number | |||
|- | |||
| DELIVERYTERM||String||align="center" |150||align="center" |N||||Additional Document Number | |||
|- | |||
| CC||String||align="center" |150||align="center" |N||||Additional Document Number | |||
|- | |||
| DOCREF1||String||align="center" |25||align="center" |N||||Additional Document Number | |||
|- | |||
| DOCREF2||String||align="center" |25||align="center" |N||||Additional Document Number | |||
|- | |||
| DOCREF3||String||align="center" |25||align="center" |N||||Additional Document Number | |||
|- | |||
| DOCREF4||String||align="center" |25||align="center" |N||||Additional Document Number | |||
|- | |||
| BRANCHNAME||String||align="center" |100||align="center" |Y||align="center" |BILLING||Fixed/Hardcode | |||
|- | |||
| DADDRESS1||String||align="center" |40||align="center" |Y||||Customer/Supplier Delivery Address 1 | |||
|- | |||
| DADDRESS2||String||align="center" |40||align="center" |Y||||Customer/Supplier Delivery Address 2 | |||
|- | |||
| DADDRESS3||String||align="center" |40||align="center" |Y||||Customer/Supplier Delivery Address 3 | |||
|- | |||
| DADDRESS4||String||align="center" |40||align="center" |Y||||Customer/Supplier Delivery Address 4 | |||
|- | |||
| DATTENTION||String||align="center" |70||align="center" |Y||||Customer/Supplier Delivery Contact Person | |||
|- | |||
| DPHONE1||String||align="center" |30||align="center" |Y||||Customer/Supplier Delivery Phone 1 | |||
|- | |||
| DFAX1||String||align="center" |30||align="center" |Y||||Customer/Supplier Delivery Fax 1 | |||
|- | |||
| TRANSFERABLE||String||align="center" |1||align="center" |Y||align="center" |T||Fixed/Hardcode | |||
|- | |||
| D_AMOUNT||FMTBcd||align="center" |2||align="center" |Y||align="center" |0||Fixed/Hardcode | |||
|- | |||
| P_PAYMENTMETHOD||String||align="center" |10||align="center" |N||||GL Bank/Cash Account Code | |||
|- | |||
| P_CHEQUENUMBER||String||align="center" |20||align="center" |N|||| | |||
|- | |||
| P_BANKCHARGE||FMTBcd||align="center" |2||align="center" |Y||align="center" |0|| | |||
|- | |||
| P_AMOUNT||FMTBcd||align="center" |2||align="center" |Y||align="center" |0|| | |||
|- | |||
| P_PAYMENTPROJECT||String||align="center" |20||align="center" |Y||align="center" |----|| | |||
|- | |||
| TAXDATE||Date||align="center" |0||align="center" |N|||| | |||
* In DD/MM/YYYY format | |||
* For Purchase Module Only | |||
* The Date must be Latest or Equal to DocDate | |||
|-style="background: #ffffcc" | |||
| EIVDATETIME || DateTime ||align="center" |0||align="center" |N|||| | |||
* E-Invocing Submited Date time in the UTC timezone | |||
* eg 19/07/2024 7:17:43 AM | |||
|-style="background: #ffffcc" | |||
| POSTCODE || String||align="center" |10||align="center" |Y|||| Customer/Supplier Postcode | |||
|-style="background: #ffffcc" | |||
| CITY || String||align="center" |50||align="center" |Y|||| Customer/Supplier City | |||
|-style="background: #ffffcc" | |||
| STATE || String||align="center" |50||align="center" |Y|||| Customer/Supplier State | |||
|-style="background: #ffffcc" | |||
| COUNTRY || String||align="center" |2||align="center" |Y|||| Customer/Supplier County | |||
|-style="background: #ffffcc" | |||
| DPOSTCODE || String||align="center" |10||align="center" |Y|||| Customer/Supplier Delivery Postcode | |||
|-style="background: #ffffcc" | |||
| DCITY || String||align="center" |50||align="center" |Y|||| Customer/Supplier Delivery City | |||
|-style="background: #ffffcc" | |||
| DSTATE || String||align="center" |50||align="center" |Y|||| Customer/Supplier Delivery State | |||
|-style="background: #ffffcc" | |||
| DCOUNTRY || String||align="center" |2||align="center" |Y|||| Customer/Supplier Delivery County | |||
|-style="background: #ffffcc" | |||
| SALESTAXNO|| String||align="center" |25||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| SERVICETAXNO || String||align="center" |25||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| TIN|| String||align="center" |14||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| IDTYPE|| Integer ||align="center" |0||align="center" |Y||align="center" |0|| | |||
* 0 - Empty | |||
* 1 - New Reg No | |||
* 2 - NRIC | |||
* 3 - Passport | |||
* 4 - ARMY | |||
|-style="background: #ffffcc" | |||
| IDNO|| String||align="center" |20||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| TOURISMNO|| String||align="center" |17||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| SIC|| String||align="center" |10||align="center" |Y|||| | |||
* For Purchase Only | |||
* Malaysia Standard Industrial Classification (MSIC) Codes | |||
* https://sdk.myinvois.hasil.gov.my/codes/#msic-codes | |||
|-style="background: #ffffcc" | |||
| INCOTERMS|| String||align="center" |3||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| SUBMISSIONTYPE|| Integer ||align="center" |0||align="center" |Y||align="center" |17|| | |||
* 0 = None | |||
* 17 = E-Invoice / Self Billing(for Purchase) | |||
* 18 = Consolidate | |||
|-style="background: #ffffcc" | |||
| IRBM_UUID|| String||align="center" |26||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| IRBM_LONGID|| String||align="center" |50||align="center" |Y|||| | |||
|-style="background: #ffffcc" | |||
| IRBM_STATUS|| Integer ||align="center" |0||align="center" |Y||align="center" |0|| | |||
* E-Invoicing Submited Status | |||
* https://sdk.myinvois.hasil.gov.my/faq/ | |||
* eg 2 | |||
|- style="background: #CEE0F2" | |||
| DETAIL||||||align="center" |Y||align="center" |DETAIL||Fixed/Hardcode | |||
|- | |||
| DOCNO||String||align="center" |20||align="center" |Y||||Document Number | |||
|- | |||
| NUMBER||String||align="center" |5||align="center" |N|||| | |||
|- | |||
| ITEMCODE||String||align="center" |30||align="center" |N||||Product Code From Maintain Item | |||
|- | |||
| LOCATION||String||align="center" |20||align="center" |Y||align="center" |----||Product Location to be IN/OUT, Code Follow Maintain Location Code | |||
|- | |||
| PROJECT||String||align="center" |20||align="center" |Y||align="center" |----|| | |||
|- | |||
| DESCRIPTION||String||align="center" |200||align="center" |Y||||Product Description | |||
|- | |||
| DESCRIPTION2||String||align="center" |200||align="center" |N|||| | |||
|- | |||
| DESCRIPTION3||Blob||align="center" |8||align="center" |N|||| | |||
|- | |||
| QTY||FMTBcd||align="center" |4||align="center" |Y||align="center" |1|| | |||
|- | |||
| UOM||String||align="center" |10||align="center" |C||||Unit of Measurement<br /> | |||
C = Y If using ITEMCODE | |||
|- | |||
| SUOMQTY||FMTBcd||align="center" |4||align="center" |Y||align="center" |0||Fixed/Hardcode | |||
|- | |||
| UNITPRICE||FMTBcd||align="center" |8||align="center" |Y|||| | |||
|- | |||
| DELIVERYDATE||Date||align="center" |0||align="center" |Y||Same As DocDate||In DD/MM/YYYY format | |||
|- | |||
| DISC||String||align="center" |20||align="center" |N|||| | |||
|- | |||
| TAX||String||align="center" |10||align="center" |N|||| | |||
|- | |||
| TAXAMT||FMTBcd||align="center" |2||align="center" |Y||align="center" |0|| | |||
|- | |||
| AMOUNT||FMTBcd||align="center" |2||align="center" |Y||||Qty * Unit Price (Excluding GST) | |||
|- | |||
| PRINTABLE||String||align="center" |1||align="center" |Y||align="center" |T||Fixed/Hardcode | |||
|- | |||
| ACCOUNT||String||align="center" |10||align="center" |C|||| | |||
* GL Account to be Debit/Credit | |||
* Mandatory if item code field empty | |||
|- | |||
| TRANSFERABLE||String||align="center" |1||align="center" |Y||align="center" |T||Fixed/Hardcode | |||
|- | |||
| REMARK1||String||align="center" |200||align="center" |N|||| | |||
|- | |||
| REMARK2||String||align="center" |200||align="center" |N|||| | |||
|- | |||
| TAXINCLUSIVE||Integer||align="center" |0||align="center" |Y||align="center" |0|| | |||
* 1 - True | |||
* 0 - False | |||
|- | |||
| BATCH||String||align="center" |30||align="center" |N||||Batch No Code Follow Maintain Batch Code | |||
|- | |||
| CODE||String||align="center" |10||align="center" |Y||||Customer/Supplier Code | |||
|- | |||
| TAXRATE ||String||align="center" |20||align="center" |N||||Tax Rate | |||
|- | |||
| TARIFF ||String||align="center" |20||align="center" |C|||| | |||
* Tariff or HS Code | |||
* Mandatory - If is SST Registered Person and Post without '''Item Code''' | |||
|- style="background: #ffffcc" | |||
| TAXEXEMPTIONREASON ||String||align="center" |300||align="center" |C|||| Mandatory if Tax code is Tax Exemption | |||
|- style="background: #ffffcc" | |||
| IRBM_CLASSIFICATION||String||align="center" |3||align="center" |Y|||| | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===Sales Credit Note=== | |||
Below is also applicable for | |||
* Sales Credit Note - SL_CN | |||
* Purchase Return - PH_SC | |||
Example file <br> | |||
-> SST-SLCN-003withKO.txt (Sales) | |||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;" | {| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;" | ||
Line 466: | Line 724: | ||
| | | | ||
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;<br /> | 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;<br /> | ||
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; | 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;<br /> | ||
DETAIL1;DOCNO;DOCTYPE;KODOCNO;KOAMT; | |||
|} | |} | ||
Line 636: | Line 895: | ||
* Tariff or HS Code | * Tariff or HS Code | ||
* Mandatory - If is SST Registered Person and Post without '''Item Code''' | * Mandatory - If is SST Registered Person and Post without '''Item Code''' | ||
|- style="background: #CEE0F2" | |||
| DETAIL1||||||align="center" |Y||align="center" |DETAIL1||Fixed/Hardcode | |||
|- | |||
| DOCNO||String||align="center" |20||align="center" |C||||OR/PV Document Number<br /> | |||
C = May set to empty if no Knock Off | |||
|- | |||
| DOCTYPE||String||align="center" |2||align="center" |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||align="center" |20||align="center" |C||||Knock Off/Off set Invoice/Debit Note Number<br /> | |||
C = May set to empty if no Knock Off | |||
|- | |||
| KOAMT||FMTBcd||align="center" |2||align="center" |Y||align="center" |0||Knock Off/Off set Invoice/Debit Note Amount | |||
|} | |} | ||
Line 732: | Line 1,009: | ||
|- | |- | ||
| Sales Order 12" || "Sales Order 12""" | | Sales Order 12" || "Sales Order 12""" | ||
|- | |||
| Sales;Order 12 || "Sales;Order 12" | |||
|} | |} | ||
Line 740: | Line 1,019: | ||
==Import Program== | ==Import Program== | ||
* Version (5. | * Version (5.11.3.65) - 12 Aug 2024 | ||
* Evaluation Limit : Unlimited | * Evaluation Limit : Unlimited | ||
* | * [https://download.sql.com.my/customer/Fairy/SQLAccTxtXMLImp-setup.exe SQLAccTxtXMLImp] | ||
* MD5 : | * MD5 : A97C2D8A1CE36371DB7CD49D0DE5FC65 | ||
* Version (4.4.0.15) - 11 May 2016 | * Version (4.4.0.15) - 11 May 2016 - '''(Deprecated)''' | ||
* | * [https://www.sql.com.my/downloadfile/Fairy/SQLAccTxtImp-setup.exe SQLAccTxtImp] | ||
* MD5 : EEF179664E1DB5B171590AC07A86AC39 | * MD5 : EEF179664E1DB5B171590AC07A86AC39 | ||
===History New/Updates/Changes=== | ===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-- | --Build 37-- | ||
* Update Fast Import Seq - Should Import SL then only AR | * Update Fast Import Seq - Should Import SL then only AR | ||
Line 855: | Line 1,256: | ||
* Enable Import Same DocNo for Supplier & Purchase Module. | * Enable Import Same DocNo for Supplier & Purchase Module. | ||
* Fixed Import Detail Not Follow Text Detail sequence. | * Fixed Import Detail Not Follow Text Detail sequence. | ||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
Line 943: | Line 1,309: | ||
* Network Server setting (i.e. not support for Standalone) | * Network Server setting (i.e. not support for Standalone) | ||
====For Build 38 & above==== | |||
[[File:Excel-Import-28.jpg|800px|center]] | |||
: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 | |||
[[File:Excel-Import-36.jpg|800px|center]] | |||
:The following Steps if <span style="color:#0000ff">Download From FTP</span> 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 | |||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |||
! 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 <span style="color:#0000ff">Archive</span> (eg download/Archive) | |||
|- | |||
| Leave it As It(I will self Manually Delete or Move it) || Do nothing after Download | |||
|} | |||
{| style="margin: 1em auto 1em auto;" | |||
|- | |||
| | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
{| | |||
|- | |||
| [[File:Template.Tips-01.jpg|60px]]|| The file will download to the Pending Folder which is set at the Last Steps | |||
|} | |||
---------------------------------------------------------------------------------------------------------------------------------------------------- | |||
|} | |||
[[File:SQLTxtImport.Fig04.jpg|800px|center]] | |||
: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 | |||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |||
! 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. | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
====For Build 25 till 37==== | |||
[[File:AutoImport-01.jpg|800 px|center]] | [[File:AutoImport-01.jpg|800 px|center]] | ||
:01. Select the SQL Accounting '''DCF''' file. | :01. Select the SQL Accounting '''DCF''' file. | ||
Line 973: | Line 1,398: | ||
07. Select ''Start a program'' & Click Next | 07. Select ''Start a program'' & Click Next | ||
[[File:AutoImport-04.jpg|center]] | [[File:AutoImport-04.jpg|center]] | ||
<span style="color:#0000ff">For Build 38 & above</span><br /> | |||
08. Click Browse button & find the '''SQLAccTxtXMLImp.exe''' (Default at ''C:\eStream\Utilities\SQLAccTxtImp'')<br /> | |||
08A. Set the <span style="color:#0000ff">Add arguments(optional)</span> as '''-Auto''' <br /> <br /> | |||
<span style="color:#0000ff">For Build 25 till 37</span><br /> | |||
08. Click Browse button & find the '''SQLSchAutoImp.exe''' (Default at ''C:\eStream\Utilities\SQLAccTxtImp'')<br /> | 08. Click Browse button & find the '''SQLSchAutoImp.exe''' (Default at ''C:\eStream\Utilities\SQLAccTxtImp'')<br /> | ||
09. Set the Start in(optional) path to the Exe folder (eg. ''C:\eStream\Utilities\SQLAccTxtImp'') & Click Next | 09. Set the Start in(optional) path to the Exe folder (eg. ''C:\eStream\Utilities\SQLAccTxtImp'') & Click Next | ||
[[File:AutoImport-05.jpg|center]] | [[File:AutoImport-05.jpg|center]] | ||
10. Tick the option ''Open the Properties dialog for this task when I click Finish''<br /> | 10. Tick the option ''Open the Properties dialog for this task when I click Finish''<br /> | ||
Line 1,030: | Line 1,462: | ||
C | C | ||
</pre> | </pre> | ||
<div style="float: right;"> [[#top|[top]]]</div> | |||
===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== | ==See also== |
Latest revision as of 04:51, 5 October 2024
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
- Easy to generate.
- About 80% of modules is covered.
- Support GST
Cons
- Only supported Semi-Comma(;)
- Only can do New/Insert action.
- Description3 not support multi line.
- Maintain Stock Item not covered.
- Is Batch update.
- Not support UDF (User Define Fields)
- Same DocNo for Different Supplier not cover
- No Transfer Status(eg DO to IV Status)
|
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; |
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 |
|
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 |
| |
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 |
| |
REGISTERNO2 | String | 30 | N |
| |
DETAIL | Y | DETAIL | Fixed/Hardcode | ||
CODE | String | 10 | Y | Customer/Supplier Account Code | |
BRANCHNAME | String | 100 | Y |
|
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 | |
String | 200 | N | Customer/Supplier EMAIL Address |
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; |
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
|
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 |
|
TAXDATE | Date | 0 | N |
| |
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 |
|
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 |
|
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; |
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 |
|
NONREFUNDABLE | Integer | 0 | Y | 0 |
For Customer Payment Only
|
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
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 |
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; |
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 |
|
TAXDATE | Date | 0 | N |
| |
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 |
|
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 |
| |
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
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 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 |
|
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 |
| |
EIVDATETIME | DateTime | 0 | N |
| |
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 |
|
IDNO | String | 20 | Y | ||
TOURISMNO | String | 17 | Y | ||
SIC | String | 10 | Y |
| |
INCOTERMS | String | 3 | Y | ||
SUBMISSIONTYPE | Integer | 0 | Y | 17 |
|
IRBM_UUID | String | 26 | Y | ||
IRBM_LONGID | String | 50 | Y | ||
IRBM_STATUS | Integer | 0 | Y | 0 |
|
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 |
| |
TRANSFERABLE | String | 1 | Y | T | Fixed/Hardcode |
REMARK1 | String | 200 | N | ||
REMARK2 | String | 200 | N | ||
TAXINCLUSIVE | Integer | 0 | Y | 0 |
|
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 |
| |
TAXEXEMPTIONREASON | String | 300 | C | Mandatory if Tax code is Tax Exemption | |
IRBM_CLASSIFICATION | String | 3 | Y |
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; |
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 |
|
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 |
| |
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 |
| |
TRANSFERABLE | String | 1 | Y | T | Fixed/Hardcode |
REMARK1 | String | 200 | N | ||
REMARK2 | String | 200 | N | ||
TAXINCLUSIVE | Integer | 0 | Y | 0 |
|
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 |
| |
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
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 |
Journal Voucher
Example file
-> SST-GL_JE-003.txt
-> GST-GL_JE-003.txt
Format |
---|
MASTER;DOCNO;DOCDATE;POSTDATE;DESCRIPTION;CANCELLED; |
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 |
|
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 |
|
TAXRATE | String | 20 | N | Tax Rate | |
TARIFF | String | 20 | C |
|
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.
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.
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
Installation
- Install sqlaccounting
- Create Sample Data / Login to the Database wanted to import
- Login (username & password is Admin)
- Install the Import Program (Username & Password is sqlutility)
- Run the Import Program
Settings
Options
Menu: Tools | Options
Options | Description |
---|---|
DefaultType | Default File Type on open For Selecting file Dialog
|
DefaultPath | Reserved for Future use |
ClearAfterPost | Auto Delete the success posted
|
DisplayFormat | Display Format for all Float/Currency type field(s) |
CheckProfile |
|
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 |
|
Leave it As It(I will self Manually Delete or Move it) | Do nothing after Download |
|
- 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.
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
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
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
Video
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
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