Import Data: Difference between revisions

From eStream Software
Line 367: Line 367:
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
|}
|}
<div style="float: right;">  [[#top|[top]]]</div>
====Leave====
{| style="margin: 1em auto 1em auto;"
|-
|
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 1 - Leave 
|-
|
TransDate,Employee,LeaveCode,UnitAsTime<br />
08/11/2015,001,UL,08:00<br />
19/11/2015,003,UL,09:20<br />
15/11/2015,005,UL,08:00<br />
01/11/2015,001,AL,08:00<br />
03/11/2015,003,AL,08:00<br />
08/11/2015,005,MC,08:00<br />
|}
||
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Example 2 - Leave 
|-
|
TransDate,Employee,LeaveCode,Unit<br />
08/11/2015,001,UL,1<br />
19/11/2015,003,UL,1.17<br />
15/11/2015,005,UL,1<br />
01/11/2015,001,AL,1<br />
03/11/2015,003,AL,1<br />
08/11/2015,005,MC,1<br />
|}
|}
{| class="mw-collapsible mw-collapsed wikitable" style="margin: 1em auto 1em auto;"
! Table Fields Properties
|-
| align="center" |'''Field Name'''
| align="center" |'''Field Type'''
| align="center" |'''Field Size'''
| align="center" |'''Remarks'''
|-
| Date || Date || align="center" |0 || In dd/mm/yyyy or dd-mm-yyyy format
|-
| Employee || String || align="center" |30 || Employee Code/ID
|-
| LeaveCode|| String || align="center" |20 || Leave Code (Eg AL, MC, etc)
|-
| Unit || FMTBcd|| align="center" |8 || Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
|-
| UnitAsTime || Time || align="center" |0 || In HH:MM format
|}
{| style="margin: 1em auto 1em auto;"
|-
|
----------------------------------------------------------------------------------------------------------------------------------------------------
{|
|-
| [[File:Template.Tips-01.jpg|60px]]|| It adviceable to use '''Unit''' instead of '''UnitAsTime''' as
:* '''UnitAsTime''' max value can enter is '''23:59''' only
:* if you Export as Grand Total
|}
----------------------------------------------------------------------------------------------------------------------------------------------------
|}
<div style="float: right;">  [[#top|[top]]]</div>
<div style="float: right;">  [[#top|[top]]]</div>



Revision as of 03:14, 29 May 2019

Introduction

This function is to import Information/Data from other Software Data
Below is the list Data that we can Import from

Special Import

Standard Import

System Contact Info
ELID Elid Sdn Bhd
SynGlobal Time Attendance SynGlobal Technologies Sdn Bhd
FingerTec TimeTec Computing Sdn Bhd
ANVIZ Hi-Profile Achievement (M) Sdn Bhd
Click Time Attendance EGroup Solution Sdn Bhd
Nexpro TimeTracX Nexus Professional Sdn Bhd
Check Time MVIT Solution
ECOTimePrint Eco Solution Technology

SQL Payroll Program

May get it from here
MD5 : 9815E0A416F0BE80B1986B5689392A36

Modules Require

For all import except UBS Payroll & AutoCount Payroll
  • Data Import

Guideline & Example

There are 2 type of import
  • Single File Import
  • Batch File Import (Available in Version 1.2019.167.146 & above)

Single File Import

01. This guide is all import except UBS Payroll & AutoCount Payroll
02. Just copy below example & paste to notepad & save it as CSV

Overtime & Wages

Example 1 - OT or Wages

Date,Employee,Code,UnitAsTime
26/6/2015,A001,DR01,04:00
28/6/2015,A001,DR02,09:38
26/6/2015,A002,DR02,06:18
29/6/2015,A002,DR03,03:22
26/6/2015,A003,DR03,04:55

Example 2 - OT or Wages

"Date","Employee","Code","Unit"
26/6/2015,A001,DR01,4
28/6/2015,A001,DR02,9.63
26/6/2015,A002,DR02,6.30
29/6/2015,A002,DR03,3.37
26/6/2015,A003,DR03,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy or dd-mm-yyyy format
Employee String 30 Employee Code/ID
Code String 20 Overtime or Wages Code
Unit FMTBcd 8 Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
UnitAsTime Time 0 In HH:MM format

Leave

Example 1 - Leave

TransDate,Employee,LeaveCode,UnitAsTime
08/11/2015,001,UL,08:00
19/11/2015,003,UL,09:20
15/11/2015,005,UL,08:00
01/11/2015,001,AL,08:00
03/11/2015,003,AL,08:00
08/11/2015,005,MC,08:00

Example 2 - Leave

TransDate,Employee,LeaveCode,Unit
08/11/2015,001,UL,1
19/11/2015,003,UL,1.17
15/11/2015,005,UL,1
01/11/2015,001,AL,1
03/11/2015,003,AL,1
08/11/2015,005,MC,1

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy or dd-mm-yyyy format
Employee String 30 Employee Code/ID
LeaveCode String 20 Leave Code (Eg AL, MC, etc)
Unit FMTBcd 8 Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
UnitAsTime Time 0 In HH:MM format

Template.Tips-01.jpg It adviceable to use Unit instead of UnitAsTime as
  • UnitAsTime max value can enter is 23:59 only
  • if you Export as Grand Total

Employee

Example 1 - Employee

"Code";"Name";"DOB";"Gender";"Salary";"Resident";"EISTYPE";"EISCATEGORY"
"0018";"Shiro Emiya";"14/06/1980";"M";"1200";"F";"1";"0"
"0019";"Nanoha Takamachi";"01/10/1995";"F";"2000";"F";"0";"1"
"0020";"Belldandy";"01/09/1977";"F";"2500";"T";"1";"0"

Example 2 - Employee

CODE,NAME,NAME2,GENDER,DOB,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,PHONE1,PHONE2,MOBILE,EMAIL,NEWIC,OLDIC,PASSPORT,IMMIGRATIONNO,BRANCH,HRGROUP,DEPARTMENT,CATEGORY,PROJECT,JOB,TASK,COUNTRY,RACE,CALENDAR,JOBTITLE,JOINDATE,CONFIRMDATE,RESIGNDATE,MARITALSTATUS,ISACTIVE,WAGESTYPE,WAGES,CONTRIB,FREQUENCY,PAYMENTMETHOD,BANK,BANKACCNO,EPFNO,NK,INITIAL,SOCSONO,SOCSOTYPE,TAXBRANCH,TAXNO,DISABLED,RESIDENT,EASERIALNO,SPOUSENAME,SPOUSEADDRESS1,SPOUSEADDRESS2,SPOUSEADDRESS3,SPOUSEADDRESS4,SPOUSEPHONE1,SPOUSEPHONE2,SPOUSEMOBILE,SPOUSETAXBRANCH,SPOUSETAXNO,SPOUSEICNO,SPOUSEWORKING,SPOUSEDISABLED,LEAVEGROUP
001,LEE CHONG WAI,,M,12/12/1990,,,,,,,,lcw@gmail.com,901212014321,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,M,1,----,3000,----,----,MBB,CIBBMY,111265660,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
002,KOO KIAN KEAT,,M,01/01/1992,,,,,,,,,920101101001,,,,----,----,----,----,----,----,----,MY,----,----,,01/08/2012,01/11/2012,,S,1,----,2000,----,HALF,MBB,RHBBMY,56706890,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
003,NICOLE,,F,31/08/1994,,,,,,,,,940831105000,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,S,1,----,2800,----,----,MBB,MBBEMY,9048540654,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
004,WONG CHUN HAN,,M,21/06/1984,,,,,,,,,840621083003,,,,----,----,----,----,----,----,----,MY,----,----,,01/02/2012,02/05/2012,,M,1,----,3200,----,----,MBB,HLBBMY,5646506160,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----
005,YAP KIM HOCK,,M,28/06/1980,,,,,,,,,800628063333,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,M,1,----,5000,----,----,MBB,OCBCMY,58946514315,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----

Table Fields Properties
Field Name Remarks
Marital Status
  • S - Single
  • M - Married
  • D - Divorced
  • W - Widow
Socso Type
  • 1 - First Category
  • 2 - Second Category
  • 0 - No Socso
EIS Type
  • 0 - No EIS
  • 1 - Has EIS
EIS Category
  • 1 - Foreign workers
  • 2 - Public sector
  • 3 - Domestic servant
  • 4 - Age 57 to 59 (never contribute EIS before)
  • 5 - Employer with no employee
  • 6 - Self employed/sole proprietors
  • 7 - Spouse of employer
Bank Code Is Bank SWIFT Code
  • PHBMMY - Affin Bank Berhad
  • RJHIMY - Al Rajhi Banking & Investment Corporation (Malaysia) Berhad
  • MFBBMY - Alliance Bank Malaysia Berhad
  • ARBKMY - AmBank (M) Berhad
  • BIMBMY - Bank Islam Malaysia Berhad
  • BKRMMY - Bank Kerjasama Rakyat Malaysia Berhad
  • BMMBMY - Bank Muamalat Malaysia Berhad
  • BSNAMY - Bank Simpanan Nasional Berhad
  • CIBBMY - CIMB Bank Berhad
  • CITIMY - Citibank Berhad
  • DEUTMY - Deutsche Bank (Malaysia) Berhad
  • HLBBMY - Hong Leong Bank Berhad
  • HBMBMY - HSBC Bank Malaysia Berhad
  • ICBKMY - Industrial and Commercial Bank of China (Malaysia) Berhad
  • MBBEMY - Malayan Banking Berhad
  • OCBCMY - OCBC Bank (Malaysia) Berhad
  • PBBEMY - Public Bank Berhad
  • RHBBMY - RHB Bank Berhad
  • SCBLMY - Standard Chartered Bank Malaysia Berhad
  • SMBCMY - Sumitomo Mitsui Banking Corporation Malaysia Berhad
  • UOVBMY - United Overseas Bank (Malaysia) Berhad

Allowance & Deduction

Example - Allowance

"Date","Employee","Code","Unit"
26/6/2017,A0001,HP,4
28/6/2017,A0001,FUEL,9.63
26/6/2017,A0002,HP,6.30
26/6/2017,A0003,FUEL,4.92
26/6/2017,A0004,HP,1

Example - Deduction

"Date","Employee","Code","Unit"
26/6/2017,A0001,LATE,4
28/6/2017,A0001,LATE,9.63
26/6/2017,A0002,LATE,6.30
29/6/2017,A0002,LATE,3.37
26/6/2017,A0003,LATE,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy or dd-mm-yyyy format
Employee String 30 Employee Code/ID
Code String 20 Allowance or Deduction Code
Unit FMTBcd 8 Qty or Unit

Batch File Import

This function only Available in Version 1.2019.167.146 & above.
All file should be in CSV also but need to be in 1 zip file.
In Zip should had At least 2 file
  • Transaction File (eg pending.overtime.csv)
  • Hash File (eg pending.overtime.hash)

Overtime & Wages

Transaction Type File Name
Wages
  • pending.wages.csv
  • pending.wages.hash
Overtime
  • pending.overtime.csv
  • pending.overtime.hash
Example 1 - OT or Wages

TransDate,PostDate,Employee,Code,WorkUnitAsTime
26/6/2015,26/6/2015,A001,DR01,04:00
28/6/2015,28/6/2015,A001,DR02,09:38
26/6/2015,26/6/2015,A002,DR02,06:18
29/6/2015,29/6/2015,A002,DR03,03:22
26/6/2015,26/6/2015,A003,DR03,04:55

Example 2 - OT or Wages

"TransDate","PostDate","Employee","Code","WorkUnit"
26/6/2015,26/6/2015,A001,DR01,4
28/6/2015,28/6/2015,A001,DR02,9.63
26/6/2015,26/6/2015,A002,DR02,6.30
29/6/2015,29/6/2015,A002,DR03,3.37
26/6/2015,26/6/2015,A003,DR03,4.92

Table Fields Properties
Field Name Field Type Field Size Remarks
TransDate Date 0 In dd/mm/yyyy or dd-mm-yyyy format

Date of transaction

PostDate Date 0 In dd/mm/yyyy or dd-mm-yyyy format

Date of Process Date

Employee String 30 Employee Code/ID
Code String 20 Overtime or Wages Code
WorkUnit FMTBcd 8 Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
WorkUnitAsTime Time 0 In HH:MM format

Template.Tips-01.jpg Normally TransDate & PostDate will be same Date

Leave

Example 1 - Leave

TransDate,Employee,LeaveCode,UnitAsTime
08/11/2015,001,UL,08:00
19/11/2015,003,UL,09:20
15/11/2015,005,UL,08:00
01/11/2015,001,AL,08:00
03/11/2015,003,AL,08:00
08/11/2015,005,MC,08:00

Example 2 - Leave

TransDate,Employee,LeaveCode,Unit
08/11/2015,001,UL,1
19/11/2015,003,UL,1.17
15/11/2015,005,UL,1
01/11/2015,001,AL,1
03/11/2015,003,AL,1
08/11/2015,005,MC,1

Table Fields Properties
Field Name Field Type Field Size Remarks
Date Date 0 In dd/mm/yyyy or dd-mm-yyyy format
Employee String 30 Employee Code/ID
LeaveCode String 20 Leave Code (Eg AL, MC, etc)
Unit FMTBcd 8 Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
UnitAsTime Time 0 In HH:MM format

Template.Tips-01.jpg It adviceable to use Unit instead of UnitAsTime as
  • UnitAsTime max value can enter is 23:59 only
  • if you Export as Grand Total

Things To Consider

01. The target database must had Valid
- Employee Code
- OT Code
- Leave Code
- Wages Code
02. File extention must be in csv (e.g. OT.csv)
03. The data in "Net Figure" (eg. OT WorkUnitAsTime := Logout Time - Login Time)
04. There is no display format for Amount/Qty (eg 1600.12 not 1,600.12)
05. There is no extra spacing at the Begin & End of data (eg Tes1,Test2 not Tes1 , Tes2 ,)
06. The data type which is Boolean (0 = False & 1 = True)
07. Must 1 Transaction type 1 file (i.e. Overtime 1 file & Leave 1 file)
08. The output can be export out in Day or Time but not both in same time
09. Make sure only export out relevant data (eg if OT = 0 or No leave taken don't export out)


----------------------------------------------------------------------------------------------------------------------------------------------------
Template.Tips-01.jpg Notes

1. Output Can be in Tab, Semicolon or Comma.
2. Field can be added by just look in the field chooser in Pending Payroll.

----------------------------------------------------------------------------------------------------------------------------------------------------


  • Need assistance? May email to support@sql.com.my

See also