Wednesday, August 4, 2010

Read A Flat File Using File Layout And Insert Into The Specific Component Using CI ( Bulk Insert )

Read A Flat File Using File Layout And Insert Into The Specific Component Using CI:
Approach:
Get the sample flat file and create a File Layout.
Create the CI for the component.
Create an AE and drag the File Layout and CI for dynamic code generation in the peoplecode action

Sample Code:
/*******************************************************************************/
Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;
/* *****************************************************************/
Function StartSession
try
&oSession = %Session;
&oSession.PSMessagesMode = 1;
catch Exception &ex
&LOGFILE.WriteLine(&ex.ToString());
end-try;
End-Function;
/*******************************************************************/
Function ImportData(&RS1 As Rowset)
try
&oEmPDpndtBlInsCi = &oSession.GetCompIntfc(CompIntfc.EM_P_DPNDT_BL_INS_CI);
If &oEmPDpndtBlInsCi = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;

&oEmPDpndtBlInsCi.InteractiveMode = False;
&oEmPDpndtBlInsCi.GetHistoryItems = True;
&oEmPDpndtBlInsCi.EditHistoryItems = False;

&recPerInfo = CreateRecord(Record.EM_PER_INFO);
&recPerInfo1 = CreateRecord(Record.EM_PER_INFO1);
&recBnkInf = CreateRecord(Record.EM_BANK_INFO);
&recdpdntInf = CreateRecord(Record.EM_DPNDNT_INFO);
&LOGFILE.WriteRowset(&RS);
&RS1(1).GetRecord(1).CopyFieldsTo(&recPerInfo);
&oEmPDpndtBlInsCi.EMPLID = &recPerInfo.EMPLID.Value;

rem ***** Execute Get *****;
If Not &oEmPDpndtBlInsCi.Get() Then
rem ***** No rows exist for the specified keys.*****;
errorHandler();
throw CreateException(0, 0, "Get failed");
End-If;

&oEmPerInfo1Collection = &oEmPDpndtBlInsCi.EM_PER_INFO1;
&RS2 = &RS1(1).GetRowset(Scroll.EM_PER_INFO1);
For &i = 1 To &RS2.ActiveRowCount
If &i > 1 Then
&oEmPerInfo1 = &oEmPerInfo1Collection.EM_PER_INFO1.InsertItem(1);
Else
&oEmPerInfo1 = &oEmPerInfo1Collection.Item(1);
End-If;
&RS2.GetRow(&i).GetRecord(Record.EM_PER_INFO1).CopyFieldsTo(&recPerInfo1);
&oEmPerInfo1.EFFDT = &recPerInfo1.EFFDT.Value;
&oEmPerInfo1.SEX = &recPerInfo1.SEX.Value;
&oEmPerInfo1.MAR_STATUS = &recPerInfo1.MAR_STATUS.Value;

End-For;

&RS3 = &RS1(1).GetRowset(Scroll.EM_BANK_INFO);
&oEmBankInfoCollection = &oEmPDpndtBlInsCi.EM_BANK_INFO;
For &j = 1 To &RS3.ActiveRowCount
If &j > 1 Then
&oEmBankInfo = &oEmBankInfoCollection.EM_BANK_INFO.InsertItem(1);
Else
&oEmBankInfo = &oEmBankInfoCollection.Item(&j);
End-If;
&RS3.GetRow(&j).GetRecord(Record.EM_BANK_INFO).CopyFieldsTo(&recBnkInf);
&oEmBankInfo.BANK_NM = &recBnkInf.BANK_NM.Value;
&oEmBankInfo.BANK_ACCOUNT_NUM = &recBnkInf.BANK_ACCOUNT_NUM.Value;
End-For;

rem ***** End: Get/Set Component Interface Properties *****;
rem ***** Execute Save *****;
If Not &oEmPDpndtBlInsCi.Save() Then;
rem errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;
catch Exception &ex
&fileLog.WriteLine(&ex.ToString());
end-try;
End-Function;
rem *****************************************************************;
rem * PeopleCode to Import Data *;
rem *****************************************************************;
&FILE1 = GetFile("C:\Documents and Settings\pmundhr\Desktop\dpndBlkIns.csv", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("C:\Documents and Settings\pmundhr\Desktop\dpndBlkIns.csv.err", "W", %FilePath_Absolute);
StartSession();
&FILE1.SetFileLayout(FileLayout.EM_P_DPNDT_IB);
&LOGFILE.SetFileLayout(FileLayout.EM_P_DPNDT_IB);
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.EM_PER_INFO, CreateRowset(Record.EM_PER_INFO1), CreateRowset(Record.EM_BANK_INFO), CreateRowset(Record.EM_DPNDNT_INFO));

&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
ImportData(&RS1);
&RS1 = &FILE1.ReadRowset();
End-While;

&FILE1.Close();
&LOGFILE.Close();
/******************************************************************************/
Sample FileLayout Image:

1 comments:

Long Chen said...

very useful for me! thank you very much!

Post a Comment