Introduction
Is an External Shareware Program which able to import below data to SQL Accounting using Excel or Access File.
- Master file (eg. Debtors, Creditors & Item List)
- Transactions
Pros
- Easy to generate
- About 80% of modules is covered.
- Support GST
- FREE import for Master file.
Cons
- Only can do New/Insert action
- Is Batch update.
- There is a cost for End User and Dealer for Transaction import. Only Free if each file record less then 100.
- Same DocNo for Different Supplier not cover
- Payment & Credit Note Knock Off for both Customer & Supplier not cover
- No transfer status for Sales & Purchase Module
Pros (UE Only)
- Easy to generate
- Able update Stock Information.
- Can do New/Insert/Edit/Update action
Cons (UE Only)
- There is a cost for End User and Dealer. Only Free if each file record less then 50.
- Only Cover Maintain Stock Item.
Todo
- Import GL Journal Entry
- Import Maintain Item Template
- Add Verify Button for Master Data Import
Tools Required
- Microsoft Excel or Access
- AccessDatabaseEngine.exe (optional)
Example Template
- Updated 08 Dec 2015
- www.estream.com.my/document/SQLAcc-ImportExcel.xls
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Tips for Programmer to Export Transaction 01. Use INNER JOIN SQL to join the Master & Detail table 02. Export to Excel or Access file
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Things to Consider
- Date Format should be in dd/mm/yyyy (entry time in Excel as dd/mm/yyyy) - Updated 14 Nov 2013
- Float, Currency & Integer type Must not had , (comma) or Currency Symbol
- If Prompt Error external table is not in the expected format - Try save the file to other name using File | Save As in Excel
- Below is the column type can/can't import
Import Program
- Version (4.2.1.9) - 05 Dec 2015
- Version (4.1.1.7) - 14 Sep 2015 (UE Only)]
- http://www.estream.com.my/downloadfile/Fairy/SQLAccXLSnMDBImp-setup.exe
History New/Updates/Changes
--Build 9--
- Fixed GL PV & OR some fields no Default Value.
- Fixed GL_PV & OR Verify Error.
- Force Must Mapping DocDate field.
--Build 8--
- Fixed unable to Import AR/AP Opening Balance.
- Fixed unable to unmap Fields.
- Remove Auto Remove Source Field after Mapped.
--Build 7--
- Fixed unable to register Database.
- Fixed Customer/Supplier record splitter missing.
--Build 6--
- Upgrade to Version 4.2.
- Upgrade to XE10.
- Remove Maintain Tax Import
- Enable Import AR, AP, SL & PH Transactions
- Enable Import GL_OR & GL_PV Transactions
--Build 5--
- Upgrade to Version 4.
- Upgrade to XE7.
- Add Split Record - To cater large Record Data File.
- Remove readonly="true" SUBTYPE="Autoinc" before append Data.
- Add Default Value for Stock Item CostingMethod field.
History New/Updates/Changes (UE Only)
--Build 7--
- Fixed “Bad variable type” error.
- Fixed some field no default value.
--Build 6--
- Upgrade to Version 4.
- Upgrade to XE7.
- Add Split Record - To cater large Record Data File.
- Remove readonly="true" SUBTYPE="Autoinc" before append Data.
- Add Default Value for Stock Item CostingMethod field.
- Add New filter function for data.
Steps
Master Data
Below is example for import Stock Item...
- 01. Click 1. Get File button
- 02. Select the File type (eg Microsoft Excel)
- 03. Select the File file wanted to import
- 04. Click Open
- 05. Click the combo box (Circle in Red) to select the Worksheet/Table in the file
- 06. Click 2. Get Data button to load the file.
- 07. Drag the available column at the SourceField & drop to the TargetField.
- 08. For Detail Part (Circle in Green) (Eg Click on the UOM tab).
- 09. Click New button.
- 10. Drag the available column at the SourceField & drop to the TargetField at detail part.
- 11. Click Save after Done (only for Detail Part).
- 12. Click Post To A/c button if all done mapping.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Blue Circle - Is use to let system know Start from which row till End which row to import.
Orange Circle - The Worksheet/Table records.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Transactions Data
Below is example for import Customer Invoice...
- 01. Click 1. Get File button
- 02. Select the File type (eg Microsoft Excel)
- 03. Select the File file wanted to import
- 04. Click Open
- 05. Click the combo box (Icon 5) to select the Worksheet/Table in the file
- 06. Click 2. Get Data button to load the file.
- 07. Tick the Is Opening Transactions to import opening balance (Available for AR & AP Module Only).
- 08. Drag the available column at the SourceField & drop to the TargetField.
- 09. Click 3. Verify button.
- 10. Click 4. Post To A/c button if confirm all data is Ok.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- Do you realise there is an arrow near the Verify button?
- In the arrow there is an Option A.Load Setting..., this to load previously mapped fields
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Video
FAQ
May refer to FAQ