Local File &MYFILE, &MYFILE1;
Local Record &REC;
Local array of string &ARRAY;
Local string &FILE_DIRECTORY, &FileName, &FileNameErr;
Local integer &COUNT, &I, &nbrSlash, &nbrSlash1, &nbrDoubleQuot, &nbrSpace;
Local string &strDate, &strYear, &strMonth, &strDay, &strTime, &strAMPM, &strDateTime, &strEmplid;;
Local string &strDayYear, &strText, &strText1, &strText2, &strDoubleQuot1, &strDoubleQuot2;
Local string &strFilePath, &strFileName, &strRecName;
&strFilePath = Record.PATHNAME.Value;
&strFileName = Record.FILENAME.Value;
&strRecName = RECORDNAME.Value;
&FileName = &strFilePath | "/" | &strFileName;
&FileNameErr = &strFilePath | "/" | "Error.log";
&MYFILE = GetFile(&FileName, "R", %FilePath_Absolute);
&MYFILE1 = GetFile(&FileNameErr, "W", %FilePath_Absolute);
rem &REC = CreateRecord(Record.RecordName);
&REC = CreateRecord(@("Record." | &strRecName));
&ARRAY = CreateArrayRept("", 0);
If &MYFILE.IsOpen Then
While &MYFILE.ReadLine(&STRING);
If &COUNT <> 0 Then
rem &ARRAY = Split(&STRING, Char(9)); /**For Tab separated***/
&ARRAY = Split(&STRING, ","); /*** for Comma Seperated***/
For &I = 1 To &REC.FieldCount
&MYFILE1.WriteLine("RowNum: " | &COUNT | " " | RTrim(LTrim(&ARRAY [&I])) | " Field Type:" | &REC.GetField(&I).Type | " Field Name:" | &REC.GetField(&I).Name);
If &REC.GetField(&I).Type = "DATE" Then
&strDate = (RTrim(LTrim(&ARRAY [&I])));
&nbrSlash = Find("/", &strDate);
If &nbrSlash > 0 Then
&strMonth = Substring(&strDate, 1, &nbrSlash - 1);
If Len(&strMonth) = 1 Then
&strMonth = "0" | &strMonth;
End-If;
&strDayYear = Substring(&strDate, &nbrSlash + 1, Len(&strDate));
&nbrSlash1 = Find("/", &strDayYear);
&strDay = Substring(&strDayYear, 1, &nbrSlash1 - 1);
If Len(&strDay) = 1 Then
&strDay = "0" | &strDay;
End-If;
&strYear = Substring(&strDayYear, &nbrSlash1 + 1, Len(&strDayYear));
If Len(LTrim(RTrim(&strYear, " "), " ")) = 2 Then;
If Value(&strYear) > 40 Then
&strYear = "19" | &strYear
Else
&strYear = "20" | &strYear
End-If;
End-If;
rem &MYFILE1.WriteLine("RowNum: " | &COUNT | " &strDate:" | &strDate | " &strDayYear:" | &strDayYear | " &strMonth:" | &strMonth | " &strDay:" | &strDay | " &strYear:" | &strYear);
&REC.GetField(&I).Value = Date3(Value(&strYear), Value(&strMonth), Value(&strDay));
Else
If RTrim(LTrim(&ARRAY [&I])) = "" Then
&REC.GetField(&I).Value = Date(0);
Else
&REC.GetField(&I).Value = Date(RTrim(LTrim(&ARRAY [&I])));
End-If;
End-If;
Else
If &REC.GetField(&I).Type = "DATETIME" Then
&strDate = (RTrim(LTrim(&ARRAY [&I])));
&nbrSlash = Find("/", &strDate);
If &nbrSlash > 0 Then
&strMonth = Substring(&strDate, 1, &nbrSlash - 1);
If Len(&strMonth) = 1 Then
&strMonth = "0" | &strMonth;
End-If;
&strDayYear = Substring(&strDate, &nbrSlash + 1, Len(&strDate));
&nbrSlash1 = Find("/", &strDayYear);
&strDay = Substring(&strDayYear, 1, &nbrSlash1 - 1);
If Len(&strDay) = 1 Then
&strDay = "0" | &strDay;
End-If;
&nbrSpace = Find(" ", LTrim(RTrim(&strDayYear, " "), " "));
&strYear = Substring(&strDayYear, &nbrSlash1 + 1, &nbrSpace - 3);
If Len(LTrim(RTrim(&strYear, " "), " ")) = 2 Then;
If Value(&strYear) > 40 Then
&strYear = "19" | &strYear;
Else
&strYear = "20" | &strYear;
End-If;
End-If;
&strTime = LTrim(RTrim(Substring(&strDayYear, &nbrSpace + 1, Len(&strDayYear)), " "), " ");
&strAMPM = Substring(&strTime, 9, 10);
&strTime = Substring(&strTime, 1, 8);
rem &strDateTime = LTrim(RTrim(&strYear, " "), " ") | "-" | &strMonth | "-" | &strDay | " " | LTrim(RTrim(&strTime, " "), " ");
&strDateTime = &strMonth | "/" | &strDay | "/" | &strYear | " " | &strTime | " " | &strAMPM;
rem &MYFILE1.WriteLine("RowNum: " | &COUNT | " &strDate:" | &strDate | " &strDayYear:" | &strDayYear | " &strMonth:" | &strMonth | " &strDay:" | &strDay | " &strYear:" | &strYear | " &strTime:" | &strTime | " &strAMPM:" | &strAMPM | " &strDateTime:" | &strDateTime);
&REC.GetField(&I).Value = DateTimeValue(LTrim(RTrim(&strDateTime, " "), " "));
Else
&REC.GetField(&I).Value = Date(RTrim(LTrim(&ARRAY [&I])));
End-If;
Else
If &REC.GetField(&I).Type = "CHAR" Or
&REC.GetField(&I).Type = "LONGCHAR" Then
&strText = RTrim(LTrim(&ARRAY [&I]));
If LTrim(RTrim(&REC.GetField(&I).Name, " "), " ") = "EMPLID" Then
&strEmplid = Rept("0", 9 - Len(&strText)) | &strText;
&REC.GetField(&I).Value = &strEmplid;
Else
If &strText <> "" Then
&REC.GetField(&I).Value = &strText;
Else
&REC.GetField(&I).Value = " ";
End-If;
End-If;
Else
If &REC.GetField(&I).Type = "NUMBER" Or
&REC.GetField(&I).Type = "SIGNEDNUMBER" Then
&strText = RTrim(LTrim(&ARRAY [&I]));
If &strText <> "" Then
&REC.GetField(&I).Value = Value(&strText);
Else
&REC.GetField(&I).Value = 0;
End-If;
End-If;
End-If;
End-If;
End-If;
End-For;
&REC.Insert();
End-If;
&COUNT = &COUNT + 1;
End-While;
Else
End-If;
PeopleCode to Import data from Multiple files into PS Records
Sample Peoplecode to import data from multiple text(.txt) files into PeopleSoft Records using FindFiles Function as below,
/*PeopleCode to Import Data into Record from Multiple .txt files */
Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local string &path;
Local array of string &ARRAY;
Local string &STRING;
Local array of string &aFileNames = CreateArrayRept(“”””, 0);
Local File &fSourceFile;
&path = “D:\InboundFiles\*.txt”;
&aFileNames = FindFiles(&path, %FilePath_Absolute);
While &aFileNames.Len > 0
&fSourceFile = GetFile(&aFileNames.Shift(), “R”, %FilePath_Absolute);
&REC = CreateRecord(Record.SY_SYSTEM_INFO);
&ARRAY = CreateArrayRept(“”, 0);
If &fSourceFile.IsOpen Then
If &fSourceFile.SetFileLayout(FileLayout.SY_SYSTEM_INFO_FLO) Then
While &fSourceFile.ReadLine(&STRING);
&ARRAY = Split(&STRING, “;”);
For &I = 1 To &REC.FieldCount
&REC.GetField(&I).Value = &ARRAY [&I];
End-For;
/* do additional processing here for converting values */
&REC.Insert();
End-While;
Else
/* filelayout not correct */
End-If;
Else
/* file not open */
End-If;
&fSourceFile.Close();
End-While;
No comments:
Post a Comment