SQL Acc XLS n MDB Import: Difference between revisions

From eStream Software
Line 170: Line 170:
===History New/Updates/Changes===
===History New/Updates/Changes===
--Build 42-- Coming Soon...
--Build 42-- Coming Soon...
* Upgrade to Version 5.10.10.
* Fix Post Button should only enable after verify for Maintain Item Import.
* Fix Post Button should only enable after verify for Maintain Item Import.
* Remove checking for Knock off DocNo in Preverify.
* Remove checking for Knock off DocNo in Preverify.
Line 456: Line 457:


<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>
===History New/Updates/Changes (UE Only)===
===History New/Updates/Changes (UE Only)===
--Build 18-- Coming Soon...
--Build 18-- Coming Soon...

Revision as of 03:37, 22 February 2020

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
QR XLSnMDBImp.jpg

Pros

  1. Easy to generate
  2. About 80% of modules is covered.
  3. Support GST/SST
  4. FREE import for Master file.
  5. Support import CSV/TXT(semi Comma) Format - Only available in Version 4.7.4.23 & above not applicable for Version 5.10.9 & above
  6. Support Schedule Auto Download from Simple FTP - Only available in Version 5.10.6.32 & above
  7. Support Schedule Auto Import for Excel/CSV/TXT - Only available in Version 5.10.6.32 & above
  8. Support import CSV/TXT(semi Comma, comma or bar) Format - Only available in Version 5.10.9 & above

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 rows.
  4. Credit Note Knock Off for both Customer & Supplier not cover
  5. No transfer status for Sales & Purchase Module
  6. Unable to import Serial Number

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 & Know Bugs

  • UE for All Master file import
  • Enable Get File 2 default to load all (if can)

Requirement

Example Template

History New/Updates/Changes

--Update 12 Feb 2020--

  • Update Sales & Purchase Tab Information to Match with Testing Company

--Update 03 Jan 2020--

  • Add Point Format

--Update 09 Sep 2019--

  • Add Opening SST Service Tax Format

--Update 10 Sep 2019--

  • Standardize the Header & Detail Column name where had same name
  • Add Stock Batch Format
  • Add Stock Item Template Format

--Update 28 Mar 2019--

  • Update Sales & Purchase Column Name & position so it can auto map on Get File in program

--Update 05 Jan 2019--

  • Add JE type for Customer/Supplier DN/CN.

--Update 09 Oct 2018--

  • Fix JV, PV & OR Detail Account Not match with sample

--Update 03 Oct 2018--

  • Add Import Maintain Tariff Format

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

----------------------------------------------------------------------------------------------------------------------------------------------------
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) or can use
    My Excel for Credit Note/Payment is in Negative Value, can it be import?
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
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

  • Float, Currency & Integer type Must not had , (comma) or Currency Symbol & Default Value is 0 not empty
  • No Formula in the cell
  • No Merge cell is use
  • Date Format should be in dd/mm/yyyy (entry time in Excel as dd/mm/yyyy) - Updated 14 Nov 2013
Excel-DateFormat.jpg
  • 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

  • Version (5.10.9.41) - 09 Jan 2020
  • Version (4.2.1.17) - 17 Dec 2019 (UE Only)
  • Evaluation Limit : 100 Records per file (Except Master File with not limit)
  • SQLAccXLSnMDBImp-setup.exe
  • MD5 : D5C4EE973A92EC370459F977E5C177BA

History New/Updates/Changes

--Build 42-- Coming Soon...

  • Upgrade to Version 5.10.10.
  • Fix Post Button should only enable after verify for Maintain Item Import.
  • Remove checking for Knock off DocNo in Preverify.
  • Fixed DocNo Status Not in Memo Mode.
  • Allow Import ItemCode without UOM & Defaul Value for SL, PH & ST.
  • Enhance Convert YYYYMMDD to DD/MM/YYYY function by remove - and . before convert.
  • Reduce the Maintain Item windows Min Height size to 700.

--Build 41--

  • Upgrade to Version 5.10.9.
  • Add Export Log Option for Auto Import.
  • Enable Import Point.
  • Fix Drag from Target to Source Not Clear for Maintenance Import.
  • Enable bypass mapping & use Default Value for Knock Off import.
  • Fixed No checking When PAYMENTMETHOD not map for Payment Import.
  • Enable Prompt for Delimiter option when import for TXT/CSV in Get File 2 & Get File 3.


--Build 40--

  • Fix unable to Connect Data Base if Data in Linux.

--Build 39--

  • Upgrade to Version 5.10.8.
  • Fixed Direct Import to DB no Auto Open Excel when only 1 Excel Sheet.
  • Fixed No checking on Field Mapping in Giro Import.
  • Add Import Opening SST Service Tax.

--Build 38--

  • Enable Support Version 782 & above (New ROC).

--Build 37--

  • Add Get Excel Example Template...Function under Help Menu

--Build 36--

  • Fixed Batch Import Unable to Import due to IsActive is Boolean Type.
  • Enhance support 99.9999% Accurate AutoMapping.
  • Remove Extra Fields for Stock Item Template.

--Build 35--

  • Fixed Verify shown ??? if itemcode in Unicode.
  • Fixed Error on Limited User on run application.

--Build 34--

  • Upgrade to Version 5.10.7.
  • Fixed Register DB should not Case Sensitive.
  • Fixed Error when Import Customer/Supplier List without Mapping Code Field.
  • Fixed Version not update in 32 Bits.
  • Enable support Running In Windows Limited User.
  • Fixed Search ItemCode In Note Problem if had ABC & 2ABC for different ItemCode.
  • ReSequence the Search ItemCode & CompanyCode.
  • Add Search In Note Field for CompanyCode.

--Build 33--

  • Import Amount become 0 after post if using 64bits.
  • Import BOM Verify error if using 64 bits.
  • Change New Icon with support 256*256 size.
  • Fixed Verify should ignore for empty ItemCode.
  • Fixed Verify Company Code not correctly if the data is empty.

--Build 32--

  • Upgrade to Version 5.10.
  • Fixed Error OnOpen Auto Import Setting for 1st time use.
  • Fixed Setting will loss on Uninstall.
  • Add Show Alert for Standalone for Auto Import.
  • Add Schedule Import for Excel - Stage 2 (Customer & Supplier Data Entry).
  • Add Schedule Import for Excel - Stage 3 (Stock Data Entry).
  • Add Schedule Import for Excel - Stage 4 (GL Data Entry).
  • Fixed Save Maintain Customer & Supplier Setting is Mixed up.
  • Add Auto Field Maping for Maintain Customer & Supplier Branch.
  • Fixed Maintain Customer & Supplier not Auto Save Setting on Post.
  • Add Schedule Import for Excel - Stage 5 (Maintain Customer & Supplier).
  • Feature : Default Untick limit Records for Registered database.
  • Fixed File still move to Done folder even had error.
  • Fixed Verify Supplier Payment & Supplier Refund Error.
  • Add Support Download from Simple FTP.
  • Fixed Load CSV shown empty data.
  • Add Schedule Import for Excel - Stage 6 (Others Maintenance - Single Table).
  • Fixed Verify Maintain Area Prompt Access Violation.
  • Fixed Unable to import Maintain Area.
  • Import ST Item Template with Amount field error.
  • Fixed Import Stock Item will prompt error "Linkfields to detail must be unique".
  • Fixed Stock Item Unable to unMap field.
  • Add Schedule Import for Excel - Stage 7 (Stock Item Template).
  • Fixed Import BOM Finish button no working.
  • Add Schedule Import for Excel - Stage 8 (Stock Batch).
  • Upgrade exe to 64 bits.
  • Change New Icon.
  • Fixed GL JV Detail Account not Verify.
  • Add Find Text in Data Grid for Data Entry Import.
  • Add Schedule Import for Excel - Stage 9 (Stock Item).

--Build 31--

  • Upgrade to Version 4.9.
  • Import BOM Prompt Access Violation if not Location fields in Excel
  • Remove P_DocNo field for Cash Sales/Purchase.
  • Fixed Verify Same DocNo for Diff. supplier shown Duplicate Doc No.
  • Fixed Data Grid not Auto width Columns.
  • If Detail had Status <> ok should abort Post
  • Add Checking for CompanyName2 field for Customer/Supplier Code
  • Add Checking for Description2 & Note field for Item Code.
  • Add Schedule Import for Excel - Stage 1 (Sales & Purchase Only)

--Build 30--

  • Upgrade to Version 4.8.6.
  • Enable Support Import JE type for Customer/Supplier DN/CN.
  • Support Auto Get ItemCode using Remark1, Remark2 or Shelf.
  • Support Auto Get CompanyCode using BizNatural.
  • Enable support Import TaxDate for JV & CB - PV.

--Build 29--

  • Fixed Verify prompt access violation if no ItemCode field map.
  • Fixed all Modified Function not working in SL, PH & ST.
  • Fixed Verify GL OR & PV shown Invalid Company Code.
  • Add New function Remove All Positive Value Data...
  • Add New function Remove All Negative Value Data...

--Build 28--

  • Fixed Verify Itemcode not fully verify.
  • Fixed if Mapped data & Default Value is empty should bypass import.
  • Fixed Verify Company Shown Invalid even is Valid Company Code.

--Build 27--

  • Fixed Source Grid had Extra unknown Field.
  • Add Remove Comma function.
  • Fixed Access Violation on Import AR & AP.
  • Fixed Load Setting not match with Save due to #10 & #13 character.

--Build 26--

  • Upgrade to Version 4.8.
  • Support Auto Get ItemCode using BarCode or Customer/Supplier ItemCode.

--Build 25--

  • Fix JV, PV & OR Detail Account Not Verify
  • Fixed Not AutoSave Setting.
  • Add Copy Cell Value Function for Data Grid.
  • Fix Import with Tariff not follow user data

--Build 24--

  • Upgrade to Version 4.7.5.
  • Enable Import Maintain Tariff.
  • Default Value for 2nd UOM for Maintain Item should set to 0.
  • Verify should bypass checking detail for opening balance else unable to import due to invalid Code.
  • Fixed Get File 3...Error on 2nd time Click.

--Build 23--

  • Upgrade to Version 4.7.
  • Fixed Memo Cell for Attachment, Note & Description3 is partly loaded.
  • Fixed KOAmt & DocType field no DefaultValue.
  • Enable Import CSV/TXT(semi Comma) file.
  • Fixed Access Violation on Get File 2 in some Situation where Excel had Empty Columns.

--Build 22--

  • 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 Information.
  • 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 relevant 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 18-- Coming Soon...

  • Fixed Import New Item Error if had mix with Replace Action when use Records.
  • Fixed Import New Item Error Invalid source array if mix with Replace Action.
  • Reduce the Windows Min Height to 700.
  • Add Show No. of Error Indicator after import if any.

--Build 17--

  • Fixed Import New Item Error.

--Build 16--

  • Fixed Import Barcode always in Base UOM.

--Build 15--

  • Add Get Excel Example Template...Function under Help Menu

--Build 14--

  • Enhance support 99.9999% Accurate AutoMapping.

--Build 13--

  • Fixed Verify shown ??? if itemcode in Unicode.
  • Fixed Error on Limited User on run application.

--Build 12--

  • Fixed Load Setting not match with Save due to #10 & #13 character.

--Build 11--

  • Fixed Not AutoSave Setting.
  • Enable Get File 2 Function.
  • Add Copy Cell Value Function for Data Grid.

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

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
Excel-Import-20.jpg
02. Select Get File 3... & system will prompt 2 dialog like below
Excel-Import-21.jpg
03. Select the Excel Header File.
Excel-Import-22.jpg
04. Select the Excel Detail File.
Excel-Import-23.jpg
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
Excel-Import-24.jpg
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.
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...

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


Split String to Columns

Menu: Tools | Split String to Columns...

This option is to Split the Long String into individual Columns.
  • Only available in Version 5.10.10.42 & above
01. Follow from Step 1 to 6 as above.
Excel-Import-37.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-38.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
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.

Auto Import

  • Only available
- in Version 4.9.6.31 & above.
- for Registered import Database.
- in Firebird Server Setting (Standalone can't Auto Import)
  • Excel Filename must be BIZOBJECT.YYYYMMDDHHMMSS.xlsx eg SL_IV.20190214101508.xlsx
  • Must only 1 Sheet in Excel File.
  • Must Manually Import for 1st time use to make sure is success mapping. - Auto Import base on last mapping import

Import Setting

Menu: File | Auto Import Settings...

Excel-Import-28.jpg
01. Select SQL Accounting DCF file
02. Select SQL Accounting Database to import & export
03. Enter the SQL Accounting User Name & Password
04. Select/Tick to Enable Download From FTP (available in Version Build 32 & above)
05. Click Next
Excel-Import-36.jpg
The following Steps if Download From FTP is Enable/Tick/Selected
06. Enter the Host/IP Address for FTP
07. Enter the FTP Port No
08. Enter the FTP User Name & Password
09. Enter the FTP Folder to download From
10. Select the Action to Do After Download
11. Click Test Connection...
12. Click Next
Action Description
Delete the file(s) in FTP Folder Will delete the file(s) after download
Move to Archive/YYYYMMDD Folder
  • Will Auto Create YYYYMMDD & move to this folder after done download
  • Make sure in the FTP folder had the folder Archive (eg download/Archive)
Leave it As It(I will self Manually Delete or Move it) Do nothing after Download

Template.Tips-01.jpg The file will download to the Pending Folder which is set at the Last Steps

Excel-Import-29.jpg
13. Select the Biz Object Type/Document Type to Import
14. Click Next
Excel-Import-30.jpg
15. Select the Pending folder where Excel files keep to import to SQL Accounting
16. Select the Done folder to move after done import the Excel file
Field Description
Last Import Date Last Import Date and Time. System will auto update
Keep Last Log Line To shown last how many line for Log
17. Click Finish Button to Close.

Windows Schedule

01. Open Windows Control Panel | Administrative Tools | Task Scheduler
Scheduler.Task1.jpg
02. Click Action | Create Basic Task...
Excel-Import-31.jpg
03. Enter Task Name (eg SQL Excel Export) & Click Next
Scheduler.Task3.jpg
04. Select Daily & Click Next
Scheduler.Task4.jpg
05. Set the Start Date & Time to Start
06. Set 1 for Recur every field & Click Next
Scheduler.Task5.jpg
07. Select Start a program & Click Next
Excel-Import-32.jpg
08. Click Browse button & find the SQLAccXLSnMDBImp.exe (Default at C:\eStream\Utilities\SQLAccXLSnMDBImp)
09. Set the Add arguments(optional) as -Auto
10. Set the Start in(optional) path to the Exe folder (eg. C:\eStream\Utilities\SQLAccXLSnMDBImp) & Click Next
Excel-Import-33.jpg
11. Tick the option Open the Properties dialog for this task when I click Finish
12. Click Finish
Excel-Import-34.jpg
13. Select the option Run whether user is logged on or not
14. Click Triggers tab at top
Excel-Import-35.jpg
15. Click Edit... button
Scheduler.Task12.jpg
16. Change the time to run
17. Click OK 2 times
Scheduler.Task10.jpg
18. Enter the window User Name & Password
19. Click Ok

Mini Data Modification Utilities

This utilities is to help user to modify the data before begin import
Steps
01. Follow from Step 1 to 4 as above
Excel-Import-15.jpg
05. Select the column to be Modify & Right Click
06. Select the Option you wanted to modify
Option/Function Description
*-1 All Data... Change the Value from Negative to Positive Value & vise versa
Convert YYYYMMDD to DD/MM/YYYY... Change the Value Date format from YYYYMMDD to DD/MM/YYYY
Truncate String... Reduce the Value field size to specific field size
Copy Cell Value... Copy the Selected row Cell Value
Remove Comma... Remove the ,
Remove All Positive Value Data... Delete all Value which is More then 0
Remove All Negative Value Data... Delete all Value which is Less then 0

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
Excel-Import-15.jpg
05. Select the column to be convert & Right Click
Excel-Import-16.jpg
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 :
Use either one of the excel formula below:
1. =CONCATENATE(E1*100,"%")
OR
2. =TEXT(E1,"0.00%")

How to add file link in the Attachments Fields?

Just Add File.Your Link file
Eg With 2 Attachments file in 1 cell
File.https://cdn.bulbagarden.net/upload/3/36/648Meloetta-Pirouette.png
File.https://cdn.bulbagarden.net/upload/6/63/Pokk%C3%A9n_Pikachu_Libre.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

In My Excel Only had SSM or GST No. for Customer/Supplier can it be import?

Yes using Build 16 & above.
Just map your SSM or GST No to the Code Field.
See below Other available Fields can map
Available SQL Accounting Field Available Import Build
SSM/Reg No 16
GST No 16
Remark 19
Biz Natural 30
Company Name 2 31
Note 34

What is the Company Code Search Sequence?

It will search as following sequence (Available in Build 34 & above)
  1. Code
  2. CompanyName2
  3. RegisterNo
  4. GSTNo
  5. BizNature
  6. Remark
  7. Note

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 1

Can try use Get File 2... under the arrow down button at Get File... button

Solution 2

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

Solution 1

Can try use Get File 2... under the arrow down button at Get File... button

Solution 2

01. Install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine.exe - 32 bits)
02. Reboot the PC.

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
Excel-Import-17.jpg
05. Select the column to be convert & Right Click
Excel-Import-18.jpg
06. Select Truncate String...
Excel-Import-19.jpg
07. Enter the New size
08. Continue Step 5 to 6 for other column
09. Done can continue norm Step 5 to 12

My excel Don't had account code if got ItemCode can it be import?

Excel-Import-25.jpg
Yes just make sure Empty the Default Value for Account field so system can Auto Get from Maintain Stock Group
Excel-Import-26.jpg

My excel only had Barcode can it be import?

Yes using Build 26 & above
Just map your Barcode to the ItemCode Field.
Available SQL Accounting Field Available Import Build
Barcode 26
Customer/Supplier Item Code 26
Remark 1 30
Remark 2 30
Shelf 30
Description 2 31
Note 31

What is the Item Code Search Sequence?

It will search as following sequence (Available in Build 34 & above)
  1. ItemCode
  2. Description2
  3. Remark1
  4. Remark2
  5. Shelf
  6. BarCode
  7. CompanyItemCode
  8. Note

Why it keep only load lst 100 records even I had registered?

Excel-Import-27.jpg
Make sure you set the Value before you Click Get File

My CSV data is 00123 but after get data become 123 (missing 00)?

Make sure you Double Quoted the value eg "00123"

What is the Proper Steps to Import BOM?

01. Import all Finish Goods & Raw Material Item code using Stock | Maintain Stock Item...&
Make sure ItemType for Finish Goods is Set to B
02. Import using Stock | Maintain Stock Item - BOM...

Can I Import Payment/Credit Note without Knock off Information?

Yes just set the column DocType, KODocNo & KOAmt to empty


See also