You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

SQR Code for converting the data from .CSV into Peoplesoft Tables

2 replies [Last post]
ananth_noone's picture
User offline. Last seen 10 years 51 weeks ago. Offline
Joined: 03/08/2010
Posts: 1

Hello All,

First time i am going to write Conversion Program using SQR to load data from .CSV to Peoplesoft Tables.

I have two question.

1- What is the best file format to load the data into Peoplesoft.

2- Can any body post SQR code for loading data from .csv or any format to load into Peoplesoft tables using SQR ,if anybody have written the code.

I would really appreciate for the help.

Thanks
Ananth

Aravind's picture
Aravind (not verified)
Re: SQR Code for converting the data from .CSV into ...

- Data in CSV file can be easily read by Application engine - code is also generated by Peopltools

Arjun R's picture
Arjun R (not verified)
Re: SQR Code for converting the data from .CSV into ...


!INCLUDE FILES
!--------------------------------------------------------------------------------
!#include 'setenv.sqc' ! Set environment
!#include 'stdapi.sqc' !Routines to update run status
!#Include 'datetime.sqc' !Routines for date and time
!-----------------
Begin-program
!-----------------
!DO STDAPI-INIT
do ToGetFile
!DO STDTERM-TERM
!-----------------
End-program
!-----------------
begin-procedure ToGetFile
Open 'arjun1.csv' as 1 for-reading record=100:vary
if #filestatus != 0
Show 'File Not Opend'
else
do ToReadFile
show 'file opened'
Close 1
end-if

end-procedure !End Procedure ToGetFile

Begin-procedure ToReadFile ! reading file and printing in .spf file

while not #end-file
read 1 into $inputvalues:150
if #end-file
break
end-if
unstring $inputvalues by ',' into $comp_code $emp_id $fname $lname $ssn $gender $dob
PRINT $comp_code (+1,1)
show $comp_code
print $emp_id (,+4)
! let $emp_id = rtrim (ltrim ($emp_id,'"'),'"')
show $emp_id
print $fname (, +3)
show $fname
show $lname
let $fname1 = $fname || $lname
show $fname1
let $fname1 = rtrim (ltrim ($fname1,'"'),'"')
print $ssn (,+2)
show $ssn
print $gender (,+2)
show $gender
show $dob
!move $dob to $dob1 'mon-dd-yyyy'
do printing

end-while
end-procedure

begin-procedure printing ! inserting data to database
begin-sql
insert into
PS_SI_RMK_EMP_REC (
SI_RMK_COMP_CODE,
SI_RMK_EMP_ID ,
SI_RMK_FNAME,
SI_RMK_SSN,
SI_RMK_GENDER,
SI_RMK_DOB)
values
($comp_code,
$emp_id,
$fname1,
$ssn,
$gender,
$dob)
end-sql
End-procedure