SQL Text Import

From eStream Software
Revision as of 06:42, 11 January 2016 by Twfaung (talk | contribs) (→‎Steps)

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

MASTER;DOCNO;DOCNOEX;DOCDATE;POSTDATE;CODE;TERMS;DESCRIPTION;AREA;AGENT;PROJECT;CURRENCYRATE;DOCAMT;CANCELLED;
DETAIL;DOCNO;ACCOUNT;DESCRIPTION;PROJECT;TAX;TAXAMT;TAXINCLUSIVE;AMOUNT;

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)

Customer Payment

Below is also applicable for

  • Customer Payment - AR_PM
  • Supplier Payment - AP_SP
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
  • Purchase Request - PH_PQ
  • Purchase Order - PH_PO
  • Goods Received Note - PH_GR
  • Purchase Invoice - PH_PI
  • Purchase Debit Note - PH_SD
  • Purchase Return - PH_SC
  • Purchase Cash Purchase - PH_CP
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;

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

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

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

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_Text_Import&oldid=3438"
Last modified
This page was last edited on 11 January 2016, at 06:42.