Introduction
Is External Shareware Program which able to import master file & transactions to SQLAccounting in Text or CSV format.
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 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; |
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 |
|
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""" |
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.8.3.51) - 30 Sep 2020
- Evaluation Limit : Unlimited
- SQLAccTxtImpV5
- MD5 : 740F5C989C9F2BE177ADFCADD00F0439
- Version (4.4.0.15) - 11 May 2016
- SQLAccTxtImp
- MD5 : EEF179664E1DB5B171590AC07A86AC39
History New/Updates/Changes
--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