SQL Text Import: Difference between revisions

From eStream Software
 
Line 695: Line 695:
|-
|-
| BATCH||String||align="center" |30||align="center" |N||||Batch No Code Follow Maintain Batch Code
| BATCH||String||align="center" |30||align="center" |N||||Batch No Code Follow Maintain Batch Code
|- style="background: #ffffcc"
|-  
| CODE||String||align="center" |10||align="center" |Y||||Customer/Supplier Code
| CODE||String||align="center" |10||align="center" |Y||||Customer/Supplier Code
|-
|-
Line 703: Line 703:
* 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: #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||||
|}
|}


Latest revision as of 04:51, 5 October 2024

Introduction

Is External Shareware Program which able to import master file & transactions to SQLAccounting in

This Program also able to Export XML Format from Different SQL Accounting Database.

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
  8. No Transfer Status(eg DO to IV Status)
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;REGISTERNO2;
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
REGISTERNO2 String 30 N
  • Customer/Supplier Company New Registration No.
  • Only Available in Version 875.782 & above
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 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
  • 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
EIVDATETIME DateTime 0 N
  • E-Invocing Submited Date time in the UTC timezone
  • eg 19/07/2024 7:17:43 AM
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
  • 0 - Empty
  • 1 - New Reg No
  • 2 - NRIC
  • 3 - Passport
  • 4 - ARMY
IDNO String 20 Y
TOURISMNO String 17 Y
SIC String 10 Y
INCOTERMS String 3 Y
SUBMISSIONTYPE Integer 0 Y 17
  • 0 = None
  • 17 = E-Invoice / Self Billing(for Purchase)
  • 18 = Consolidate
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
  • GL Account to be Debit/Credit
  • Mandatory if item code field empty
TRANSFERABLE String 1 Y T Fixed/Hardcode
REMARK1 String 200 N
REMARK2 String 200 N
TAXINCLUSIVE Integer 0 Y 0
  • 1 - True
  • 0 - False
BATCH String 30 N Batch No Code Follow Maintain Batch Code
CODE String 10 Y Customer/Supplier Code
TAXRATE String 20 N Tax Rate
TARIFF String 20 C
  • Tariff or HS Code
  • Mandatory - If is SST Registered Person and Post without Item Code
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;
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;
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 Document Number
DOCNOEX String 20 N Additional Document Number
DOCDATE Date 0 Y In DD/MM/YYYY format
POSTDATE Date 0 Y Same As DocDate In DD/MM/YYYY format
CODE String 10 Y Customer/Supplier Code
COMPANYNAME String 100 Y Customer/Supplier Name
ADDRESS1 String 40 Y Customer/Supplier Address 1
ADDRESS2 String 40 Y Customer/Supplier Address 2
ADDRESS3 String 40 Y Customer/Supplier Address 3
ADDRESS4 String 40 Y Customer/Supplier Address 4
PHONE1 String 30 Y Customer/Supplier Name Phone 1
FAX1 String 30 Y Customer/Supplier Fax 1
ATTENTION String 70 Y Customer/Supplier Content Person Name
AREA String 10 Y ----
AGENT String 10 Y ---- Salesman Code
PROJECT String 20 Y ----
TERMS String 10 Y Credit Terms (eg 30 Days, C.O.D., etc)
CURRENCYRATE FMTBcd 6 Y 1
DESCRIPTION String 150 Y See Note Tab Description to be shown at Customer/Supplier Statement
CANCELLED String 1 Y F
  • T - True
  • F - False
DOCAMT FMTBcd 2 Y 0 Document Amount
VALIDITY String 150 N Additional Document Number
DELIVERYTERM String 150 N Additional Document Number
CC String 150 N Additional Document Number
DOCREF1 String 25 N Additional Document Number
DOCREF2 String 25 N Additional Document Number
DOCREF3 String 25 N Additional Document Number
DOCREF4 String 25 N Additional Document Number
BRANCHNAME String 100 Y BILLING Fixed/Hardcode
DADDRESS1 String 40 Y Customer/Supplier Delivery Address 1
DADDRESS2 String 40 Y Customer/Supplier Delivery Address 2
DADDRESS3 String 40 Y Customer/Supplier Delivery Address 3
DADDRESS4 String 40 Y Customer/Supplier Delivery Address 4
DATTENTION String 70 Y Customer/Supplier Delivery Contact Person
DPHONE1 String 30 Y Customer/Supplier Delivery Phone 1
DFAX1 String 30 Y Customer/Supplier Delivery Fax 1
TRANSFERABLE String 1 Y T Fixed/Hardcode
D_AMOUNT FMTBcd 2 Y 0 Fixed/Hardcode
P_PAYMENTMETHOD String 10 N GL Bank/Cash Account Code
P_CHEQUENUMBER String 20 N
P_BANKCHARGE FMTBcd 2 Y 0
P_AMOUNT FMTBcd 2 Y 0
P_PAYMENTPROJECT String 20 Y ----
TAXDATE Date 0 N
  • In DD/MM/YYYY format
  • For Purchase Module Only
  • The Date must be Latest or Equal to DocDate
DETAIL Y DETAIL Fixed/Hardcode
DOCNO String 20 Y Document Number
NUMBER String 5 N
ITEMCODE String 30 N Product Code From Maintain Item
LOCATION String 20 Y ---- Product Location to be IN/OUT, Code Follow Maintain Location Code
PROJECT String 20 Y ----
DESCRIPTION String 200 Y Product Description
DESCRIPTION2 String 200 N
DESCRIPTION3 Blob 8 N
QTY FMTBcd 4 Y 1
UOM String 10 C Unit of Measurement

C = Y If using ITEMCODE

SUOMQTY FMTBcd 4 Y 0 Fixed/Hardcode
UNITPRICE FMTBcd 8 Y
DELIVERYDATE Date 0 Y Same As DocDate In DD/MM/YYYY format
DISC String 20 N
TAX String 10 N
TAXAMT FMTBcd 2 Y 0
AMOUNT FMTBcd 2 Y Qty * Unit Price (Excluding GST)
PRINTABLE String 1 Y T Fixed/Hardcode
ACCOUNT String 10 C
  • GL Account to be Debit/Credit
  • Mandatory if item code field empty
TRANSFERABLE String 1 Y T Fixed/Hardcode
REMARK1 String 200 N
REMARK2 String 200 N
TAXINCLUSIVE Integer 0 Y 0
  • 1 - True
  • 0 - False
BATCH String 30 N Batch No Code Follow Maintain Batch Code
CODE String 10 Y Customer/Supplier Code
TAXRATE String 20 N Tax Rate
TARIFF String 20 C
  • Tariff or HS Code
  • Mandatory - If is SST Registered Person and Post without Item Code
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

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

  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)

For Build 38 & above

Excel-Import-28.jpg
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
Excel-Import-36.jpg
The following Steps if Download From FTP is Enable/Tick/Selected
06. Enter the Host/IP Address for FTP
07. Enter the FTP Port No
08. Enter the FTP User Name & Password
09. Enter the FTP Folder to download From
10. Select the Action to Do After Download
11. Click Test Connection...
12. Click Next
Action Description
Delete the file(s) in FTP Folder Will delete the file(s) after download
Move to Archive/YYYYMMDD Folder
  • Will Auto Create YYYYMMDD & move to this folder after done download
  • Make sure in the FTP folder had the folder Archive (eg download/Archive)
Leave it As It(I will self Manually Delete or Move it) Do nothing after Download
Template.Tips-01.jpg The file will download to the Pending Folder which is set at the Last Steps
SQLTxtImport.Fig04.jpg
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

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

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

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

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

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_Text_Import&oldid=13078"
Last modified
This page was last edited on 5 October 2024, at 04:51.