Ask the Experts - 97

Posted by Nitin Pai
Comments (4)
April 4th, 2009

I need some info..reg how to insert or update .csv /excel file values(Fields) e into a peoplesoft table…for eg..PS_JOB

any kind of process is ok..(APP Eng / SQR /or any method) send me the source code if possible

Thanks for posting your question!

A file layout can be created using the required table (i.e. PS_JOB), the file layout properties can be altered to suit the necessary validations and the sample source code can be obtained by dragging and dropping the created file layout in the PeopleCode window. This sample source code can then be altered to put in additional validations and process flows.


Rahul Seth

a) Create a file layout to read CSV/EXCEL file.
b) Use Application Engine
i) Read the File in Temp Table.
ii) Validate the data
iii) Insert/Update data into table.
iv) Based on temp table generate statistics as to how many rows updated/inserted
c) Some source code

File Read
rem *********************************;
rem * PeopleCode to Import Data from data file *;
rem *********************************;

Local Rowset &lrwsReq_chek;
Local File &FILE1;
Local Record &REC1;
Local Record &REC_TMP;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M, &cursorPos;
Local boolean &bTrue_False;
Local number &SEQ_ID;
Local string &sUrl, &sFilename, &sOprid, &sRunctlid;

SQLExec(“truncate table ps_sample_tmp”); SQLExec(“COMMIT”); SQLExec(“select runcntlid,oprid from psprcsrqst where prcsinstance=:1”, TEST_AET.PROCESS_INSTANCE.Value, &sRunctlid, &sOprid); SQLExec(“SELECT FILENAME, ATTACHSYSFILENAME FROM PS_Runcontrolrecord WHERE OPRID=:1 AND RUN_CNTL_ID=:2”, &sOprid, &sRunctlid, &sFilename, &sSysFilename);

&sUrl = GetURL(URL.SYSURL) | &sSysFilename;
&FILE1 = GetFile(&sUrl, “R”, “A”, %FilePath_Absolute); REM &FILE1 = GetFile(“/ps/PT8.43/appserv/prcs/crmdev/files/test_file.txt”, “R”, “A”, %FilePath_Absolute);

&RS1 = &FILE1.CreateRowset();
&RS1 = &FILE1.ReadRowset();
&REC_TMP = CreateRecord(Record.sample_TMP);

While &RS1 Null
&SEQ_ID = &SEQ_ID + 1;
&RS1 = &FILE1.ReadRowset();



Populate the valid rows.

Fetch data from temp tables and show in Message log.

Gautam Subhedar

Comments (4)

sinoj - July 21st, 2009

I am trying to use the "Create Reference Property" of component interface. But it is comming disable when i right clicked the CI. Can you please tell me the basic functionality of "Create Reference" for CI.

Sridhar - June 7th, 2009

Hi, I would like to know if there is any way to execute keyboard commands from SQR.For example execute ctrl+k from SQR. Thanks in advance

VR - May 26th, 2009

Hello, As per peoplesoft, there are four levels in level-based controls: Level 0, Level 1, Level 2, and Level 3; And the child data can be populated using Scroll bars, Scroll Areas and Grids... Now, I have a requirement say populating Employee's Dependents data which creates the need to go beyond Level-3... What's the best way to achieve it? Thanks in advance VR, Gurgaon

Nikit - April 25th, 2009

Hi, To avoid unique constrain SQL error while inserting into a table use selectbykey function sample code:- &REC_TMP.key1.Value = &key1; &REC_TMP.key2.Value = &key2; &REC_TMP.key3.Value = &key3; if not &REC_TMP.selectbykey() &REC_TMP.key1.Value = &key1; &REC_TMP.key2.Value = &key2; &REC_TMP.key3.Value = &key3; &REC_TMP.field1.Value = &field1; &REC_TMP.field2.Value = &field2; &REC_TMP.field.Value = &field3; &REC_TMP.insert(); end-if; Thanks, Nikit Murgude

Comments are closed.