[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
SQR Issue - Can I read a Excel Flat File into SQR Variables? -Reply
- Subject: SQR Issue - Can I read a Excel Flat File into SQR Variables? -Reply
- From: David Anderson <DANDERS1@SMTPGW.IS.HFH.EDU>
- Date: Fri, 8 Jan 1999 18:39:16 -0500
Here is some code I wrote to attack that very problem. See the
attachment and look for the procedure Check-For-Quotes. This is not the
best, but it does work!
Dave
!******************************************************************************
! CSV2TXT.sqr
! Converts CSV file to fixed length TXT file for PS Import.
! Creates Short_Description, based on first ten characters of description,
! if absent. Gives the user the option to WRITE an new file or APPEND an
! existing file.
!
! ASSUMPTIONS:
!
! INPUT:
! SETUP defines CSV filename (through input command), Path, and TXT filename.
!
! OUTPUT:
! SETUP defines Path and TXT filename.
!
! AUTHOR:
! Computer Management Sciences, Inc (CMSI) 248 262-1495
! David Anderson (DCA) May 1998
!
! MODIFICATIONS:
! Company/Name Initials Phone#
!
!******************************************************************************
! CONFIDENTIALITY INFORMATION
! Property of Henry Ford Health System
!******************************************************************************
!
! MODIFICATION HISTORY:
! Date Mod# Request# Initials Description of Modification
!
!******************************************************************************
#include 'setenv.sqc' !Determines environment SQR executes in
!******************************************************************************
BEGIN-SETUP
!******************************************************************************
#define $Path 'm:\jobcod~1\'
End-Setup
!******************************************************************************
BEGIN-REPORT
#debug show 'Begin Report'
!******************************************************************************
do Init-DateTime
do Init-Number
do Get-Current-DateTime
do Init-Report
let #FileCounter = 1
let $FileCounter = edit(#FileCounter, '009')
do Process-Main
do Reset
do Get-Current-DateTime()
move $AsOfNow to $Ended
show ' '
show $ReportTitle ', ' $ReportID ' completed at ' $Ended '.'
END-REPORT
!******************************************************************************
BEGIN-PROCEDURE INIT-REPORT
#debug show 'Init Report'
!******************************************************************************
do Init-DateTime
do Get-Current-DateTime()
move $AsOfNow to $Began
Get_FileName:
input $FileName maxlen=8 'Filename (without extension) to be converted' status=#istat
if #istat !=0
show 'Please reenter filename (without extension) with less than 9 chars'
goto Get_FileName
end-if
move 'CSV to TXT Conversion' to $ReportTitle
move $FileName to $ReportID
show ' '
show $ReportTitle ' began at ' $Began
show ' '
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE PROCESS-MAIN
#debug show 'BEGIN-PROCEDURE PROCESS-MAIN'
!******************************************************************************
do Open-Files
do Read-File
do Close-Files
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE READ-FILE
#debug show 'BEGIN PROCEDURE READ-FILES'
!******************************************************************************
while 1
read 1 into $RawData:250
if #End-File
show 'Lines Read ' #RecordCount
Break
else
add 1 to #RecordCount
add 1 to #RowCount
do CHECK-FOR-QUOTES
do EXTRACT-DATA
do FIX-DEFAULTS
if #RecordCount != 1
if #RowCount >= 1000
add 1 to #FileCounter
let $FileCounter = edit(#FileCounter, '009')
move 0 to #RowCount
close 2
let $File = 'Jobcd' || $FileCounter
let $OutputFile = {$Path} || $File || '.txt'
open $OutputFile as 2 for-writing record=155
end-if
do Write-File
end-if
end-if
end-while
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE CHECK-FOR-QUOTES
#debug show 'BEGIN PROCEDURE CHECK-FOR-QUOTES'
#debug show #RecordCount ' ' $RawData
!******************************************************************************
move '' to $RefinedData
let #LineLength = length($RawData)
move 0 to #Position
move 0 to #QuotesFound
while #Position <= #LineLength
extract $Value from $RawData #Position 1
if #QuotesFound = 0
if $Value = '"'
move 1 to #QuotesFound
add 1 to #Position
else
let $RefinedData = $RefinedData || $Value
add 1 to #Position
end-if
else
evaluate $Value
when = ','
let $RefinedData = $RefinedData || '-'
add 1 to #Position
when = '"'
move 0 to #QuotesFound
add 1 to #Position
when-other
let $RefinedData = $RefinedData || $Value
add 1 to #Position
end-evaluate
end-if
end-while
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE EXTRACT-DATA
#debug show 'BEGIN PROCEDURE EXTRACT-DATA > '
#debug show $RefinedData
#debug show #Position ' ' #LineLength
!******************************************************************************
unstring $RefinedData by ',' into
$L1L2
$DBS_CODE
$PS_CODE
$EFFECT_DATE
$STATUS
$DESCRIPTION
$SHORT_DESCRIPTION
$SALARY_PLAN
$SALARY_GRADE
$SALARY_STEP
$TARGET_INCENTIVE
$MANAGER_LEVEL
$UNION_CODE
$WORKERS_COMP
$JOB_FAMILY
$JOB_SUB_FAMILY
$FLSA_STATUS
$EEO-1_CTGRY
$JOB_GROUP
$STANDARD_OCC_CTGY
$ACCT_CODE
$REVIEW_GROUP
$HF_OT_CD
#debugD show 'BEGIN PROCEDURE EXTRACT-DATA > '
#debugD show ' $L1L2 ' $L1L2
#debugD show ' $DBS_CODE ' $DBS_CODE
#debugD show ' $PS_CODE ' $PS_CODE
#debugD show ' $EFFECT_DATE ' $EFFECT_DATE
#debugD show ' $STATUS ' $STATUS
#debugD show ' $DESCRIPTION ' $DESCRIPTION
#debugD show ' $SHORT_DESCRIPTION ' $SHORT_DESCRIPTION
#debugD show ' $SALARY_PLAN ' $SALARY_PLAN
#debugD show ' $SALARY_GRADE ' $SALARY_GRADE
#debugD show ' $SALARY_STEP ' $SALARY_STEP
#debugD show ' $TARGET_INCENTIVE ' $TARGET_INCENTIVE
#debugD show ' $MANAGER_LEVEL ' $MANAGER_LEVEL
#debugD show ' $UNION_CODE ' $UNION_CODE
#debugD show ' $WORKERS_COMP ' $WORKERS_COMP
#debugD show ' $JOB_FAMILY ' $JOB_FAMILY
#debugD show ' $JOB_SUB_FAMILY ' $JOB_SUB_FAMILY
#debugD show ' $FLSA_STATUS ' $FLSA_STATUS
#debugD show ' $EEO-1_CTGRY ' $EEO-1_CTGRY
#debugD show ' $JOB_GROUP ' $JOB_GROUP
#debugD show ' $STANDARD_OCC_CTGY ' $STANDARD_OCC_CTGY
#debugD show ' $ACCT_CODE ' $ACCT_CODE
#debugD show ' $REVIEW_GROUP ' $REVIEW_GROUP
#debugD show ' $HF_OT_CD ' $HF_OT_CD
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE FIX-DEFAULTS
#debug show 'BEGIN PROCEDURE FIX-DEFAULTS'
!******************************************************************************
let $JOB_GROUP = lpad($JOB_GROUP, 2, '0')
if $JOB_GROUP = '00'
move '99' to $JOB_GROUP
end-if
if ltrim(rtrim($SHORT_DESCRIPTION, ' '), ' ') = ''
let $SHORT_DESCRIPTION = substr($DESCRIPTION,1,10)
end-if
let $UNION_CODE = lpad($UNION_CODE, 3, '0')
if $UNION_CODE = '000'
move '' to $UNION_CODE
end-if
move '' to $STANDARD_OCC_CTGY !per Angela Keown 6/17
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE WRITE-FILE
#debug show 'BEGIN PROCEDURE WRITE-FILE'
!******************************************************************************
write 2 from
$PS_CODE:6 !001
'19150101' !007
'A' !015
$DESCRIPTION:30 !016
$SHORT_DESCRIPTION:10 !046
! DescrLong
$SALARY_PLAN:3 !056
$SALARY_GRADE:3 !059
$SALARY_STEP:2 !062
'9 ' !064
! Survey Salary
! Survey Job Code
$UNION_CODE:3 !066
! Retro Rate
! Retro Percent
'USD' !069
'4000' !072
'H' !076
$WORKERS_COMP:4 !077
$JOB_FAMILY:6 !081
! Knowhow Points
! Accntab Points
! Probslv Points
! Job Points Total
! Knowhow Pct
! Accntab Pct
! Probslv Pct
! Trn Program
! Med Checkup
$FLSA_STATUS:1 !087
$EEO-1_CTGRY:1 !088
! EEO4
! EEO5
! EEO6
$JOB_GROUP:2 !089
$STANDARD_OCC_CTGY:4 !091
! Can Soc Cd
! Can Occ Cat
! Svc Pub
! Personal
! Suprvsry
! Assignment Geoloc
! Can Job Cls
! Can NOC Cd
! Seasonal
! Functional Cd
! BA Cd
! Technical
! Emp Cat FRA
! Activity Type FRA
! INSEE Cd
$ACCT_CODE:25 !095
! HF Target Pct
$JOB_SUB_FAMILY:6 !120
$L1L2:6 !126
$DBS_Code:6 !132
$REVIEW_GROUP:3 !128
$HF_OT_CD:1 !Hard coded for now. Will have to change.
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE OPEN-FILES
#debug show 'BEGIN PROCEDURE OPEN-FILES'
!******************************************************************************
let $File = 'Jobcd' || $FileCounter
let $FileName = 'PPH_COMBINED'
let $InputFile = {$Path} || $FileName || '.csv'
let $OutputFile = {$Path} || $File || '.txt'
!let $OutputFile = {$Path} || $FileName || '.txt'
show '$InputFile = ' $InputFile ' $OutputFile = ' $OutputFile
open $InputFile as 1 for-reading record=250
open $OutputFile as 2 for-writing record=155
show ' '
show 'I M P O R T A N T'
show 'Converting ' $InputFile ' to ' $OutputFile
END-PROCEDURE
!******************************************************************************
BEGIN-PROCEDURE CLOSE-FILES
#debug show 'BEGIN PROCEDURE CLOSE-FILES'
!******************************************************************************
close 1
close 2
END-PROCEDURE
!******************************************************************************
! INCLUDE FILES
!******************************************************************************
!Required Process Scheduler SQCs
#include 'stdapi.sqc' !Process Scheduler Interface
#include 'askaod.sqc' !Get-As-Of-Date
!#include 'payrnctl.sqc' !Select-Parameters PAYROLL
!#include 'hrrnctl1.sqc' !Select-Parameters HR
!Other SQCs
#include 'curdttim.sqc' !Procedures to retrieve current system date/time
#include 'datemath.sqc' !SQR date arithmetic procedures
#include 'datetime.sqc' !Routines for date/time formatting
#include 'number.sqc' !Routines for number formatting
#include 'reset.sqc' !Resets printer and indicates end of report
#include 'getcodta.sqc' !Get-Company-Data
#include 'getpgdta.sqc' !Get-PayGroup-Data
#include 'geternnm.sqc' !Get-Earnings-Name from the Earnings Table
#include 'getdednm.sqc' !Get-Deduction-Name from the Deductions Table
#include 'getstdta.sqc' !Get selected fields from the State Tax Tables
#include 'getlcdta.sqc' !Get selected fields from the Local Tax Tables
#include 'timemath.sqc' !SQR time arithmetic procedures
#include 'readxlat.sqc' !Read the Translate Table for the desired values