Replies to This Discussion

Permalink Reply by Jim Marion on January 19, 2009 at 10:25pm

Hi Chuck. I have done this using Apache POI, the PeopleSoft file attachment API, app engine, and Java. I use the file attachment API to provide users with the ability to upload files (see the AddAttachment function in PeopleBooks). I then use an app engine to run some PeopleCode. That PeopleCode uses GetAttachment to move the file from the database into a temp file. I use the Java object java.io.File to get a temp file name. Next, the PeopleCode runs some Java that uses POI to read the spreadsheet row by row, executing a SQLExec for each row. I wish I had an example for you, but I don't. I documented SQLExec usage from Java in my post Accessing the PeopleSoft Database in Java. I know the details here are quite sketchy, but hopefully this gives you some ideas.

• ▶ Reply

Permalink Reply by Chuck on January 20, 2009 at 6:47am

Is there also a method of doing this by reading a .csv file?

• ▶ Reply

Permalink Reply by Jim Marion on January 20, 2009 at 11:49am

Yes, you can use CSV, but that requires the user to save the Excel file in CSV format. When saving Excel files to CSV, Excel does not quote number columns stored as text. For example, if you have an identifier like PROJECT_ID that has leading zeros, Excel will not quote this identifier. This is not a problem with PeopleSoft. The problem is comes when a user tries to open the CSV file in Excel. At this point, Excel will treat the unquoted number column as a number and will strip the leading zeros, corrupting the identifier.

If you use PeopleSoft to read CSV, the file attachment piece is the same. I would still use a Java file object to get a temp file name. You can then use the PeopleCode File object to process your file.

• ▶ Reply

Permalink Reply by Chuck on January 20, 2009 at 12:18pm

Jim,
Thanks. I have created a push button. How do I have a dialog box that allows a user to select a file from their file system? I can't find any information in Peoplebooks.

• ▶ Reply

Permalink Reply by Jim Marion on January 20, 2009 at 2:10pm

Did you look up the AddAttachment function in the PeopleCode Language Reference PeopleBook? From there, you will find a link titled "Using File Attachments in Applications." This link provides all of the delivered documentation for file attachments. Using this documentation, it is possible to stumble through a file attachment.

• ▶ Reply

Permalink Reply by DowntownChicago on July 16, 2009 at 7:29pm

Hi Jim,

I review AddAttachment but I don't understand where the file go. After I click add, it doesn't give me the error, but I don't see in the server. According to the tracefile, it's in d:\temp\PSFTP\SADEV90_37851\25288\file_a.csv But I don't see it.

I want to put the file in d:\temp Is AddAttachment enough? or I need to use putAttachment and/or GetAttachment ? Can it be right after AddAttachment()? Or they have to be called from App Engine.

Thank you.

• ▶ Reply

Permalink Reply by Ramesh Vankayala on January 21, 2009 at 12:13pm

Chuck,

Take a look at the App engine: GL_JRNL_IMP (GL Flat File Journal Import) in Finance. This one Import file format is determined by File Layout GL_JRNL_LOAD.

The runcontrol page is what you are looking for. I have attached the screen shot of the page.

If you already have a logic built in the stored procedure and want to use that logic as it is... then use the app engine program loop through the records from the CSV file and trigger the Stored procedure using people code SQLExec.
Not necessarily have to use any API or Java code to read the file. Just use a file layout and load the record from CSV into the file layout and use row set function to read through the rows and trigger the SP using People Code.

-Ramesh

Attachments:

• Doc1.doc, 51 KB

• ▶ Reply

Permalink Reply by Jim Marion on January 21, 2009 at 1:16pm

Thanks Ramesh. GL_JRNL_IMP is a good example. All the extra validation code makes it a bit complicated, but the basics are there. Here are the basic parts that I think are relevant to this discussion. I copied this from section: PROCESS, step: Import, Action: PeopleCode.

REM ** the name of the file that was uploaded;
&DataFileName = LOAD_JRNL_AET.ATTACHUSERFILE;

try
REM ** move the file from the storage location to the local file system in the directory pointed to by the environment variable PS_FILEDIR;
&RtnCode = GetAttachment(URL.GL_FILE_IMPORT, LOAD_JRNL_AET.ATTACHSYSFILENAME.Value, &DataFileName, "PS_FILEDIR");
catch Exception &Excp;
&RtnCode = 99;
If &Excp.MessageSetNumber = 2 And
&Excp.MessageNumber = 788 Then
MessageBox(0, "", 5825, 118, "Check PS_FILEDIR variable");
Else
Error (&Excp.ToString());
End-If;
end-try;

If &RtnCode = 0 Then
REM ** Open the file for reading, set filelayout, etc;
&ImportFile = GetFile(&DataFileName, "R", &CharSet, %FilePath_Relative);
If &ImportFile.IsOpen Then
&ImportFile.SetFileLayout(FileLayout.GL_JRNL_IMPORT);
&LogFile.WriteLine(Left(String(%Time), 8) | " " | MsgGetText(5825, 104, "Processing file...", &DataFileName));
MessageBox(0, "", 5825, 104, "", &DataFileName);

&File_RS = &ImportFile.CreateRowset();
ImportData(&File_RS);
&ImportFile.Close();
Else
/* Cannot open file */
&LogFile.WriteLine(MsgGetText(5825, 126, "GetFile error", &DataFileName));
&ERRcount = &ERRcount + 1;
End-If;
Else
/* Error getting attachment */
&LogFile.WriteLine(MsgGetText(5825, 125, "GetAttachment error", &DataFileName, &RtnCode));
&ERRcount = &ERRcount + 1;
End-If;

• ▶ Reply

Permalink Reply by Jim Marion on January 21, 2009 at 1:26pm

The FileLayout object is the delivered, recommended method, but for CSV, I actually prefer to use:

Local array of any &data;
Local file &data_file;
Local string &line;

...

While &data_file.ReadLine(&line);
&data = Split(&line, ",");

REM ** process data;
...
End-While;

With this approach, of course, you need to remove quotes from quoted strings, etc. Nevertheless, I find it to be a lot less complicated and require a lot less code than the record/rowset based code required by FileLayout. Just personal preference, of course.

• ▶ Reply

Permalink Reply by Joe Doredant on February 6, 2009 at 9:09am

Ramesh,
Excellent suggestion... we utilize this method as well and works great. However when we are loading the data into the target system we do use component interface peoplecode preceeded by a DoSelect in an App engine that reads the file layout table sending the binds via the App engine table to the CI PeopleCode Step to insure that the data enters the system under the same business rules and security requirements as if it were keyed in through the page on the front end of the application.

Have a great day,
Joe

• ▶ Reply

Permalink Reply by DowntownChicago on July 16, 2009 at 7:31pm

Is this in Finance system? I'm using HRMS and i can't find App engine: GL_JRNL_IMP in app designer :(

• ▶ Reply

Permalink Reply by David Eisenman on January 21, 2009 at 2:46pm

I've processed a CSV file in PeopleCode to allow users to create the data associated with Inventory Adjustments (in Excel)and have them performed en masse. Add_Attachment lets the user select a file from their local drive and Get_Attachment stores the file on the AppServer's TMP drive. It was a quick app designed for a specific purpose (integrating several new business units). A code snipet includes:

/*
Add_Attachment will allow the user to select the file from their local drive
and will FTP the file onto the File Server.
*/
Add_Attachment(URL.GL_FILE_IMPORT, "", "", 0, False, &Recname, &UniqueName, &Filename, 2, &RtnCode);

If (&RtnCode = %Attachment_Success) Then
DoSave();
End-If;

/*
GetAttachment will find the file on the File Server and then store it
in the TMP directory -- Unix or NT -- of the AppServer
*/
&RtnCode = GetAttachment(URL.GL_FILE_IMPORT, &UniqueName, &Filename, "TMP");
If (&RtnCode <> %Attachment_Success) Then
Error (0);
End-If;
&fil = GetEnv("TMP") | "\" | &Filename;

&file1 = GetFile(&fil, "R", %FilePath_Absolute);

/*
Now that we have the file, let's process it...
*/
If &file1.IsOpen Then
&file_rowset = &file1.CreateRowset();
If &file1.SetFileLayout(FileLayout.G_INVADJ_TMP) Then

&del_sql = "delete from ps_" | Record.G_INVADJ_TMP | " WHERE OPRID='" | %OperatorId | "'";
SQLExec(&del_sql);

&Total_Recs = 0;
While &file1.ReadLine(&string)
&skip_row = False;
&ARRAY = Split(&string, ",");

...

If Not &skip_row Then
&method = 3;
&EIP_CTL_ID = generate_eip_ctl_id(&method, G_INVADJ_WRK.BUSINESS_UNIT);

&next = &next + 1;
&rec_test.GetField(&next).Value = &EIP_CTL_ID;
/* Insert the rows into Record.G_INVADJ_TMP */
&rec_test.Insert();
&Total_Recs = &Total_Recs + 1;
End-If;
End-While;

Else
Error ("GAF File error: failed SetFileLayout");
End-If;
Else
Error ("GAF File error: failed file open");
End-If;
&file1.Close();
/*
Now insert rows into BCT_CTL/BCT_DTL
*/

• ▶ Reply

• ‹ Previous
• 1
• 2
• Next ›
• Page