SQL Text Import: Difference between revisions

From eStream Software
Line 752: Line 752:


===History New/Updates/Changes===
===History New/Updates/Changes===
--Build 36-- Coming Soon...
* Fixed XML Tariff & Bank not import.
* Fixed Action Column too small.
--Build 35--
--Build 35--
* Fixed XML Tariff not import if had Tax Code.
* Fixed XML Tariff not import if had Tax Code.

Revision as of 00:29, 16 November 2018

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
Template.Tips-01.jpg Notes 
1. FileName can be any name.
2. File Extension can be .txt or .csv
3. 1 DocType/BizObject 1 txt file.
Template.Tips-01.jpg 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

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

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

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

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 N
  • Tariff or HS Code
  • Mandatory - If is SST Registered Person and Post without Item Code

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

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



History New/Updates/Changes

--Build 36-- Coming Soon...

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

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

  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

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

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)
AutoImport-01.jpg
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
AutoImport-02.jpg
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
Scheduler.Task1.jpg
02. Click Action | Create Basic Task...
AutoImport-03.jpg
03. Enter Task Name (eg SQL Acc Auto Import) & Click Next
Scheduler.Task3.jpg
04. Select Daily & Click Next
Scheduler.Task4.jpg

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

Scheduler.Task5.jpg

07. Select Start a program & Click Next

AutoImport-04.jpg

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

AutoImport-05.jpg

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

AutoImport-06.jpg

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

AutoImport-07.jpg

14. Click Edit... button

Scheduler.Task12.jpg

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

Scheduler.Task10.jpg

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

Steps To Import

Below is Example to Import Sales Invoice From Text File

SQLTxtImport.Fig01.jpg


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
SQLTxtImport.Fig02.jpg
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)


SQLTxtImport.Fig03.jpg

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

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_Text_Import&oldid=9097"
Last modified
This page was last edited on 16 November 2018, at 00:29.