SQL Text Import

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 can do New/Insert action.
2. Maintain Stock Item not covered.
3. Is Batch update.
4. Not support Unicode.
5. Same DocNo for Different Supplier not cover
6. There is a cost for End User and Dealer. Only Free if each file record less then 100.

----------------------------------------------------------------------------------------------------------------------------------------------------

	Notes 1. FileName can be any name. 2. 1 DocType/BizObject 1 txt file.
	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

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

Table Fields Properties
Field Name	Field Type	Field Size	Default Value	Remarks
Master				Fixed/Hardcode
DOCNO	String	20		Document Number
DOCNOEX	String	20		Additional Document Number
CODE	String	10		Customer Code
DOCDATE	Date	0		In YYYYMMDD format (for XML Only)
POSTDATE	Date	0	Same As DocDate	In YYYYMMDD format (for XML Only)
AREA	String	10	----	Follow Maintain Customer Information
AGENT	String	10	----	Follow Maintain Customer Information
PROJECT	String	20	----
TERMS	String	10		Follow Maintain Customer Information
CURRENCYRATE	FMTBcd	6	1
DESCRIPTION	String	150	Sales	Description to be shown at Customer/Supplier Statement
CANCELLED	String	1	F	T - True F - False"
DOCAMT	FMTBcd	2		Document Amount
DETAIL				Fixed/Hardcode
DOCNO	String	20		Invoice Number
PROJECT	String	20	----
ACCOUNT	String	10		GL Account to be Debit/Credit
DESCRIPTION	String	80		Product Description
TAX	String	10
TAXAMT	FMTBcd	2	0
AMOUNT	FMTBcd	2		Qty * Unit Price
TAXINCLUSIVE	Integer	0	0	1 - True 0 - False"

Customer Payment

Below is also applicable for

• Customer Payment - AR_PM
• Supplier Payment - AP_SP

Table Fields Properties
Field Name	Field Type	Field Size	Default Value	Remarks
MASTER				Fixed/Hardcode
DOCNO	String	20		OR Document Number
COMPANYCODE	String	10		Customer Code
DOCDATE	Date	0		In DD/MM/YYYY format
POSTDATE	Date	0	Same As DocDate	In DD/MM/YYYY format
DESCRIPTION	String	150	Payment For Account	Description to be shown at Customer/Supplier Statement
AREA	String	10	----	Follow Maintain Customer Information
AGENT	String	10	----	Follow Maintain Customer Information
PAYMENTMETHOD	String	10		GL Bank/Cash Account Code
CHEQUENUMBER	String	20
PROJECT	String	20	----
PAYMENTPROJECT	String	20	----
CURRENCYRATE	FMTBcd	6	1
BANKCHARGE	FMTBcd	2	0
DOCAMT	FMTBcd	2		Document Amount
LOCALDOCAMT	FMTBcd	2		DocAmt * CurrencyRate
UNAPPLIEDAMT	FMTBcd	2	0	Payment Amt - OffSet Invoice Amt
CANCELLED	String	1	F	T - True F - False"
NONREFUNDABLE	Integer	0	0	1 - True 0 - False"
DETAIL				Fixed/Hardcode
DOCNO	String	20		OR Document Number
DOCTYPE	String	2		IV - Invoice DN - Debit Note"
KODOCNO	String	20		Knock Off/Off set Invoice/Debit Note Number
KOAMT	FMTBcd	2	0	Knock Off/Off set Invoice/Debit Note Amount

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"

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 (4.3.0.9) - 10 Sep 2015
• http://www.estream.com.my/downloadfile/Fairy/SQLAccTxtImp-setup.exe

History New/Updates/Changes

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

Useful tools

• http://www.diffnow.com/

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

SQLAcc-TextImport-Video

1. Set the Range to Import (Useful if the list too long & only available for Registred version)
2. Click Get File button to select the Txt or csv file
3. Click Verify To Check any duplicate or Valid Maintenance Code (eg Area, Agent, etc)
4. Click the Dropdown List (see below pic) to see all the error
5. 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

• Text Import

See also

• SQL Accounting Linking
• SDK Live
• SQL XML Import
• SQL Acc XLS n MDB Import
• Others Customisation