SQL Acc XLS n MDB Import

From eStream Software

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

  1. Easy to generate
  2. About 80% of modules is covered.
  3. Support GST
  4. FREE import for Master file.

Cons

  1. Only can do New/Insert action
  2. Is Batch update.
  3. There is a cost for End User and Dealer for Transaction import. Only Free if each file record less then 100.
  4. Same DocNo for Different Supplier not cover
  5. Payment & Credit Note Knock Off for both Customer & Supplier not cover
  6. No transfer status for Sales & Purchase Module

Pros (UE Only)

  1. Easy to generate
  2. Able update Stock Information.
  3. Can do New/Insert/Edit/Update action

Cons (UE Only)

  1. There is a cost for End User and Dealer. Only Free if each file record less then 100.
  2. 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

Example Template

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--

----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Tips for Programmer to Export Transaction 
01. Use INNER JOIN SQL to join the Master & Detail table 
02. Export to Excel or Access file
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg 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)
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Tips for Coca-Cola Transaction Import 
User might had to at Coca-Cola system before export 
01. Group the ARTNUM
02. Net the AMTSUBJECTTOGST & GSTAMT column
It might had rounding issue after import 
(i.e. excel not match with Tax Invoice Issue from coca-cola)
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Tips for GSK Transaction Import 
01. User might had to convert the file to excel file
02. Export File by Document Type (i.e. Invoice 1 file & Credit Note 1 file)
----------------------------------------------------------------------------------------------------------------------------------------------------

Things to Consider

  • Date Format should be in dd/mm/yyyy (entry time in Excel as dd/mm/yyyy) - Updated 14 Nov 2013
Excel-DateFormat.jpg
  • 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
Excel-Import-02.jpg
  • Try just use Alphanumeric for the Worksheet Name
Excel-Import-06.jpg

Import Program

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)
Excel-Import-04.jpg
03. Select the File file wanted to import
04. Click Open
Excel-Import-03.jpg
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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg

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)
Excel-Import-04.jpg
03. Select the File file wanted to import
04. Click Open
Excel-Import-05.jpg
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.
----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg
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
----------------------------------------------------------------------------------------------------------------------------------------------------

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.
Excel-Import-07.jpg
07. Tick the Column(s) to Group
08. Click 3. Verify... button
Excel-Import-08.jpg

Split Memo to Columns

Menu: Tools | Split Memo to Columns...

01. Follow from Step 1 to 6 as above.
Excel-Import-09.jpg
07. Set Number of Column(s) to split (Default is 4).
08. Select the Column to be Split
09. Click 5. Split button.
Excel-Import-10.jpg
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.
Excel-Import-13.jpg
07. Set Number of Column(s) to split (Default is 4).
08. Select the Column to be Convert
09. Click 5. Convert button.
Excel-Import-14.jpg
10. Scroll to the right. Highlight in Yellow is the newly Columns Created.
11. Click 6. Export Excel(*.xls) to export out the Result.
Template.Tips-01.jpg System will use
  • The 1st column in excel as a Key of Grouping.
  • The empty row as "Reset/End of" Record.

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
Excel-Import-11.jpg
05. Select the column to be convert & Right Click
Excel-Import-12.jpg
06. Select *-1 All Data
07. Continue Step 5 to 6 for other column
08. Done can continue norm Step 5 to 12

See also

Retrieved from "https://wiki.sql.com.my/index.php?title=SQL_Acc_XLS_n_MDB_Import&oldid=7245"
Last modified
This page was last edited on 17 June 2017, at 02:21.