SQL XML Import: Difference between revisions

From eStream Software
 
(102 intermediate revisions by 2 users not shown)
Line 5: Line 5:
# Can do action such as New & Edit.
# Can do action such as New & Edit.
# About 95% of modules is covered.
# About 95% of modules is covered.
# Support GST
# Free of Charge for End User.
 
===Cons===
===Cons===
# Payment & Credit Note can't do Edit action.
# Can’t do Delete action.
# Can’t do Delete action.
# Not easy to generate.
# Not easy to generate.
# Is Batch update.
# Is Batch update.
# No Transfer Status (eg DO to IV Status)
<div style="float: right;">  [[#top|[top]]]</div>


==Guideline & Example==
==Guideline & Example==
You can just go to the module you wanted to import
You can just go to the module you wanted to import<br />
 
Example for Sales Invoice
Example for Sales Invoice
# Login SQL Accounting
# Click Sales | Invoice...
# Click Sales | Invoice...
# Select any Invoice
# Select any Invoice
Line 22: Line 30:
# Save the filename as SL_IV.IV-00270.300-A0003.xml
# Save the filename as SL_IV.IV-00270.300-A0003.xml
   (Example IV No. IV-00270 & Customer code is 300-A0003)
   (Example IV No. IV-00270 & Customer code is 300-A0003)
[http://www.estream.com.my/document/Example-XML.zip SQLAcc-XMLImport-Example]
[https://download.sql.com.my/customer/Fairy/Example-XML.zip SQLAcc-XMLImport-Example]
 
[https://download.sql.com.my/customer/Fairy/Example-XMLTag-ROWDATA.zip Example Full XML Tag for ROWDATA part (after the </METADATA> tag)]
:::----------------------------------------------------------------------------------------------------------------------------------------------------
::::{|
|-
| [[File:Template.Tips-01.jpg|60px]]|| '''Notes'''
1. FileName :=
    - BizObject.DocNo.CompanyCode.xml for AR, AP, SL & PH
    - BizObject.DocNo.xml for GL & ST
    - BizObject.Code.xml for Tools
2. 1 Record 1 xml file.
3. Please zip all the xml in 1 zip file, the zip filename can be anything &
    no folder in zip file.
|}
:::----------------------------------------------------------------------------------------------------------------------------------------------------
 
===Special Character===
Below data character need to be careful
::{| class="wikitable"
|-
! Data !! In XML
|-
|  & || &amp;amp;
|-
| < ||  &amp;lt;
|-
| > || &amp;gt;
|-
| " || &amp;quot;
|}
 
<div style="float: right;">  [[#top|[top]]]</div>
 
==SQL Import Program Download==
* Version 5 (For SQL Accounting Version 736 & above)
* '''[https://www.sql.com.my/wiki/SQL_Text_Import#Import_Program SQL Import V5]'''
 
 
* Version (4.1.0.23) - 25 Aug 2016 '''(Deprecated)'''
* [https://www.sql.com.my/downloadfile/Fairy/SQLAccImportV4-setup.exe SQL Import V4]
* MD5 : C237B8E11592F767867FA9B5874D5967
 
===History New/Updates/Changes===
--Version 5--
May Refer to [[SQL_Text_Import#History_New.2FUpdates.2FChanges|Version 5 History & Changes]]
 
 
--Build 23--
* Fix Unable Import JE Foreign DR & CR.
* Add Link to SQL Acc. File | Run.
 
--Build 22--
* Fix Import Taxable Amt not match xml
 
--Build 21--
* Fix Import GL Cash Book only import FromDocType is Empty.
* Fix InitialPurchaseCost at Sales Invoice not imported.
 
--Build 20--
* Fix Import Journal Tax not imported.
* Fix Missing Seq for GL OR, GL PV & GL JE.
 
--Build 19--
* Fix Import Key Violation Maintain Item.
 
<div style="float: right;">  [[#top|[top]]]</div>
 
==SQL Export Program Download (Deprecated)==
For SQL Accounting User ONLY who wanted to export data from A SQL Accounting Database to B SQL Accounting Database
<br />
 
* '''[https://download.sql.com.my/customer/Fairy/SQLAccExportV5-setup.exe SQL Export V5]'''
<br />
 
* Version 5.3.0.11 - 15 Apr 2018
:(For Export XML File From SQL Accounting)
* MD5 : 8E80512239F7A30ABA6F6DD8E2032C8A
 
===History New/Updates/Changes===
--Build 11--
* Upgrade to Version 5.3.
* Enable support Version 776 & above.
* Change New High Resolution Icon.
 
--Build 10--
* Remove BOM Listing export from Menu.
* Fixed Export Same DocNo but Diff Supplier incorrectly export
 
--Build 9--
* No Data Export out when filtering.
 
--Build 8--
* Upgrade to Version 5.
* Internal Release.
 
--Build 7--
* Fixed Fast Export Error for GL_BA
 
--Build 6--
* Remove Tax Export for Fast Export.
* Fixed Fast Export Error for GL_PV & GL_OR
 
--Build 5--
* Add Fast Export to Main Menu.
 
<div style="float: right;">  [[#top|[top]]]</div>
 
==Useful tools==
* http://www.corefiling.com/opensource/xmldiff.html  (Just tick Schema mode)


==Installation==
==Installation==
# Install sqlaccounting
# Install sqlaccounting
# Create Sample Data
# Create Sample Data / Login to the Database wanted to import
# Login (username & password is Admin)
# Login (username & password is Admin)
# Install the Import Program
# Install the [http://www.estream.com.my/downloadfile/Fairy/SQLAccImportV4-setup.exe Import Program] (Username & Password is sqlutility)
# Run the Import Program
# Run the Import Program


==Steps==
==Steps==
::[[File:SQLXMLImport.Fig01.jpg|650px]]
Click ''Tools | Fast XML Import...'' - For Version 5 & above only
[[File:SQLXMLImport.Fig01.jpg|650px|center]]
<br>
<br>
# Click '''Folder Icon''' button to select the zip file which content of XML file.
:01. Click '''Folder Icon''' button to select the zip file which content of XML file.
# Tick the Module to import.
:02. Tick the Module to import.
# Click '''Verify''' button to check any conflict.
:03. Click '''Verify''' button to check any conflict.
# Any Error or Conflict will be seen here
:04. Any Error or Conflict will be seen here
# In this '''Action''' user can change the option either is '''Import''' or '''Replace'''
:05. In this '''Action''' user can change the option either is '''Import''' or '''Replace'''
# Tick this Option if wanted to set status on each document (only available if there is UDF Module with UDF_Status)
:06. Click Import Button
# Click Import to Post to Account (Only import it the Check box is Tick)


::{| class="wikitable"
{| class="wikitable" style="margin: 1em auto 1em auto;"
|-
|-
! Description !! Properties
! Description !! Properties
Line 52: Line 169:
| Action Button || This button Allow user to batch Change the Action Column
| Action Button || This button Allow user to batch Change the Action Column
|}
|}
==Video==
* [https://youtu.be/V7eLIA7L0qY Import-SQL XML Import & Export]
==FAQ==
===Why the SO or PH Deposit is not Imported even the XML had the information?===
: It because the D_DocNo field can't set. User had to export the OR/PV from Customer Payment/Supplier Payment
===Why AR_PM, AR_CN, AP_SP & AP_SC can't don't had the Replace Action?===
: It due to
:* Gain Loss
:* Refund knock off can't be replace
:* GST-03 Item 12 Net Gain in Forex
===Why after upgrade SQL Acc Version 742 & above when import GL_PV, GL_OR & GL_JE prompt Access Violation===
: It due to from Version 742 & above the Detail field '''TAXREF''' is change to '''GSTNO''';
===I had 2 or more database/Company to import but how can I avoid import wrong zip file?===
:- Using Version 5.6.0.21 you can add in the zip file with '''Profile.txt''' with content "CompanyName";"Remark";
:  eg ''"Testing Company";"2017";'' in the '''Profile.txt'''
:- This function unavailable for ''Fast XML Import'' function.
:- Remember to set to '''1''' in ''Tools | Options | CheckProfile''
:- This function is only Alert user if not match & user still can by ignore the Alert & continue import
===In my zip file consist of xml file which filename had follow the requirement but why still empty when Get file?===
: Make sure the xml file Attributes for the following setting is selected (Right Click | Properties | General | Advanced...)
::* File is ready for archiving
::* Allow this file to have contents indexed in addition to file properties
===Do the XML import Description3 support multi line?===
: Yes but you need to adjust the Field SUBTYPE from Binary to Text
Original
<pre>
...
<FIELD attrname="DESCRIPTION3" fieldtype="bin.hex" SUBTYPE="Binary" WIDTH="8"/>
...
</pre>
After
<pre>
...
<FIELD attrname="DESCRIPTION3" fieldtype="bin.hex" SUBTYPE="Text"/>
...
</pre>
: And Data input as below
<pre>
...
      <sdsDocDetail>
        <ROWsdsDocDetail DTLKEY="-1" DOCKEY="-1" SEQ="1" ITEMCODE="1000679600" LOCATION="----" BATCH="" PROJECT="----" DESCRIPTION="Batch for Diamond" DESCRIPTION3="Line 1
#Line 2
Line 3" QTY="3.0000" UOM="CON" RATE="1.0000" SQTY="3.0000" SUOMQTY="0.0000" UNITPRICE="7.90000000" DELIVERYDATE="20190425" DISC="" TAX="" TARIFF="" TAXAMT="0.00" LOCALTAXAMT="0.00" TAXINCLUSIVE="0" AMOUNT="1747.52" LOCALAMOUNT="1747.52" TAXABLEAMT="1747.52" ACCOUNT="500-000" PRINTABLE="T" TRANSFERABLE="T" INITIALPURCHASECOST="0.00" CHANGED="F">
...
</pre>
:Result
<pre>
Line 1
#Line 2
Line 3
</pre>
<div style="float: right;">  [[#top|[top]]]</div>
==See also==
* [[SQL_Accounting_Linking#FAQ|FAQ]]
* [[SQL Accounting Linking]]
* [[SDK Live]]
* [[SQL Text Import]]
* [[SQL Acc XLS n MDB Import]]
* Others [[Customisation]]

Latest revision as of 01:32, 3 May 2024

Introduction

Is External Program which able to import master file & transactions to SQLAccounting in XML format.

Pros

  1. Can do action such as New & Edit.
  2. About 95% of modules is covered.
  3. Support GST
  4. Free of Charge for End User.

Cons

  1. Payment & Credit Note can't do Edit action.
  2. Can’t do Delete action.
  3. Not easy to generate.
  4. Is Batch update.
  5. No Transfer Status (eg DO to IV Status)

Guideline & Example

You can just go to the module you wanted to import

Example for Sales Invoice

  1. Login SQL Accounting
  2. Click Sales | Invoice...
  3. Select any Invoice
  4. Click Detail
  5. Right Click below empty Browse button
  6. Select Copy Invoice
  7. Open Notepad & paste it
  8. Save the filename as SL_IV.IV-00270.300-A0003.xml
  (Example IV No. IV-00270 & Customer code is 300-A0003)

SQLAcc-XMLImport-Example

Example Full XML Tag for ROWDATA part (after the </METADATA> tag)

----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Notes 
1. FileName :=
   - BizObject.DocNo.CompanyCode.xml for AR, AP, SL & PH
   - BizObject.DocNo.xml for GL & ST
   - BizObject.Code.xml for Tools
2. 1 Record 1 xml file.
3. Please zip all the xml in 1 zip file, the zip filename can be anything &
   no folder in zip file.
----------------------------------------------------------------------------------------------------------------------------------------------------

Special Character

Below data character need to be careful

Data In XML
& &amp;
< &lt;
> &gt;
" &quot;

SQL Import Program Download


  • Version (4.1.0.23) - 25 Aug 2016 (Deprecated)
  • SQL Import V4
  • MD5 : C237B8E11592F767867FA9B5874D5967

History New/Updates/Changes

--Version 5-- May Refer to Version 5 History & Changes


--Build 23--

  • Fix Unable Import JE Foreign DR & CR.
  • Add Link to SQL Acc. File | Run.

--Build 22--

  • Fix Import Taxable Amt not match xml

--Build 21--

  • Fix Import GL Cash Book only import FromDocType is Empty.
  • Fix InitialPurchaseCost at Sales Invoice not imported.

--Build 20--

  • Fix Import Journal Tax not imported.
  • Fix Missing Seq for GL OR, GL PV & GL JE.

--Build 19--

  • Fix Import Key Violation Maintain Item.

SQL Export Program Download (Deprecated)

For SQL Accounting User ONLY who wanted to export data from A SQL Accounting Database to B SQL Accounting Database


  • Version 5.3.0.11 - 15 Apr 2018
(For Export XML File From SQL Accounting)
  • MD5 : 8E80512239F7A30ABA6F6DD8E2032C8A

History New/Updates/Changes

--Build 11--

  • Upgrade to Version 5.3.
  • Enable support Version 776 & above.
  • Change New High Resolution Icon.

--Build 10--

  • Remove BOM Listing export from Menu.
  • Fixed Export Same DocNo but Diff Supplier incorrectly export

--Build 9--

  • No Data Export out when filtering.

--Build 8--

  • Upgrade to Version 5.
  • Internal Release.

--Build 7--

  • Fixed Fast Export Error for GL_BA

--Build 6--

  • Remove Tax Export for Fast Export.
  • Fixed Fast Export Error for GL_PV & GL_OR

--Build 5--

  • Add Fast Export to Main Menu.

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

Click Tools | Fast XML Import... - For Version 5 & above only

SQLXMLImport.Fig01.jpg


01. Click Folder Icon button to select the zip file which content of XML file.
02. Tick the Module to import.
03. Click Verify button to check any conflict.
04. Any Error or Conflict will be seen here
05. In this Action user can change the option either is Import or Replace
06. Click Import Button
Description Properties
Import Action It will Insert the Record To SQL Accounting
Replace Action It will Call Edit & Update the Record
Action Button This button Allow user to batch Change the Action Column

Video

FAQ

Why the SO or PH Deposit is not Imported even the XML had the information?

It because the D_DocNo field can't set. User had to export the OR/PV from Customer Payment/Supplier Payment

Why AR_PM, AR_CN, AP_SP & AP_SC can't don't had the Replace Action?

It due to
  • Gain Loss
  • Refund knock off can't be replace
  • GST-03 Item 12 Net Gain in Forex

Why after upgrade SQL Acc Version 742 & above when import GL_PV, GL_OR & GL_JE prompt Access Violation

It due to from Version 742 & above the Detail field TAXREF is change to GSTNO;

I had 2 or more database/Company to import but how can I avoid import wrong zip file?

- Using Version 5.6.0.21 you can add in the zip file with Profile.txt with content "CompanyName";"Remark";
eg "Testing Company";"2017"; in the Profile.txt
- This function unavailable for Fast XML Import function.
- Remember to set to 1 in Tools | Options | CheckProfile
- This function is only Alert user if not match & user still can by ignore the Alert & continue import

In my zip file consist of xml file which filename had follow the requirement but why still empty when Get file?

Make sure the xml file Attributes for the following setting is selected (Right Click | Properties | General | Advanced...)
  • File is ready for archiving
  • Allow this file to have contents indexed in addition to file properties

Do the XML import Description3 support multi line?

Yes but you need to adjust the Field SUBTYPE from Binary to Text

Original 

...
<FIELD attrname="DESCRIPTION3" fieldtype="bin.hex" SUBTYPE="Binary" WIDTH="8"/>
...

After 

...
<FIELD attrname="DESCRIPTION3" fieldtype="bin.hex" SUBTYPE="Text"/>
...
And Data input as below
...
      <sdsDocDetail>
        <ROWsdsDocDetail DTLKEY="-1" DOCKEY="-1" SEQ="1" ITEMCODE="1000679600" LOCATION="----" BATCH="" PROJECT="----" DESCRIPTION="Batch for Diamond" DESCRIPTION3="Line 1
#Line 2
Line 3" QTY="3.0000" UOM="CON" RATE="1.0000" SQTY="3.0000" SUOMQTY="0.0000" UNITPRICE="7.90000000" DELIVERYDATE="20190425" DISC="" TAX="" TARIFF="" TAXAMT="0.00" LOCALTAXAMT="0.00" TAXINCLUSIVE="0" AMOUNT="1747.52" LOCALAMOUNT="1747.52" TAXABLEAMT="1747.52" ACCOUNT="500-000" PRINTABLE="T" TRANSFERABLE="T" INITIALPURCHASECOST="0.00" CHANGED="F">
...
Result
Line 1
#Line 2
Line 3

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_XML_Import&oldid=12840"
Last modified
This page was last edited on 3 May 2024, at 01:32.