Re: Reading Flat Files from SQR

[Wendy Stern <wstern@NUI.COM>]

I do it with payroll every week.  Timekeepers enter the time on our
mainframe I export the file and read it in an sqr to load a table.

move 'Y:\PSTE\TIMEENT' to $filename
  !move 'c:\TEMP\TIMEENT' to $filename
  concat $run with $filename
  concat '.DAT' with $filename
  open $filename as 1
      for-reading
      record=91:fixed
      status = #open1
  if #open1 = -1
    display 'Open for ' noline
    display $filename  noline
    display ' FAILED'
    stop
  end-if
  do Read-Data
end-procedure

!***********************************************************************
! This procedure reads the data, and calls the routine to read the data.
!***********************************************************************
begin-procedure READ-DATA
  while 1 = 1
    read 1 into $loadrecord:91
    if #end-file = 1
      break !exit the "while"
    end-if
    do Process-Data
  end-while
end-procedure

!***********************************************************************
! This procedure processes the input record, reformats the data and
! calls a routine to check the company paygroup is valid for the run_id,
! and calls the routine to insert the data to NUI_TIMEENT_WRK.
!***********************************************************************
begin-procedure PROCESS-DATA
  add 1 to #in_count
  let $company = substr($loadrecord,1,3)
  let $company = rtrim($company,' ')
  if $company = ''
    move ' ' to $company
  end-if
  let $paygroup = substr($loadrecord,4,3)
  let $paygroup = rtrim($paygroup,' ')
  if $paygroup = ''
    move ' ' to $paygroup
  end-if
  let $emplid = substr($loadrecord,7,10)
  let $emplid = ltrim($emplid,'0')
  let $reg_hrs = substr($loadrecord,17,6)
  let #reg_hrs = to_number($reg_hrs)
  multiply .01 times #reg_hrs
  let $account = substr($loadrecord,23,6)
  let $account = rtrim($account,' ')
  if $account = ''
    move ' ' to $account
  end-if
  let $date = substr($loadrecord,29,8)
  do Format-Datetime($date,$dateout,{DEFCMP},'','native')
  let $date1 = substr($dateout,1,7)
  let $date2 = substr($dateout,10,2)
  move $date1 to $dateout
  concat $date2 with $dateout
  let $shift = substr($loadrecord,37,1)
  let $shift = rtrim($shift,' ')
  if $shift = ''
    move '1' to $shift
  end-if
  let $units = substr($loadrecord,38,4)
  let #units = to_number($units)
  let $erncd = substr($loadrecord,42,3)
  let $erncd = rtrim($erncd,' ')
  if $erncd = ''
    move ' ' to $erncd
  end-if
  let $otcd = substr($loadrecord,45,2)
  let #otcd = to_number($otcd)
  multiply .1 times #otcd
  let $othrs = substr($loadrecord,47,6)
  let #othrs = to_number($othrs)
  multiply .01 times #othrs
  let $spec_pay = substr($loadrecord,53,8)
  let #spec_pay = to_number($spec_pay)
  multiply .01 times #spec_pay
  let $rate_pay = substr($loadrecord,61,8)
  let #rate_pay = to_number($rate_pay)
  multiply .01 times #rate_pay
  let $rc = substr($loadrecord,69,3)
  let $rc = rtrim($rc,' ')
  if $rc = ''
    move ' ' to $rc
  end-if
  let $project_id = substr($loadrecord,72,8)
  let $project_id = rtrim($project_id,' ')
  if $project_id = ''
    move ' ' to $project_id
  end-if
  let $activity_id = substr($loadrecord,80,8)
  let $activity_id = rtrim($activity_id,' ')
  if $activity_id = ''
    move ' ' to $activity_id
  end-if
  let $password = substr($loadrecord,88,4)
  let $password = rtrim($password,' ')
  if $password = ''
    move ' ' to $password
  end-if
  do Validate-Company
  if $found = 'N'
    display 'Invalid Company Paygroup Combination'
    display $loadrecord
    add 1 to #reject_count
  else
    do Insert-Nui-Timeent-Wrk
  end-if
end-procedure

!***********************************************************************
! This procedure inserts the data to NUI_TIMEENT_WRK.
!***********************************************************************
begin-procedure INSERT-NUI-TIMEENT-WRK
  move 'N' to $insert_error
begin-sql on-error=insert-error
insert into PS_NUI_TIMEENT_WRK (
  PAY_END_DT,
  COMPANY,
  PAYGROUP,
  EMPLID,
  REG_HRS,
  GL_PAY_TYPE,
  ASOFDATE,
  SHIFT,
  STATISTIC_AMOUNT,
  ERNCD,
  FACTOR_MULT,
  OT_HRS,
  OTH_EARNS,
  HOURLY_RT,
  DEPTID,
  PROJECT_ID,
  ACTIVITY_ID,
  PROCESS_FLAG)
values (
  &A.PAY_END_DT,
  $company,
  $paygroup,
  $emplid,
  #reg_hrs,
  $account,
  $dateout,
  $shift,
  $units,
  $erncd,
  #otcd,
  #othrs,
  #spec_pay,
  #rate_pay,
  $rc,
  $project_id,
  $activity_id,
  'N')
end-sql
  if $insert_error = 'N'
    add 1 to #out_count
  else
    add 1 to #error_count
    move 'N' to $insert_error
  end-if
end-procedure


> ----------
> From:         Srinivasan S[SMTP:srinivasan.seetharaman@DB.COM]
> Sent:         Thursday, July 09, 1998 1:29 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Reading Flat Files from SQR
>
> Hi all,
>      Is it possible to read a flat file(ASCII) file from SQR. We have
> a
> requirement where in , we would have to read a flat file and populate
> a
> table.
>
> TIA,
>
> Srini
>