SQL Acc XLS n MDB Import: Difference between revisions

From eStream Software
Line 85: Line 85:
===Transactions Data===
===Transactions Data===
* Coming Soon...
* Coming Soon...
==FAQ==
May refer to [[SQL_Accounting_Linking#FAQ|FAQ]]


==See also==
==See also==
* Others [[Customisation]]
* Others [[Customisation]]

Revision as of 03:49, 23 October 2015

Introduction

Is an External Program which able to import below data to SQL Accounting using Excel or Access File.

  • Master file (eg. Debtors, Creditors & Item List)
  • Transactions (Coming Soon)

Pros

  1. Easy to generate
  2. About 80% of modules is covered.
  3. Support GST
  4. Free of Charge for End User (Master file import ONLY Except SQLAccXLSnMDB UE)
  5. Able update Stock Information (SQLAccXLSnMDB UE ONLY)

Cons

  1. Only can do New/Insert action (Except SQLAccXLSnMDB UE which can do New/Insert & Edit/Update).

Tools Required

Example Template

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
  • 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
Excel-Import-02.jpg

Import Program

History New/Updates/Changes

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

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

  • Coming Soon...

FAQ

May refer to FAQ

See also