SQL Acc XLS n MDB Import: Difference between revisions

From eStream Software
Tag: Reverted
Tag: Manual revert
Line 4: Line 4:
|-
|-
|  
|  
Excel Import
Is an External Shareware Program which able to import below data to SQL Accounting using Excel or CSV or TXT File.
Is an External Shareware Program which able to import below data to SQL Accounting using Excel or CSV or TXT File.
* Master file (eg. Debtors, Creditors & Item List)
* Master file (eg. Debtors, Creditors & Item List)

Revision as of 03:27, 23 November 2023

Introduction

Is an External Shareware Program which able to import below data to SQL Accounting using Excel or CSV or TXT 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(Bar(|),Comma(,),Semi Comma(;),Tilde(~)) Format - Only available in Version 5.10.9 & above
  9. Support import Json Format - Only available in Version 5.11.10.43 & above
  10. No rows limit (but recommended max 500 rows per file as more records, more hard & time consumption to check if have error)

Cons

  1. Only can do New/Insert action not applicable for Version 5.16.10.62 & above
  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 not applicable for Version 5.15.10.57 & above
  5. No transfer status for Sales & Purchase Module not applicable for Version 5.18.18.80 & above
  6. Unable to import Serial Number
  7. Not support Same DocNo with Different Supplier Code - not applicable for Version 5.11.10.43 & above

Pros (UE Only) - (Deprecated)

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

Cons (UE Only) - (Deprecated)

  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.

Requirement

Example Template

History New/Updates/Changes

--Update 18 Nov 2023--

  • Update ChartOfAccount_GL FieldName not correct for auto mapping

--Update 13 Nov 2023--

  • Update Sales & Purchase for Transfer Status Fields

--Update 25 Oct 2023--

  • Add GL Stock & PD_JO (Job Order Template)
  • Update STAS_STDS template with note & fixed error sample value

--Update 09 Oct 2023--

  • Update Note on some field which Can't Empty

--Update 03 Oct 2023--

  • Change some field from Compulsory to Optional

--Update 25 Aug 2023--

  • Update Bank Giro List

--Update 13 Dec 2022--

  • Update StockItem_BOM Missing Column & data not sorting

--Update 25 Nov 2022--

  • Add new Customer&Supplier Master Format
  • Add Maintain Shipper Format

--Update 28 Oct 2022--

  • Fixed Payment Example Not Correct KO DocNo

--Update 18 Oct 2022--

  • Add Example rounding for Sales & Purchase

--Update 06 Oct 2022--

  • Add More sample for Journal Voucher
  • Add Note for Journal Voucher

--Update 01 Jun 2022--

  • Add Batch Delete format
  • Add Currency With Detail format

--Update 14 Apr 2022--

  • Add ST_Item_Alt format

--Update 19 Oct 2021--

  • Update ARAP_Deposit Data to match Sample data
  • Add ARAP_CN_KO Format
  • Add ARAP_Deposit_Refund Format
  • Add ARAP_Deposit_Forfeit Format

--Update 05 Oct 2021--

  • Update StockList Missing Isbase column & Required field for RATE1

--Update 11 Sep 2021--

  • Add Price Tag Example for Stock List Template

--Update 18 Aug 2021--

  • Add Enable Import Stock Item Matrix Profile Template

--Update 03 Apr 2021--

  • Add GL_Budget Template
  • Add AR_CustomerBranch Template

--Update 14 Dec 2020--

  • Add StockLit_PriceTagARAP Template
  • Update StockGroup, ST_ITEM_TPL, ST_Batch, Tariff & StockItem_BOM Template no mention field size

--Update 14 Aug 2020--

  • Update ChartOfAccount_GL - Add More Example & Notes

--Update 04 Jul 2020--

  • Update MaintainSupplier_Giro - Update to Match with Testing Company
  • Add StockList_UOMnPrice Template

--Update 03 Jun 2020--

  • Split Stock Issue & Stock Receive Template

--Update 26 May 2020--

  • Add Stock Assembly & Disassembly Template
  • Add more example for CashBookEntry_PV_OR
  • Add Customer Deposit Template

--Update 15 May 2020--

  • Move file to Google Drive

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


--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, txt or CSV 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
  • No Hidden column
  • Avoid use Unicode Character for Row 1 (Header)
  • 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
  • Make sure the Key of Records is properly Sorted
Excel-Import-41.jpg
  • Special Character (for csv or txt output only)
User Input Text Output
SalesOrder SalesOrder
Sales Order 5' 12" "Sales Order 5' 12"""
SalesOrder6'13" "SalesOrder6'13"""
SalesOrder6" x 13" "SalesOrder6"" x 13"""
Sales Order "Sales Order"
Sales Order 5' 12 "Sales Order 5' 12"
Sales Order 12" "Sales Order 12"""
Sales;Order 12 "Sales;Order 12"



Template.Tips-01.jpg
For Easy Export you can use " for all output value
Eg "AL","161924123243970563"

Import Program

  • Version (5.18.18.80) - 18 Nov 2023
  • Version (4.5.4.37) - 01 Jun 2022 (UE Only) - (Deprecated)
  • Evaluation Limit : 100 rows Records per file (Except Master File with not limit)
  • SQLAccXLSnMDBImp-setup.exe
  • MD5 : FBA4D306FC3FF6A037A1580575BE25F3

History New/Updates/Changes

--Build 80--

  • Upgrade to Version 5.18.18
  • Allow Import Empty Seq for Maintain Stock Item - Price Tag Import.
  • Fixed GL Cash Book Data not link with docno.
  • Enable Transfer Status for Sales & Purchase Module.
  • Add highlighter for Error after verify.

--Build 79--

  • Upgrade to Version 5.17.17.
  • Add Maintain Stock Item - Category.
  • Fixed Access Violation when Get Data From mdb file.
  • Enable Auto get UnitPrice in SQL Acc by Not mapping UnitPrice & Set Default Value.
  • Fixed Maintain Stock Item no PreVerify for Code field.
  • Fixed CompanyName not check for field Mapping for Maintain Customer/Supplier Import.
  • Add GL Stock Value.
  • Add Job Order.
  • Add Option "System Auto Detail" for Job Order, Stock Assembly & Stock Disassembly.

--Build 78--

  • Fixed Verify Not correct when no Itemcode Mapping.

--Build 77--

  • Upgrade to Version 5.17.16.
  • Fixed Error on Function Memo to Column when split Memo row more then No of Columns.
  • Add Clear Cache Data Option.
  • Enable support Search Description2 for PaymentMethod Field.
  • Fixed Verify UOM not same as excel when if itemcode have more the 1 UOM with Rate=1.
  • Fixed ItemCode UOM sometime not correctly check.

--Build 76--

  • Upgrade to Version 5.17.15.
  • Fixed unable to find record if On Spot Change Code.
  • Fixed import csv with long number become scientific notation.
  • Fixed import csv with missing number begin with 00x.
  • Add F1 shortcut for Get User Guide... function.
  • Add Load Last Posted Status Information... function - For Transactions.
  • Fixed Auto Import Stock Item Fail.

--Build 75--

  • Enable Support Read UOM from Barcode & Stock Alt Item Table.
  • Fixed Detail Description should not have Default Value.
  • Fixed AR & AP KO should not show Agent & Area Mapping.
  • Fixed AP Verify Error.
  • Fixed Customer CN, Supplier DN & CN Default Value not correct.

--Build 74--

  • Upgrade to Version 5.17.14.
  • Enable Support Direct DB Connect for SQL Connect.
  • Enable Auto Remember by BizType for Auto Replace.
  • Fixed Error when Exit Program if the db is registered.
  • Fixed Verify DocNo for Customer should excluded code.
  • Fixed Default Open from Desktop else from My Document folder.
  • Fixed Verify Slow.
  • Add Option Auto KnockOff for Customer/Supplier Payment & Refund.

--Build 73--

  • Fixed Error after done import Maintain Customer/Supplier New format.

--Build 72--

  • Fixed Source Mapping Field have Extra Column.
  • Fixed Get CSV with Unicode Error.
  • Fixed Access Violation when posting Payment.

--Build 71--

  • Fixed Import SL & PH missing detail.

--Build 70--

  • Upgrade to Version 5.17.13.
  • Remove 'Mapping Field can't be empty' for Stock Transfer Header Code.
  • Enable Support Import Seq Field for Maintain Stock Item - BOM.
  • Fixed Duplicate DocNo when Exce/CSV file data not sort by DocNo.
  • Enable Read File in subfolder of pending for Auto Import.
  • Add new option Include SubFolder File(s)... for Auto Import for multi 3rd Party Export to different folder.
  • Enable Search AltCode for ItemCode field.
  • Enable Import Maintain Shipper.
  • Enable Auto Generate DocNo using prefix <<New>>.
  • Fixed Stock Transfer Customer code not checking other Maintain Customer fields.
  • Fixed Verify 2nd time data gone.
  • Fixed Verify Empty Value for Terms should bypass.
  • Add New Format for Maintain Customer & Maintain Supplier to support Replace Action.
  • Combine UE Function.
  • Add Option Get file(s) from FTP... for transactions import.
  • Enable Replace Option for Duplicate transaction for All Maintenance (Except Maintain Account).

--Build 69--

  • Add Remove Other Character(s)... function.
  • Fixed Copy Cell Value not enable for single table import.
  • Fixed SUOMQTY field missing from mapping.

--Build 68--

  • Fixed Field Auto Mapping not check equally match column.
  • Fixed Sales.Points.OPF can't Auto Import.
  • Fixed Import Attachment field become RichText.
  • Fixed Import GL PV & GL OR error.

--Build 67--

  • Change FTP download TransferType to Binary.
  • Fixed Error when download FTP file with folder.
  • Add Double Click Pending & Done label to open the folder.
  • Add Test FTP Download option on Right Click Pending label.

--Build 66--

  • Upgrade to Version 5.16.12.
  • Fixed Slow on Auto Mapping function.
  • Move Select Record Range to top.
  • Fixed Tab Order.
  • Add Batch Delete function.
  • Enable Replace Option for Item Template & Maintain Batch.
  • Fixed Import Stock Template not set 'Mapping Field can't be empty' for Code field.
  • Allow import Detail Maintain Currency.
  • Fixed FTP download Error.

--Build 65--

  • Fixed Points Verify Keep shown Invalid Customer code.
  • Fixed Points Post to Account prompt Access Violation.
  • Fixed Verify if Mapped field with empty data for Agent, Area, Project should by pass

--Build 64--

  • Upgrade to Version 5.16.11.
  • Add Import Stock Alternative.
  • Add Auto Resize Prefix Main Screen Size.

--Build 63--

  • Fixed Error Import Matrix Profile.
  • Fixed Disc have value even Excel don't have Disc Column.

--Build 62--

  • Upgrade to Version 5.16.
  • Enable Replace Option for Duplicate transaction.

--Build 61--

  • Fixed Add for detail Mapping override by automap.

--Build 60--

  • Fixed GL Acc Verify not correctly for SpecialAccount Type.
  • Add Filter for Untick Only, Tick Only & All option for GL Acc.
  • Misleading information at Stock Item Filter record to record when set 501 to 1000 become 500

(conflict with to rec & max record)

--Build 59--

  • Fixed Maintain Batch Source Field missing last column.
  • Fixed Stock Item Not Auto Maping Field for Sub Table.
  • Fixed GL Acc Verify missing some Account Type & special Account type.
  • Fixed Import Unicode for More Description become ???

--Build 58--

  • Fixed Payment Auto Mapping not correct due to Fixed Journal Auto Mapping.
  • Fixed More Description & Attachment not support Unicode.

--Build 57--

  • Upgrade to Version 5.15.
  • Fixed Journal Auto Mapping not Correct for DR & CR.
  • Fixed GL Maintain Account Verify with AccType = IV shown invalid.
  • Add Customer Credit Note - Knockoff.
  • Add Supplier Credit Note - Knockoff.
  • Fixed Customer Deposit Company Code checking Code same as other Document (eg IV)
  • Add Customer/Supplier Deposit Refund.
  • Add Customer/Supplier Deposit Forfeit.
  • Add Supplier Deposit.

--Build 56--

  • Fixed Matrix Profile for D1SUBCODE & D2SUBCODE not fully import due to too long list.

--Build 55--

  • Remove GL Stock Value function which is not ready.
  • Add Checking For AccType & SpecialAccType for GL_Acc Import.
  • Add Checking Mandatory Field for GL_Acc Import.
  • Enable Import Stock Item Matrix Profile.

--Build 54--

  • Shortcut to Open Selected file.
  • Fixed DB Import Unicode become ?.

--Build 53--

  • Fixed Auto Import RPC server is unavailable Error.
  • Fixed Stock Item ItemCode not Verify.

--Build 52--

  • Upgrade to Version 5.14.
  • Add Import GL Budget.
  • Fixed Verify Account should not exclude Bank & Cash Type.

--Build 51--

  • Add Force Kill SQLAcc for AutoImport.
  • Fixed Verify Account not excluding Customer,Supplier & Stock Account.
  • Fixed FTP - Not able move to Archive Folder.
  • Fixed unable to find data if query containing the '&', '!' characters.

--Build 50--

  • Fixed Detail Description map as Description_HDR.
  • Skip Posting KnockOff if No Map KODocNo.
  • Fixed Verify for AR_CN & AP_SC prompt DocType Required when no Map.

--Build 49--

  • Fixed Import GL Maintain Account Get excel file still old method.

--Build 48--

  • Fixed Payment KnockOff field not auto map.
  • Enable Grid Export for Direct Import DB.
  • Enable Mini Utilities for Direct Import DB.
  • Fixed Grid Export not working.
  • Add Grid Export for Master data

--Build 47--

  • Fixed Import GL Maintain Account Some Account Description is Empty.
  • Fixed GL Maintain Account unable reset to empty after drag back to source.
  • Fixed Import Stock Item Randomly Error Prompt.

--Build 46--

  • Add Checking Only Get Excel Row Data when Column A is not Empty.
  • Fixed unable to connect to DB in SQL Connect.

--Build 45--

  • Upgrade to Version 5.13.
  • Upgrade IDE to Delphi 27.
  • Error when Import BOM with mapping empty OverheadCost.
  • Add Delete Existing Data... option for Supplier Giro & Opening SST Import
  • Fixed Post Button should only enable after Verify
  • Fixed Progress Bar not update when lot of records.
  • Fixed Error Get Json File if Not Registered.
  • Fixed Error when Click Get Data 2nd Times for Json File.

--Build 44--

  • Upgrade to Version 5.12.10.
  • Auto Force Use Get File 2 for Get File for Excel format.
  • Fixed Customer/Suppler Payment missing DocAmt field.
  • Fixed Get Excel Example Template Link not update.

--Build 43--

  • Upgrade to Version 5.11.10.
  • Fixed Verify payment method not correctly checking.
  • Enable Import Stock Item Assembly.
  • Enable Import Stock Item Disassembly.
  • Enable Support Same DocNo but Different Supplier Code.
  • Enable Import Customer Deposit.
  • Fixed Maintainance not force Code Mapping.
  • Add Import from Json File format - Stage 1 of 2.
  • Fixed Auto Login Password Not Case Sensitive.

--Build 42--

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


History New/Updates/Changes (UE Only) - (Deprecated)

--Build 37--

  • Upgrade to Version 4.5.4.
  • Fixed Slow on Auto Mapping function.
  • Move Select Record Range to top.
  • Fixed Tab Order.

--Build 36--

  • Upgrade to Version 4.5.3.
  • Add Import Stock Alternative.
  • Add Auto Resize Prefix Main Screen Size.

--Build 35--

  • Upgrade to Version 4.5.
  • Add option Delete Existing Data for all Import Option except Maintain Stock Item.

--Build 34--

  • Fixed Add for detail Mapping override by automap.

--Build 33--

  • Misleading information at Stock Item Filter record to record when set 501 to 1000 become 500 (conflict with to rec & max record)

--Build 32--

  • Fixed Stock Item Not Auto Maping Field for Sub Table.
  • Fixed Import Unicode for More Description become ???

--Build 31--

  • Fixed More Description & Attachment not support Unicode.

--Build 30--

  • Fixed Prompt Error when excel UOM Rate is empty.

--Build 29--

  • Internal Release.

--Build 28--

  • Fixed DB Import unicode become ?.

--Build 27--

  • Shortcut to Open Selected file.
  • Fixed Status with OK Action should be Import not Replace.
  • Fixed Default Should Tick All.

--Build 26--

  • Upgrade to Version 4.4.
  • Add Function &1. Maintain Customer - Branch...

--Build 25--

  • Fixed unable to find data if query containing the '&', '!' characters.

--Build 24--

  • Disable Finish Button for DB Import.
  • Fixed Price Tag Import Discount not clear when is empty.

--Build 23--

  • Fixed Verify Not correct for Maintain Stock Item - UOM & Price.

--Build 22--

  • Upgrade to Version 4.3.2.
  • Add Checking Only Get Excel Row Data when Column A is not Empty.
  • Fixed unable to connect to DB in SQL Connect.
  • Fixed Import Stock Item Randomly Error Prompt.
  • Fixed Click Next slow in Direct Import Action.
  • Enable Grid Export for Direct Import DB.
  • Enable Mini Utilities for Direct Import DB.
  • Add Maintain Stock Item - Price Tag - Customer...& Maintain Stock Item - Price Tag - Supplier...

--Build 21--

  • Fixed Update with Customer & Supplier Item Unable Update if More then 1 Record

--Build 20--

  • Fixed Update with Customer & Supplier Item Error.

--Build 19--

  • Upgrade to Version 4.3.
  • Upgrade IDE to Delphi 27.
  • Add Stock Item - BOM.
  • Update Get Excel Example Template Link.
  • Auto Force Use Get File 2 for Get File for Excel format.
  • Add Stock Item - UOM & Price.

--Build 18--

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


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 (Max. 60 Character each Column).
  • 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 runtime to modify the data before begin import
Steps
01. Follow from Step 1 to 4 as above
Excel-Import-15.jpg
Excel-Import-15A.gif
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
Remove Others Characters... Delete any characters given in the selected Column

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

My excel date format is in dd-mmm-yyyy with time format, how I convert it to dd/MM/yyyy?

Can try use this formula =TEXT(DATEVALUE(TEXT(A41,"dd-mmm-yyyy")),"dd/mm/yyyy")

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 (Version 781 & Below)
  4. BRN (Version 782 & Above)
  5. BRN2 (Version 782 & Above)
  6. GSTNo
  7. BizNature
  8. Remark
  9. 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. Alternative Code (Available in Build 70 & above)
  8. CompanyItemCode
  9. 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?

Excel-Import-40.jpg
Yes just set the column & Default Value for DocType, KODocNo & KOAmt to empty


Why after verify all detail Status shown ok but Header Still untick?

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

Why After Import to the Note/Attachment/Description3 data is truncated to 286 characters?

Make sure In Your Excel Header name is using Note/Description3/Attachment

Prompt "Access violation at address xxxxxx in module...." when Get file from excel

Open the excel try delete few column at the end of excel (see example below)
Excel-Import-42.jpg

How to prevent Import Price Tag with empty row?

Excel-Import-43.jpg
Make sure your Excel consist of
  • Price Tag Qty
  • Price Tag UOM (don't share same UOM for other field mapping for same Item code)

How to convert the cell value so I can sum using formula in excel?

Can try use this formula =Value(A2)

Can I Reimport for Same Transactions?

Yes for Version 5.16.10.62 & above
Excel-Import-44.jpg
01. Right Click Here & Tick the option Auto Replace for Duplicate
02. Click Get file & Select Excel Sheet
03. Click Get Data
04. Click Verify
05. Click Post To A/c

What is alternative for FTP?

Excel-Import-45.jpg
You can use like

How to add Excel Import Shortcut in SQL Accounting?

You may refer here for steps


How to enter Multi Line for More Description in Excel?

Excel-Import-47.jpg

You can enter it all in 1 cell

How to convert Excel to CSV file?

Excel-Import-48.gif

How to Load CSV file?

Excel-Import-49.gif

I have problem to export field name with ( and ) can I ignore it?

Yes you can just ignore the ( and )
eg Code(10) can just export as Code10

Why the Auto Mapping is not correctly map?

Make sure the column position you export out is same as our template column position

I have more then 1 3rd party to import, can I create folder for each party?

Yes can for Build 70 & above. Below is Example to setup 3 diff POS System
Excel-Import-50.jpg
In the Auto Import
  • Pending Folder - Select the Root folder where have more then 1 3rd party
  • Tick/Select Include SubFolder File(s)
Excel-Import-51.jpg

Can I use SQL Accounting Auto Generate DocNo?

Yes can for Build 70 & above. Just Prefix <<New>>n in the DocNo field for each set where n is number. Example below is for 2 Invoice number
Excel-Import-52.jpg

How to Convert text number to number in Excel?

Excel-Import-53.gif

Can I import without UnitPrice (i.e. SQL Account auto Calculate the UnitPrice)?

Yes with Version Build 79 & above, just
Don't Map & Set Empty For Default Value for the fields
- UnitPrice
- DISC
- TaxAmt
- TaxInclusive
- Amount
Excel-Import-54.jpg

See also