Categories
SQR

Read File Using SQR and Insert data into Table PeopleSoft

I was working on SQR code that will read CSV file with SQR Code and Insert data into Staging Table.This blog post will help you to understand How to do Read file with SQR.I hope you are aware of SQR File Reading , Writing & open commands, if you are not then please read the Hyperion SQR Command on Oracle Docs.Here is the Links ( Opens in New Window) for you.

So Lets look the CSV file along with structure,columns etc.

2013_11_04_01
CSV File Structure

Here is the Full SQR Code

I have also attached the Sample CSV file & Full SQR Code

 !-----------------------------------------------------------------------------------------------!
!  Report Name:  FILEREAD.SQR -- Run Via Process Schedular SQR Process (API Aware)              !
!  Report Descriptions: This SQR Program will read file data & insert into staging table         !
!  Report Create Date: 29-October-2013                                                             !
!  Report Run Component: PRCSMULTI                                                                !
!-----------------------------------------------------------------------------------------------!

#include 'setenv.sqc'      !Set environment

!******************
Begin-Program
!******************
     do Init_Report
     do Main_Report
     do Stdapi-Term
end-program

!****************************
Begin-Procedure Init_Report
!****************************
     Do Stdapi-Init
     Do Truncate_Tables
end-procedure

!***************************
Begin-Procedure Main_Report
!***************************
    Do Process_Input_File

End-Procedure

!**********************************
Begin-Procedure Process_Input_File
!**********************************
    !Set the File Path
    Let $OpenFileName = 'E:\AppEngineFiles\GBI_29October2013.csv' !Please Change the File Path
    Open $OpenFileName As 1 For-Reading Record=3000:Vary Status=#FileStatus
        !Checking For File Status
        If #FileStatus != 0
            display $OpenFileName noline
            display 'Open Failed'
        Else 
            !Read Input File
            Do Read_Input_File
        End-If
End-Procedure

!***************************************
Begin-Procedure Read_Input_File
!***************************************
#debug show 'Entering Read_Input_File'

    !First We need to skip the headings
    Read 1 into $Record:3000

    Move 0 to #Total_Record
    Move 0 to #Total_Inserted_Record

    While Not #end-file
        Read 1 into $Input:3000 Status=#Read_stat

        If #end-file !When End-File then Break
            Break
        End-If

        Add 1 to #Total_Record     
        If #Read_Stat <> 0 !SQR returns zero if the read is successful
                #debug show 'Bad return from the Read command, errno= ' #Read_Stat
                #debug show ' Record # = ' #Total_Record
                #debug show 'Could not read record # ' #Total_Record
        Else
                Add 1 to #Recs_Rej
                !#debug show 'Calling Process-Input-Record to process record # ' #Total_Record
                Do Process_Input_Record 
        End-If

    End-While
    !At this point all records are read from the file
    Close 1
    Let $Total_Rec_Inserted = to_char(#Total_Inserted_Record)
    #debug show 'Total records inserted:  ' $Total_Rec_Inserted
End-Procedure

!************************************
Begin-Procedure Process_Input_Record
!************************************
    !We are doing Unstring
    Unstring $Input By ',' Into $Emplid $Emplname $Country $NidDescr $Nid $primaryNid 
    Do Insert_Data_Into_Tmp_Table
End-Procedure

!*******************************
Begin-Procedure Truncate_Tables
!*******************************
    !Before Insertion, We need to Truncate The Staging Table
    Let $TruncateCommand = '{PTTRUNCATE}'
    BEGIN-SQL  On-Error=SQL-Error
            [$TruncateCommand] PS_UD_TMP_DATA
    END-SQL
End-Procedure

!*****************************************
Begin-Procedure Insert_Data_Into_Tmp_Table
!*****************************************

Begin-SQL On-Error=Insert_Error !Using Insert Error Procedure ( Duplicate Check )

    !When your program finishes without errors, Commitis performed by SQR automatically.

    Insert Into PS_UD_TMP_DATA 
    ( EMPLID,EMPLNAME,COUNTRY,DESCR,NATIONAL_ID,PRIMARY_NID )
    Values
    ( $Emplid,$Emplname,$Country,$NidDescr,$Nid,$primaryNid )

End-Sql

    If #sql-count=1
        Add 1 to #Total_Inserted_Record
    Else
        #debug  Show 'No rows inserted'
    End-If

End-Procedure

!****************************
Begin-Procedure Insert_Error
!****************************
    If #sql-status = -9 ! if duplicate (this code is for ORACLE only)
        #debug Show 'Insert Error: Duplicate row is not allowed for emplid=' $Emplid
    Else
        #debug Show 'Insert Error: ' $sql-error
        #debug Show 'Error number: ' #sql-status
        Stop ! Halt Program and Rollback 
    End-If
End-Procedure

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
!***********************************************************************

I’m not explain the full code, i request you to read the code carefully hope you will be understand it easily.

For Process Definitions i have made Process Type: SQR Process
SQR Process Definations
SQR Process Definations
Debug
Debug

My Staging Table Looks like

 CREATE TABLE PS_UD_TMP_DATA (EMPLID VARCHAR2(11) NOT NULL,
   EMPLNAME VARCHAR2(50) NOT NULL,
   COUNTRY VARCHAR2(3) NOT NULL,
   DESCR VARCHAR2(30) NOT NULL,
   NATIONAL_ID VARCHAR2(20) NOT NULL,
   PRIMARY_NID VARCHAR2(1) NOT NULL) TABLESPACE HRLARGE STORAGE
 (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0)
 PCTFREE 10 PCTUSED 80
/
SQL Record App Designer View
SQL Record App Designer View
Record Type is SQL Table

After Successfully completed the process , Output File Looks like.

Process Output File
Process Output File

Running Select Statement on Staging Table

Querying the Staging Table
Querying the Staging Table

Please let me know your feedback and if you think it can be improved by your valuable suggestions.

Download The Full Code
FILEREAD SQR Code
Sample CSV File

5 replies on “Read File Using SQR and Insert data into Table PeopleSoft”

This worked like a charm! Step by step, with code, images… very detailed, indeed.
10/10.
Thank you very much for this post! Now I’ll be able to get out early today and go eat at Hooters, or something…

-Josh

Leave a Reply

Your email address will not be published. Required fields are marked *