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.

• SQR Open Command
• SQR Read Command
• SQR Write Command

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

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.

SQR Process Definations

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

Record Type is SQL Table

After Successfully completed the process , Output File Looks like.

Process Output File

Running Select Statement on Staging Table

Querying the Staging Table

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