SQL Text Import

From eStream Software

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
  4. FREE import for Master file.

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. There is a cost for End User and Dealer. Only Free if each file record less then 100.
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

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;
DETAIL;DOCNO;ACCOUNT;DESCRIPTION;PROJECT;TAX;TAXAMT;TAXINCLUSIVE;AMOUNT;CODE;

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

Customer Payment

Below is also applicable for

  • Customer Payment - AR_PM -> GST-AR_PM.txt
  • Supplier Payment - AP_SP -> GST-AP_SP.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 ----
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
  • 1 - True
  • 0 - False
DETAIL Y 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

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

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 ----
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 ----
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 Y GL Account to be Debit/Credit
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

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 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 Refund - TXT
  • Add Customer & Supplier Contra - TXT
  • Add TaxDate field for Supplier,Purchase & Customer/Sales Credit Note - TXT
  • Add GST Gift - TXT & XML

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

Steps

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=7071"
Last modified
This page was last edited on 8 May 2017, at 02:20.