0

I'm setting up a program wherein it will read a csv file from a directory and it will be loaded into a peoplesoft table. 

Local SQL &SQL;
Local File &FileSetid;
Local array of string &Columns_array;
Local string &RowSetid;

&FileSetid = GetFile("\\CBRTPWDAPU218\psoft\fspsdev\custhome\sqr\testdata.csv", "R", %FilePath_Absolute);

If &FileSetid.IsOpen Then
   While &FileSetid.ReadLine(&RowSetid);
      &Columns_array = Split(&RowSetid, ",");
      SQLExec("INSERT INTO PS_FT_TRN_ITEM_CLM (BUSINESS_UNIT, CUST_ID, ITEM, ENTRY_TYPE, ENTRY_REASON) VALUES (:1,:2,:3,:4,:5)", &Columns_array [1], &Columns_array [2], &Columns_array [3], &Columns_array [4], &Columns_array [5]);

   End-While;
End-If;

I am getting "Fetching Array Element 2: Index is not in range 1 to 1."

The input file I have contains this:

BUSINESS_UNIT,CUST_ID,ITEM,ENTRY_TYPE,ENTRY_REASON 10000,ARTEST,ITEM01,NEW,NEW

Tom Micua
  • 5
  • 1
  • 4
  • I've done this kind of parsing using SQR to import, but when using PeopleCode, I've only used File Layout definitions and never needed to do my own parsing like this. If you haven't considered using a File Layout, it's worth considering. – qyb2zm302 Feb 04 '19 at 17:19

1 Answers1

0

I am not positive, but you might need to instantiate the array, either with CreateArray() or CreateArrayRept(). One option may be to create an empty array of String using CreateArrayRept("",0);. Then maybe the Split() function will be able to fill the array.

Local array of string &Columns_array;

&Columns_array=CreateArrayRept("",0);
&Columns_array = Split(&RowSetid, ",");

I am not exactly sure how that will work with the loop, though. You might need to reinitialize for each iteration, or use another array method like .shift to return the values for your insert statement and leave the array empty.

All that said, I do agree with the comment about using File Layouts for this type of thing. They work well for reading in flat files.

otherted
  • 151
  • 3