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 26 Oct 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.8) - 17 Nov 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 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