Introduction
Is an External Shareware Program which able to import below data to SQL Accounting using Excel or Access File.
|
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 100.
- Only Cover Maintain Stock Item.
Todo
- UE for All Master file import
Tools Required
- Microsoft Excel or Access
Microsoft Access Database Engine 2007 Redistributable (AccessDatabaseEngine.exe)- Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe - 32 bits)
Example Template
- Updated 14 Feb 2018
- Example Template-SQLAcc-ImportExcel.xls
History New/Updates/Changes
--Update 21 Mar 2018--
- Incorrect date format in Sample Data
- ARAP_Payment Data incorrect Value
- Add Customer/Supplier Import Format
--Update 05 Feb 2018--
- Add Stock Transfer Import Format
- Add Stock Receive Import Format
--Update 05 Feb 2018--
- Add Giro Import Format
- Add BOM Import Format
- Update ARAP Payment with Knock off
- Update Sales & Purchase - Add Tax Inclusive
--Updated 26 Sep 2016--
- Add Example Data for Maintain Customer/Supplier
- Add Example Data for Maintain Item
- Update Maintain Account with Notes
- Add Example Data for New Journal Vourcher
--Updated 07 Sep 2016--
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Tips for Yeos Transaction Import User might had to adjust some data in the excel file 01. Remove the 1st row(i.e 1st row must be a title of the column) 02. PRD Qty & Gross AMT column need * -1 (only for Credit Note) or can use My Excel for Credit Note/Payment is in Negative Value, can it be import?
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Things to Consider
- Float, Currency & Integer type Must not had , (comma) or Currency Symbol & Default Value is 0 not empty
- No Formula in the cell
- Date Format should be in dd/mm/yyyy (entry time in Excel as dd/mm/yyyy) - Updated 14 Nov 2013
- Below is the column type can/can't import
- Try just use Alphanumeric for the Worksheet Name
Import Program
- Version (4.6.3.21) - 03 Mar 2018
- Version (4.2.1.10) - 30 Jun 2017 (UE Only)
- Evaluation Limit : 100 Records per file (Except Master File with not limit)
- http://www.estream.com.my/downloadfile/Fairy/SQLAccXLSnMDBImp-setup.exe
- MD5 : BDDFA83E446E82136A83E9A021382F23
History New/Updates/Changes
--Build 22-- Coming Soon...
- Upgrade to Version 4.6.4.
- Fix Get File 2 for Maintain Customer, Supplier & Stock Item unable to Click New button.
- Fix Supplier Payment Knock Off Detail not verify.
- Fix Customer & Supplier Payment No Default Description.
- Fix Verify Customer & Supplier Payment error if no Knock off information in excel.
- Fix Verify Customer & Supplier Payment PaymentMethod not check.
- Fix Error when Post To A/c for Customer & Supplier Payment error if no Knock off information in excel.
- Fix unable to load all data once verify button is click.
- Fix error Import BOM if Batch & BOMPackage is empty.
- Enable Customer/Supplier Refund Import.
--Build 21--
- Fix Default Value for NonRefundable should be 0 not F
- Fix Prompt Access Violation for Get File 2 for Maintain Customer & Supplier.
--Build 20--
- Fix Detail Project field missing.
- Add Auto Get Data if only 1 worksheet/table.
- Fix Access Violation for Get File 3 when no select any excel.
--Build 19--
- Upgrade to Version 4.6.
- Support Auto Get CompanyCode using Remark.
- Add Truncate String... Function.
- Fixed Verify no check Batch field.
- Improve Performance on Get File 2.
- Add Get File 3 function (Data Entry Only) to enable import 2 (Header & Detail) Excel.
--Build 18--
- Upgrade to Version 4.5.3.
- Fixed Scrollbar not shown.
- Enable Customer & Supplier Payment with Knock Off.
- Enable Import Stock Item BOM.
- Enable Import Supplier GIRO Infomation.
- Fixed Check Duplicate Records...unable to Get File 2.
- Enable Get File 2 function for Split Memo To Columns.
- Add Get File 2 function to enable Open Excel file for pc without excel file install - Final Part.
- Enable Get File 2 function for Convert Row To Columns.
- Fixed Unable to Import no GST transactions for Locked GST Period.
- Fixed Get File 2 unable to get Date value correctly.
- Fixed Get File 2 still append row even is empty if Actual row data < To Records.
- Fixed Get File 2 not correctly append if there empty cell in 1st column.
- Add Convert YYYYMMDD to DD/MM/YYYY function.
- Fixed Verify prompt Access Violation if Mapped ItemCode without Map UOM with Default Value <> empty.
--Build 17--
- Upgrade to Version 4.5.
- Remove support Provider Microsoft.Jet.OLEDB.4.0 as Microsoft Windows Security update KB4041681 is no more support.
- Add Get File 2 function to enable Open Excel file for pc without excel file install - Part 1.
- Fixed Remark1 & Remark2 empty if just mapping CompanyItemCode field.
--Build 16--
- Fixed AR & AP Payment not remove not relevent fields.
- Fixed AR & AP Payment not auto set Default Value.
- Support Auto Get CompanyCode using ROC or GST No.
- Support Auto Get Set Tax Code when mapping & Default Value fields is not set.
- Fixed Access Violation when import Maintain Batch without mapping ItemCode.
- Add Verify Option for AR & AP Payment.
- Add Verify Option for Master Data.
--Build 15--
- Upgrade to Version 4.4.
- Add Convert Rows to Columns... Option.
- Show Result next to selected Column for Split Memo to Columns... Option
- Fixed Verify Itemcode not checking UOM.
- Enable Import Stock Received, Stock Issue, Stock Adjustment & Stock Transfer.
- Enable Import Maintain Item Template.
- Add Verify Location.
--Build 14--
- Upgrade to Version 4.3.2.
- Add *-1 All Data function.
- Add TaxDate field for AP, PH, AR_CN & SL_CN.
- Add Check Duplicate Records... Option.
- Add Split Memo to Columns... Option.
--Build 13--
- Upgrade to Version 4.3.
- Upgrade to XE25.
- Fix some Default Value is not Assign if there is mapping field.
- Fixed Mixed Excel datatype.
- Remove Default Tax Code.
- Add Auto Remove Empty Space before append the data.
- Add Grid Export to Excel for DocNo List Grid.
- Set Excel As Default File type.
- Fixed TaxInclusive not working.
- Add AutoMapping Field for Same FieldName with Target FieldName.
--Build 12--
- Fixed Maintain Batch Prompt 100 records
- Fixed Maintain Batch Progress bar always 0%
- Fixed Maintain Batch Posting record still limit even untick option.
- Fixed some can't read xlsx.
- Fix some Default Value is not Assign if there is mapping field.
--Build 11--
- Fixed Missing DeliveryDate Field
- Fixed Import Amount Double if no Mapping for Amount
- Fixed Get Data 2nd time no records shown.
--Build 10--
- Fixed unable to Import GL OR
- Enable Auto Calc TaxAmt.
- Fix Maintain Acc can't import Tax, CashFlowType & MSIC.
- Enable Import GL_JE.
--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 10--
- Add AutoMapping Field for Same FieldName with Target FieldName
--Build 9--
- Upgrade to Version 4.2.
- Upgrade to XE25.
- Fix some Default Value is not Assign if there is mapping field.
- Fixed Mixed Excel datatype.
- Add Auto Remove Empty Space before append the data.
- Set Excel As Default File type.
- Add AutoMapping Field for Same FieldName with Target FieldName.
--Build 8--
- Remove Invalid components.
--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.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
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.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Transactions Data - Get File 3
- This Option only for Excel
- Only available in Version 4.6.3.19 & above
- 01. Click arrow at the 1. Get File button
- 02. Select Get File 3... & system will prompt 2 dialog like below
- 03. Select the Excel Header File.
- 04. Select the Excel Detail File.
- 05. Click the combo box to select the Worksheet/Table for each Header & Detail file
- 06. Click the combo box to select the Key Field to link both Header & Detail file
- 07. Click 4. Get Data button
- 08. Enter the Merge File name.
- 09. Click Save button.
Options
- Only available in Version 4.3.2.14 & above
Check Duplicate Records
Menu: Tools | Check Duplicate Records...
- This Function is to check the duplicate records for the selected file
- 01. Follow from Step 1 to 6 as above.
- 07. Tick the Column(s) to Group
- 08. Click 3. Verify... button
Split Memo to Columns
Menu: Tools | Split Memo to Columns...
- This option is to Split the memo (Data more then 1 row in the cell) into individual Columns.
- 01. Follow from Step 1 to 6 as above.
- 07. Set Number of Column(s) to split (Default is 4).
- 08. Select the Column to be Split
- 09. Click 5. Split button.
- 10. Scroll to the right. Highlight in Yellow is the newly Columns Created.
- 11. Click 6. Export Excel(*.xls) to export out the Result.
Convert Rows to Columns
Menu: Tools | Convert Rows to Columns...
- Only available in Version 4.3.2.15 & above
- 01. Follow from Step 1 to 6 as above.
- 07. Set Number of Column(s) to split (Default is 4).
- 08. Select the Column to be Convert
- 09. Click 5. Convert button.
- 10. Scroll to the right. Highlight in Yellow is the newly Columns Created.
- 11. Click 6. Export Excel(*.xls) to export out the Result.
|
Video
FAQ
Why the system take so long to Get Data from excel where in excel just few records?
- This happen when there is the problem in Excel Worksheet.
- Try below steps
- 1. Press CTRL + Home
- 2. Press CTRL + A (To Select All)
- 3. Press CTRL + C (To Copy Selected)
- 4. Press Shift + F11 (To inserts a New Worksheet into the active Workbook)
- 5. Press CTRL + V (To Paste)
- 6. Save the Excel & Try again on New Worksheet
Prompt Error external table is not in the expected format when try Get Data from Excel.
- Try save the file to other name using File | Save As in Excel
My excel date format is in YYYYMMDD format, how I convert it to dd/MM/yyyy?
- Can try use this formula =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
- * In Version 4.5.3.18 & above had function call Convert YYYYMMDD to DD/MM/YYYY... can help to convert it before import.
- Steps
- 01. Follow from Step 1 to 4 as above
- 05. Select the column to be convert & Right Click
- 06. Select Convert YYYYMMDD to DD/MM/YYYY...
- 07. Continue Step 5 to 6 for other column
- 08. Done can continue norm Step 5 to 12
Can I import without Tax Amount (i.e. SQL Account auto Calculate the Tax Amount)?
- Yes with Version Build 10 & above, just
- Don't Map the fields
- - Amount
- - TaxAmt
- - TaxInclusive
- Just Map Fields
- - Qty
- - UnitPrice
- - Tax
Why My Excel data shown as eg 5% but once I Get File become 0.05?
- It because Excel will auto convert the 5% to 0.05 in data.
- Solution : Can try use this formula =CONCATENATE(E1*100,"%")
How to add file link in the Attachments Fields?
- Just Add File.Your Link file
- Eg File.http://cdn.bulbagarden.net/upload/3/36/648Meloetta-Pirouette.png
My Excel for Credit Note/Payment is in Negative Value, can it be import?
- No you can't import the document amount with Negative Value.
- In Version 4.3.2.14 & above had function call *-1 All Data can help to convert to positive before import.
- Steps
- 01. Follow from Step 1 to 4 as above
- 05. Select the column to be convert & Right Click
- 06. Select *-1 All Data
- 07. Continue Step 5 to 6 for other column
- 08. Done can continue norm Step 5 to 12
In My Excel Only had SSM or GST No. for Customer/Supplier can it be import?
- Yes using Version 16 & above.
- Just map your SSM or GST No to the Code Field.
- If your Excel don't had both SSM or GST No. you can Maintain it In Remark Field in Maintain Customer for the Excel Company Code. (Available in Version 19 & above)
Can system Auto Set Tax code if my Excel don't had tax code column?
- Yes using Version 16 & above
- Just make sure Default Value field is Empty
Prompt "Error Unexpected error from external database driver (1)" when Get file from excel
This is due to Windows Security update KB4041681
Solution
- 01. Save the Excel file to xlsx
- 02. Install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe)
- 03. Reboot the PC.
Prompt "Provider cannot be found. It may not be properly installed" when Get file from excel
- 01. Install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe - 32 bits)
- 02. Reboot the PC.
- OR
- Can try use Get File 2... under the arrow down button at Get File... button
My excel String size is longer then SQL, how I can reduce it?
- Can try use this formula =LEFT(A2,30) (eg for get 1st 30 character from A2)
- In Version 4.6.3.19 & above had function call Truncate String... can help to reduce it before import.
- Steps
- 01. Follow from Step 1 to 4 as above
- 05. Select the column to be convert & Right Click
- 06. Select Truncate String...
- 07. Enter the New size
- 08. Continue Step 5 to 6 for other column
- 09. Done can continue norm Step 5 to 12
- Other FAQ