Line 314: | Line 314: | ||
{| | {| | ||
|- | |- | ||
| [[File:Template.Tips-01.jpg|60px]]|| | | [[File:Template.Tips-01.jpg|60px]]|| System will use | ||
:* | :* The '''1st column''' in excel as a Key of Grouping. | ||
:* The empty row as "Reset/End of" Record. | |||
|} | |} | ||
---------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
Revision as of 02:21, 17 June 2017
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
- Nestle Transactions
- Yeos Transactions
- Coca-Cola Transactions
- GSK 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 100.
- Only Cover Maintain Stock Item.
Todo
- Import Payment with Verify option
- Import Maintain Item Template
- Import Stock Issue
- Import Stock Received
- Import Stock Adjustment
- Import Stock Transfer
- Add Verify Button for Master Data Import
- Import Giro for supplier
Tools Required
- Microsoft Excel or Access
- AccessDatabaseEngine.exe (optional)
Example Template
- Updated 08 May 2017
- www.estream.com.my/document/SQLAcc-ImportExcel.xls
History New/Updates/Changes
--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--
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
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 & Default Value is 0 not empty
- Below is the column type can/can't import
- Try just use Alphanumeric for the Worksheet Name
Import Program
- Version (4.3.2.14) - 08 Jun 2017
- Version (4.2.1.9) - 01 May 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 : 4A6AD5C0346143A1888AB511B736AD19
History New/Updates/Changes
--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 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.
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Options
- Only available in Version 4.3.2.14 & above
Check Duplicate Records
Menu: Tools | Check Duplicate Records...
- 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...
- 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
- Coming Soon...
- 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))
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
- Other FAQ