This post is in response to Fred’s request on ToD. He had asked for an SQR program that would read from a flat file and update the values to a record.
You can download the flat file, the sqr file and the pdf file that it generates from the links provided.
We have taken care to make it as simple as possible while keeping all that was asked for. Let me know your questions through comments.
Here it comes!
BEGIN-REPORT PRINT 'REPORT OF UPDATED EMPLIDs' (1) CENTER PRINT 'EMPLID' (+1,1) OPEN 'D:\flat_file.txt' AS 1 FOR-READING RECORD=80:vary WHILE 1 READ 1 INTO $emplid:5 $stat:1 !reading the emplid and status from the flat file. IF #END-FILE BREAK END-IF !Selecting the status for the particular employee from the record. BEGIN-SELECT poc_stat &stat DO UPDATE-REC FROM ps_poc_rec WHERE emplid = $emplid END-SELECT END-WHILE END-REPORT !******************************************************************** !PROCEDURE: UPDATE-REC !Desc: This Procedure checks if there is a difference between the !status of the employee given in the file vis-a-vis the one in the !record. If there is a difference, this procedure updates the status !in the record with the one given in the file. !******************************************************************** BEGIN-PROCEDURE UPDATE-REC IF $stat <> &stat PRINT $emplid (+1,1) BEGIN-SQL UPDATE PS_POC_REC SET POC_STAT = $stat WHERE emplid = $emplid END-SQL END-IF END-PROCEDURE |
The record used in the above example.
Record used in the example
Hey Rakesh nice to see this gainin pace. Hope to see more and more request being serviced here;)
BTW, bring more guys for guest posts so that more querries can be answered.
Keep up the good work
Hi Rakesh
Thanks for this post for me. I have got what I precisely wanted!
Will getback with more requests…
Thanks again.
Fred
@ Vinod
Thanks…
We are actively thinking to bring in some guest bloggers soon.
@ Fred
You can always get back with more on ToD…
Rakeshsgk
Hello, I enjoy your article. This is a good site and I wanted to post a little note to let you know, good job! Thanks Meme
Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!
Hello, I admire your article. This is a informative site and I wanted to post a comment to tell you, nice job!
Thanks,
Tammy
I’m impressed!!! Really informative blog post here my friend. I just wanted to comment & say keep up the quality work. I’ve bookmarked your blog just now and I’ll be back to read more in the future my friend! Also nice colors on the layout, it’s really easy on the eyes.
Excelent work here. I’m new with SQR and it was too explicit. I just have a question:
in this line:
I’m perfectly clear on what it does but I’d like some explanatioon about the use of “record=80”
I’ve seen other examples and this value can change… what it does? What is it for?
Thanks a lot for this article again, it enlighten me a lot….
Regards…
@ Brain – Glad that you found this to be useful.
VARY specifies that the record can be of varying length.
RECORD=80 indicates that the max length of the record is 80. If a line being read is longer than this, the rest is ignored.
Hope that helps.
Perfect article at the right time for me!
But, when I’m trying to do the same with an XLS file as an input, special characters are getting read by strings that I’ve defined,
Are there any minor changes to be done/followed when working with an xls file instead of txt file as shown in example?
Please help.
Thank you.
@ Kranthi – Nice the know that the post was helpful.
It would be too complex for an SQR to read an XLS file.
On the other hand, using a CSV file and unstring command would be easy.
Is there any specific reason why you particular about the file type, i.e; XSL?
how does this work?
when you’re trying to update shouldn’t emplid be #emplid? in your example it’s a string and when I try to update a line use a string the update doesn’t take.
UPDATE PS_POC_REC SET POC_STAT = $stat WHERE emplid = $emplid
Hi Rakesh. Would you happen to know the maximum record length the SQR OPEN statement can handle? Is it still 32767? Thanks.
Regards,
Vincent
Hi Vincent,
You are right.
The max length is still 32767.
I just tried the below statement on 8.52 and it failed.
This is the error I got.
Error on line 32:
(SQR 2902) OPEN missing required qualifiers:
RECORD={rec_len} FOR-READING|FOR-WRITING|FOR-APPEND
Errors were found in the program file.
SQR for PeopleSoft: Program Aborting.
Hello, I’m not familiar with SQR and Application Engine. Today my boss asked me to update data from AE and automatically update into my SQR page.. So how to do that? please help me.. :'(
Hi Alba,
I didn’t quite understand your requirement.
Can you please detail out what you mean by the below statement?
“update data from AE and automatically update into my SQR page”
I’m new to SQR and This is an excellent article for reading data from a file.
I have a question:
My file has 6 columns. I would like to read only 5 columns and ignore the last 6th column.
Can I write the read statement just for the 5 columns and ignore the 6th column?
READ 1 INTO $col1:5 $col2:5 $col3:5 $col4:5 $col5:5
OR is it compulsory to read all the columns into variables?
Glad that you found this useful.
To answer your questions, it’s not compulsory to read the entire row / record into variables. You are free to read whatever is required.
However, keep in mind that the total lenghts of all these variables put together must be less than or equal to the length of the record.
Thank you very much for the quick response !
Glad to have helped!
I GOT ERROR
(SQR 3211) #Numeric variables and literals must have :length of 1, 2 or
4
bytes.
@Sruthi – You might have added a space between the variable name and the length. Can you recheck this bit?
It should be $emplid:5 $stat:1
NOT $emplid : 5 $stat : 1
Hi Rakesh,
I am facing a problem when trying to load the data which is zero.I m loading PO line data through the fixed length data extraction which you said above, but when the PO Qty is zero from that line onwards, the data is not inserted into the staging records. The process is going to success.
In the debugging I found that the data extraction alignment is disturbed from the row which has the PO Qty zero.
What can be the reason for this? In SQR Can we make the file read pointer to zero when it reads new lines.
Please suggest.
Hi Poornima,
When you say that “the data extraction alignment is disturbed”, do you mean that the alignment of data in the input file is disturbed?
If yes then, that might be the cause of the issue.
Hi Rakesh,
The above provided information is really helpful in understanding ‘how SQR reads file’. Really appreciate your expertise.
Can you please let me know the meaning for various status code (Error code) while opening a .txt file for reading via SQR.
I am receiving status ‘-1’.
Line of code from your example:
open ‘test.txt’ as 1 for-reading record=80:vary Status=#InputFileStatus
Show #InputFileStatus
For my code, #InputFileStatus is coming as -1.
I would love to get information corresponding to various Status.
Thanks in advance.
Hi Dev,
If Open is successful, it returns 0.
If it fails it returns -1
Hi Rakesh,
Hope you have an answer to my problem.
1. Read a directory to generate a list of files and create a file list
2. Open the file list and loop through the file list to open each file in the list
3. Read the contents of all the files and generate a PDF report
My code looks like this:
===========================
======================
The issue is when I execute this code, it is going into an infinite loop. Is there a different way to check the end of file?
Thanks for any pointers you can provide
-Sri
Hey Sri,
First thing first – Make sure that the outer loop looping through the files (which you haven’t shown here) is not causing the infinite loop.
If that’s not the case, proceed onto rest of the reply.
I’ve always used a loop like the one below and it has worked every time.
If it doesn’t work for you, try doing the following:
1. Print the $readline variable within the loop. What’s the output like?
2. If your files have a footer, try using some character format from the footer as a condition to break the while loop.
Let me know how it goes.
regards
Rakesh
how can I read a list of PDFs from a folder on the server and then manipulate the file name and then write back to server?
@Mark
Considering that you’ve commented on an SQR related post, I would provide an approach to do this in SQRs.
Within an SQR, I would first create a Unix command to list all pdf files in the folder into a temporary file.
Then use the ‘call system’ to execute this command.
Now, we have the filenames in our temporary file.
Within the SQR open the temporary file, loop through the lines (pdf filenames).
After each filename is read, use another Unix command to move the file with a new name.
Cheers
Rakesh
Thank you for that. What do you mean by the “Call System”?
Call System is the SQR command for issuing an OS command.
Examples of this are provided in the SQR Commands section of SQR Language reference PeopleBooks.
Hi Rakesh, Not to be a pain but does this work on NT as well? I’m getting error:
Error on line 31:
(SQR 4009) Function ‘\\XXXXX\data\’ missing parentheses.
Hi Mark,
That command doesn’t work on NT but the approach sure does.
For Windows, use the dir command instead of ls.
The following will list all pdf files in the present directory into a new file named temp_pdf_listing.txt
Cheers
I did get it to work, thanks for your advice!!
Glad to know.
Happy to help!
Hi Rakesh,
I have a question on this line. OPEN ‘D:\flat_file.txt’ AS 1 FOR-READING RECORD=80:vary
is the argument RECORD is mandatory to mention ?like RECORD=80:vary here. does it work if i don’t mention it.
Hi Ajay
Yes, it is mandatory.
But you may use fixed, vary, or fixed_nolf.
Hi Rakesh
Thanks Alot for this very helpful article. I Have one question here
1. Is it Possible to read data from .DAT file using SQR?