SQL Text Import: Difference between revisions

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



Revision as of 02:27, 9 January 2016

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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Notes
1. FileName can be any name.
2. 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

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

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

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

SQLTxtImport.Fig01.jpg


  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)


SQLTxtImport.Fig02.jpg

This is example error on the drop down status

Video

See also