Import Data: Difference between revisions

From eStream Software
Line 211: Line 211:
29/6/2017,A0002,LATE,3.37<br />
29/6/2017,A0002,LATE,3.37<br />
26/6/2017,A0003,LATE,4.92<br />
26/6/2017,A0003,LATE,4.92<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
|-
| Code || String || align="center" |20 || Deduction Code
|-
| Unit || FMTBcd|| align="center" |8 || Qty or Unit
|}
|}



Revision as of 09:18, 5 April 2018

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

SQL Payroll Program

May get it from here
MD5 : 9815E0A416F0BE80B1986B5689392A36

Modules Require

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

Guideline & Example

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

Overtime

Example 1 - OT

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

"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 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"
"0018";"Shiro Emiya";"14/06/1980";"M";"1200"
"0019";"Nanoha Takamachi";"01/10/1995";"F";"2000"
"0020";"Belldandy";"01/09/1977";"F";"2500"

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

Deduction

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 Deduction Code
Unit FMTBcd 8 Qty or Unit

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