PeopleSoft Corner

Who's Online

We have 4 guests online

CB Login

Recommended Products

I use and recommend the following products:

UltraEdit

UltraCompare

BeyondCompare

SQL Developer

del.icio.us addon for Firefox

 

Import Data with ExcelToCI Print
Written by Brent Martin   
Friday, 02 February 2007

The Excel To CI spreadsheet makes it easy to import data from Excel into PeopleSoft. It lets you enter data in a familiar Excel spreadsheet, and when you're ready it loads the data to PeopleSoft via Component Interface.  Since Component Interface is used, it runs the PeopleCode edit checks that run when you enter the data on-line and it enforces the integrity of the data as it loads. Any errors are reported back so that you can correct them.

This article will explain step-by-step how to use ExcelToCI to load a simple set of data (locations) into your PeopleSoft application.

The spreadsheet is called ExcelToCI.xls and is located in your PS_HOME directory under excel folder.

The PeopleBooks documentation is located at http://<YourHelpWebServer:Port>/PSOL/htmldoc/eng/psbooks/tcpi/htm/tcpi10.htm #i090f42a6803232d2.

Before you can use the Excel To CI Spreadsheet, you’ll need to know the name of the Component Interface that you’re working with as well as the WEBLIB_SOAPTOCI web library added to one of your permission lists.

The ExcelToCI utility is a Microsoft Excel spreadsheet with 5 tabs, one for each worksheet. Each worksheet has a special function. Start by opening the ExcelToCI spreadsheet. After opening the file you will be warned that the spreadsheet contains macros. Make sure to click the Enable Macros button.

 

 

If you don’t see the Macro warning dialog, your security settings may be set too high to run any macros.

Navigate to Tools > Macro > Security and double-check your macro security settings. Anything above Medium will cause problems with this spreadsheet. 

Once the spreadsheet is open, it is a good idea to click File > Save As and save it to your local hard drive.

 

 

 

Coversheet Tab

The coversheet tab is a general worksheet that explains how to configure and use the utility. Nothing to see here. Move along.

 


Connect Information Tab

This tab is where you specify the parameters needed to log in to PeopleSoft and submit data to the application. The parameters you’ll need will depend on the environment you’ll be working in.

 

 

Most of the time you can map the parameters from the target environment’s URL directly to fields on this page. The example on the right illustrates this.

 

You may have to ask your administrator what the default local node is. If you need to guess, PT_LOCAL and PSFT_EP are pretty good ones. There’s usually no need to change the Chunking Factor.

Error Threshold can be set if you want to stop the import after a certain number of errors are reached.

The value for the Action field is populated by the system when the component interface is retrieved from the database, so you don’t need to set it at this point.

 

 


Template Tab

The template tab is where you identify the component interface you want to use and which fields you want to map to and submit. The Template Actions toolbar will make everything possible.


First, click the New Template button. You’ll be prompted for your PeopleSoft user id, password, and component interface name.

As you can see, I’m using the Location Table CI for purposes of this demonstration.

When you’re done, click OK.

If you entered everything correctly, the template will be auto-generated based on the component interface.

 

The next step is to tell ExcelToCI which fields you want to include for the CI.

 

We’ll be working with the buttons on the second row of the Template Actions toolbar.

 

Select the grey box below the field name you want to include in your CI mapping. Then click the Select Input Cell button on the Template Actions tab. The Grey box will change colors to indicate that it has been selected. If you make a mistake, repeat the process but click Deselect Input Cell.

Continue the process until all of the cells you need have been selected.

When you’re done, click the New Data Input button. This will build the next worksheet called Data Input.

You will get a warning about deleting existing data. We don’t have existing data so just click Yes.

 

 


Data Tab

The Data Input worksheet is where we will enter the data that we want to load. The fields that we selected on the Template tab have been brought forward to this worksheet, so all we have to do now is enter the data that we want to load.

 

Now we’re ready to submit this data to the Component Interface. But before we do, go back to the Connect Information worksheet and verify that the Action field reflects what we really want to do.

 

If you’re loading new data, make sure the action is Create. It is possible to update data with the ExcelToCI spreadsheet too, so if that’s what you’re trying to do just set the action to UPDATE.

Once you verify this is correct, go back to the Data Input worksheet.

Now click the Stage Data for Submission button. This will build the final tab, Staging and Submission.

     

 


Staging and Submission

The Staging and Submission tab shows all of the data that you’re going to send to PeopleSoft via Component Interface. It’s formatted for PeopleSoft’s purposes, so you might see data broken out on different lines.

 

If you feel good about the data you’ve entered, click the Submit Data button.

The logon box will appear again. Verify your username and password, and click OK.

If the rows load correctly, you should see a green OK button in the Status field of this page. Otherwise you’ll see a red ERROR.

And this shows the data successfully loaded in PeopleSoft.

Dealing with Errors

Sometimes errors happen. The next example was created by submitting the same data a second time.

Here’s what the Staging & Submission tab looks if you submit data that is in error.

If you mouse over the error cell, you’ll see the error message. As you can tell, the row already existed.

If you think this is something you can correct, click the Post Results button which will copy the data back to the Data Input tab. From there you can correct whatever was wrong and resubmit.

To correct this, I’d just change the key information and click the Stage Data for Submission button again.

 

Loading Effective Dated Tables with ExcelToCI

If you’re populating a component with Effective Date as a key, and you want to populate multiple Effective Dates for the same key value, you’ll have to do some special processing. Here’s why:

Remember on the Connection Information tab, there’s an Action field that can be either Add or Update/Display. This means that for each row it attempts to load, it’ll either load it in Add mode or Update/Display mode. If, for example you have the same location with two effective dates, it’ll attempt to add the first row. This will succeed. Then it’ll try to add the next row with the different effective date. But since there’s already a row for the same key values, you’ll get the “Row already exists with the specified keys” error message

So to make it work, you’ll have to create two loads. The first load will be done with the Action field on the Connection Information tab set to Add. It will contain just one row (and one effective date) for each unique key field. This should load successfully.

The next load will be done with the Action field set to Update/Display. It will contain all of the rows that you didn’t process in the first load. Update/Display mode will allow new effective-dated rows to be added to the existing keys so this load will succeed as well.

A Parting Note of Caution

As nice as the spreadsheet is, it only works on components that have a pre-built Component Interface. A component interface is a mapping between an external system and a PeopleSoft component. Since it’s a mapping, the tables and fields won’t necessarily match the tables and fields in the database, or even the component for that matter! And you may see fields for in-memory, or “derived work” records that don’t really exist in the database but are used for on-line computations before data is actually written to the database.

Mapping to the right fields can require a bit of trial and error, but if the Component Interface and the underlying component are designed well (and the delivered CI's usually are) the process should go smoothly.

 

 

Comments (36)add feed
Log file after data processing : Jagadish S
Hello,

This is a wonderful step-by-step explanation of the Excel-to-CI load.

I would like to generate the Log as a text file, and have a copy as reference. Is it possible to create one? I see a check box while clicking on Submit Data, and I checked it to 'generate log'. However, I am not sure where the log file is created.

Please help.

thanks, Jag
May 25, 2007
RE: Log File : Brent Martin : http://www.erpassociates.com
The log file is created in your C:Temp directory. It's called ExcelToCI with the time stamp appended to it and a ".log" file extension.

The log file contains the actual XML message published to PeopleSoft, timings, the response message, and other statistics.
May 25, 2007
Using it in Peoplesoft ver 8.3 : ganesh

It is very useful tool.

We use PeopleSoft Tools ver 8.22 and application 8.31. It doesn't contain the Weblib
WEBLIB_SOAPTOCI. Can any one suggest me in how can I implement this in my system ?

Kindly help.
July 26, 2007
... : Shy
Very well written explanation. Question: Do you know of a method of linking a Customer to a Contact. I've looked at the CI for a Company and a CI for a Contact (Person) but ideally would like to create a CI for Company and add the Contacts to the address book of the Company during one import... I'm new to Peoplesoft and have tried numerous ways but without much success.
August 10, 2007
... : Brent Martin
I don't know the specifics, but if a delivered CI doesn't have the data you want to load you either have to do two loads (usually what I recommend); write your own VBA script to load your spreadsheet into two different components; or customize the Component to load the data you want, then customize the CI to reference the new fields.

If you want to go the 2-loads route (or custom VBA script) you should be able use the CONTACT_INFO component to get contact info loaded.
August 13, 2007
Do we have the Excel to CI feature in version 8.3 : PRABHU
Do we have the Excel to CI feature in version 8.3
October 16, 2007
Software Enigineer : Ishtiaq Ahmad
Its really amazing step-by-step process to export data from excel to peopleSoft smilies/smiley.gif
October 24, 2007
Excel to CI : Tojo
This is very well written document. Is there a way to avoid loading any rows into the database table even if one row fails to load? or in other words, is it possible to do bulk insert instead of row by row?
October 26, 2007
Engineer : saranga Aberatne
this is really good.
November 27, 2007
Q&A : Brent Martin
There's no easy way to do a bulk insert where everything rolls back if one fails to load. Component interface just isn't suited to it. The best way would be to write your own interface using a traditional tool like SQR or App Engine.

PeopleSoft 8.3 did have Component Interface functionality, but I believe the cool ExcelToCI spreadsheet was new to 8.4.
November 27, 2007
Q&A : Tarannum
Hi,

I am using the recent Microsoft 07 version. I am not getting any template actions window on chnaging the security. Why is it so?
December 03, 2007
RE: Q&A : Brent Martin
Excel '07 is a little different -- the toolbars are gone now. At the top in the "Ribbon", you should see an "Add-Ins" tab. Click it, and the options should be there.

For more info about Excel 2007 and ExcelToCI, check out http://www.erpassociates.com/peoplesoft-corner-weblog/utilities/exceltoci-and-office-2007.html.
December 03, 2007
Q : :-)
Hi

Very nicely explained.

I am trying to test this, but i am getting an error as "The dwnload of the specified resourse failed.. error occured in routine sendSOAPRequest_GetCIShape". Has anyone encountered this?Pl. let me know.

TIA
December 05, 2007
RE:Q : Neetha Pai
Hi..

I guess the info in the connect tab is wrong. Pl.take the template from your PS_HOME directory under excel folder.

I hope this helps.

-Neetha
December 10, 2007
PeopleSoft Developer : Richard : http://NA
I have a tricky scenario...what if I need to do both create and update? Say on the first row of data it contains keys and detail that need to be created that would be 0 level and 1 level. But on the subsequent rows they just need to be added at the 1 level. Basically mimic'ing adding a new value and entering subsequent rows of data on a page.
December 18, 2007
PS... : Richard : http://NA
append to the above...exluding the fact this can be achieved on the "submit to database" worksheet by removing the extra header rows.
December 19, 2007
Inserting Parent/Child data with ExcelToCI : Brent Martin
Funny you should ask, Richard. I've been struggling with the same thing myself.

What I tell all of my non-technical users is to set the Action on the Connection Information tab to "Create" . The first one should load correctly, and the rest error out because there?s already a row with those fields in the database. Then change the Action back to Update, submit again, and the first one will error out and the rest will load correctly.

That doesn?t work if you want to load everything using a sequence like "NEXT". Or if you want to give your users a one-step solution. For that, you need to hack the VBA code.

You mentioned that if you delete the 000-level row off of all but the first record, the child rows load under the parent record just the way you want. So why not make the VBA code do that for you during the ?Stage data for submission? step? Here?s how:

Open your spreadsheet and launch the Visual Basic editor. Expand the Modules folder (on the left-hand "Project" sidebar). Double-click the ConvertInputData module. This module has all of the subroutines that drive the "Stage Data For Submission" button. Now search for the comment ?While data exists on the data input sheet?. You?ll see some code that looks like this:

'While data exists on the Data Input sheet
Do While LTrim(wksDataInput.Cells(lDataInputRow, 1).Value) > " " Or LTrim(wksDataInput.Cells(lDataInputRow, 2).Value) > " "

'Copy the data from the data input, check to see if a status row exists
iDataInputColumn = 1
If ActiveWorkbook.Sheets(sSheet4).Cells(iConstDataInputRowLabel, iConstDataInputColHome).Value = sLblStatus Then
iDataInputColumn = iDataInputColumn 1
End If

lRowCount = copyDataValues(lFirstSubmitToDbLastRow, lRows)
lDataInputRow = lDataInputRow 1

Loop

See the "Do While" line? This looks in columns 1 and 2 to see if there?s data, and if no data exists it figures everything has been staged. Usually your parent record will start in column 1 (or column 2 if you hit the Post Results button). How I approached it was that the parent record will only contain data if I want a new one inserted, and I only wanted to stop the loop if no data exists in the Child row. So what you have to do is figure out what column the child data starts in, and change the Do While to look in those columns instead of columns 1 and 2. Assuming my child record starts in column 11 (or 12 if somebody hits the Post Results), here?s how the Do While will look:

Do While LTrim(wksDataInput.Cells(lDataInputRow, 11).Value) > " " Or LTrim(wksDataInput.Cells(lDataInputRow, 12).Value) > " "

(Looks like i'm out of space. This is continued in the next comment)

December 20, 2007
Inserting Parent/Child data with ExcelToCI : Brent Martin
Next, we need to delete the 000 rows on the "Staging and Submission" tab if they don?t contain data. Find the CopyDataValues function and you?ll see a For J loop that has a nested "for l" loop. Just under the "Next l" line, add this code:

'EA Mod Brent Martin 12/14/2007 - Remove the 000 row if it doesn't have any data
' This will allow multiple rows to be inserted into the same component
If Cells(j, 3).Value = "000" And Cells(j, 4).Value
ActiveWorkbook.Sheets(sSheet5).Rows(j).Delete
j = j - 1
lRowCount = lRowCount - 1
End If
' End EA Mod

This deletes the blank 000 rows and sets the counters so that you get a clean staging sheet with good parent-child values. After that, you should be able to submit your parent-child data in one shot. Let me know how it works for you!


December 20, 2007
re: Inserting Parent/Child data with ExcelToCI : Richard : http://NA
With some tweaking I got it to work for my situation. This is what I did:

Prior to the Do While loop add the following:

Dim y As Long
y = 1

Then where you stated to add your code I added a modified version for our situation (in my version its a nested k loop):

If Cells(j, 3).Value = "000" Then
If y > 1 Then
ActiveWorkbook.Sheets("Submit to Database").Rows(j).Delete
j = j - 1
iRowCount = iRowCount - 1
End If
y = y 1
End If

Thanks for your help. It really gave me a boost.
December 20, 2007
... : abcd
The best explanation every seen on ExcelToCI...

Thanks..
December 30, 2007
... : Eric
Brent, thank you very much for taking the time to put together this great document!

Having one issue... when loading PeopleSoft field types defined as ?NUMBERS?, PeopleSoft is truncating the value by dropping the decimals (e.g. loading 15.00 instead of 15.23). Specifically, we are trying to load data into the SALARY_GRADE component interface. I?ve tried changing the format of the Excel field (e.g. number, currency, text, etc.) but nothing seems to work.

Has anyone else experienced this issue?
January 02, 2008
... : Pumpkin
Hi Brent, This one is a very neat and clear explanation. Thank Goodness i saw ur blog b4 digging into the 600 pages doc published by PeopleSoft.

Anyways, i have a quick question now. I am trying to integrate CI through Messages. I have currently built a CI based on the VCHR_EXPRESS component. When i parse XML which i got as an input from the user through the message and try to instantiate the CI and call the Create method , it says First operand of .null ----- error. There is a global record which gets value in the search save code . But since CI is not supporting the search save event, this particular record is empty and system throws this error. Is there any workaround for this ? i mean how to pass search record values to the Pre build code ?

January 04, 2008
RE: Truncating Numbers : Brent Martin
I haven't seen this problem. You might create a very small file (like 1 row) and when you click the "Submit Data" button, check the "Generate Log" box on the Login popup window. This will create a log file in your temp directory that will show (among other things) the XML that is being submitted.

Look at the XML and see if the decimals appear there. If not, it's some type of problem with your spreadsheet -- maybe you need to change the format of your decimal fields or walk through the VBA debugger and figure out what makes the decimals disappear.

If the decimals do exist in the XML file, then the CI is stripping them off somehow so you might want to open a case with PeopleSoft or do some tracing on the PeopleSoft side.
January 04, 2008
RE: Pumpkin : Brent Martin
Okay so let me get this straight. Integration broker receives a message, and you have some subscription PCode (or something) that you want to invoke CI and push the data into. Does that sound right?

This isn't exactly an ExcelToCI (or even an SOAPTOCI) deal -- I'm assuming you invoke the CI directly from PeopleCode. Without seeing your code I'm not sure how to help you, but I'd suggest that you break it down a bit. Make the Pcode program insert hard-coded values first just to get the silly thing working -- this will probably be the toughest part. You might create it on a simple page with a push button so you can try and debug different approaches more quickly. Once it works, integrate it with your incoming message.

I'm not sure of your requirements, but inserting data directly to VCHR_EXPRESS isn't technically required -- it seems like you could write to the voucher staging tables and run the Voucher Build process to generate the voucher. You might also take a look at the Voucher Upload spreadsheet (can't remember what it's called off the top of my head) and see what logic it invokes to build vouchers. There might be an easier integration point...
January 04, 2008
SENDSOAPREQUEST_GETCISHAPE error : SAS
After banging my head for couple of hours I figured cause for SENDSOAPREQUEST_GETCISHAPE error. We were using https:// for the URL but our certificates were wrong. After talking to the admin, I changed the Protocol from https to http and changed the port number which corresponds to http://. After making this, it worked.
January 04, 2008
... : Pumpkin
Thanks brent for your suggestion. I infact tried hardcoding the values into the search record in the message subscription and tried invoking the CI but in vain. Why i want to use the VCHR_EXPRESS component to write the voucher details is because -- my client does not want some sort of batch process to run and then get back the voucher id . They want immediate responce . Hence i thought CI approach should be good .

Here is where my problem occurs. There is a search save code in this component which is not getting triggered when CI executes because of which im getting the first operand of .null error . I tried replicating the same code in the prebuild event still system is not recognising the search record and its values. I need help in this portion . a) How to bring the search record values into the CI



January 04, 2008
RE: VCHR_EXPRESS : Brent Martin
Sometimes to make a CI work you'll need to customize the component or the objects it refers to. For instance, CI loads fields in the order that they appear in the record definition, not in the tab order of the page. So sometimes you have to rearrange field order in record definitions to make them work. Other times you might have to conditionally fire PeopleCode depending on if you're accessing via CI or online.

I've tried to make CI work with the VCHR_EXPRESS component in the past, and as I recall it's not easy (in fact I didn't accomplish what I was trying to do). I'm not saying it can't be done, but it'll be almost impossible to know how much time you'll need to spend on it. If you don't have a hard deadline that might be OK, if you're working on a project with agressive timelines you might want to try another approach. Here are a couple of suggestions:

1) Look for another Component that can enter vouchers that's not as complicated. Quick Invoice Entry comes to mind.

2) If there's not a lot of variability in your message, or if you're sure your message won't send bad data, it might be worth just running a SQL Trace to see what validations the Component does, how it gets the next Voucher ID and what tables get inserted, and duplicate those in PCode without going through CI.

Sorry I can't be more help on this one.
January 07, 2008
... : Pumpkin
Thanks for this info brent. I will look into different approach ..
January 07, 2008
Spreadsheet Template : martin
Can anyone help me about the spreadsheet template to modify?
Can I make the entry sheets to make it as one sheet? then all codes behind it was added to a single command button?
January 09, 2008
PeopleSoft Developer : Richard
Be sure to add the web library permission WEBLIB_SOAPTOCI to your permission list that the component interface resides. Been beating my head for weeks on this one. Found this on a Google search. Besides verifying your default node setup, this is yet another fix for the infamous "The response text is not valid XML. Login Data cleared."
January 29, 2008
PeopleSoft Developer : Richard
append to above...On closer look Brent has mentioned this further up this page. Just goes to show you have to pay attention.
January 30, 2008
... : Barb
Hello,
I have an exceltoci populating a very simple custom component/record. The problem we are having is with performance. Any more then 200 lines takes hours to run, less then 200 will run in 20 minutes or so. Is there a way to tune this at all? The PCode excuting behind the component is minimal.
Thanks

Barb
March 13, 2008
RE: Performance : Brent Martin
That's not a lot of information to go on but I'll take a stab at it. One common problem I've seen with the ExcelToCI code is with how it deals with parent-child inserts. If you have the same header row but want to insert child rows, it works like this: 1) Look up the header row through the search record in update/display mode & open the component, and 2) Insert a single child row, and 3) Go back to step 1 for each child row. Each time it takes a little longer for step 1 because there are more child rows to load.

When you have a lot of inserts, how you want it to work is like this: 1) Look up the header row in update/display mode, and 2) Insert all of the child rows.

You can trick ExcelToCI to do the more efficient approach on the staging and submission tab. Delete all but the first 000 (header) rows, leaving the child rows in place. Then submit it.
March 16, 2008
... : Jeanie
Thanks for posting the great step by step.

Has anyone seen a scenario where you get an "OK" status indicating all went well and the data was loaded, but no data was actually written to the database?

I'm loading items via the IN_MST_ITM_XLS CI. Built template and input a line of data to load, staged data for submission, and clicked on "Submit Data" on the "Staging and Submission" page. I get a status back that all is "OK", but there's no data when in check in psoft.

FSCM 8.9 MP3, PT 8.47.05, Oracle 10G.

Thanks for any assist!
July 09, 2008
... : sri
Hi,

I am trying to read the functional documents of some of the financial module.Could you please let me know the link.
July 31, 2008
PeopleSoft documentation : Brent Martin
July 31, 2008
Write comment
quote
bold
italicize
underline
strike
url
image
quote
quote
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley
Smiley


Write the displayed characters


busy
Last Updated ( Saturday, 03 February 2007 )
 
< Prev   Next >