Import Data with ExcelToCI

Posted by: Brent Martin in Utilities

Tagged in: Untagged 

Brent Martin

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 and the SOAPTOCI web service 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.

 

 

Trackback(0)
Comments (76)Add Comment
0
Log file after data processing
written by Jagadish S, May 25, 2007
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
Brent Martin
RE: Log File
written by Brent Martin, May 25, 2007
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.
0
Using it in Peoplesoft ver 8.3
written by ganesh, July 26, 2007

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.
0
...
written by Shy, August 10, 2007
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.
Brent Martin
...
written by Brent Martin, August 13, 2007
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.
0
Do we have the Excel to CI feature in version 8.3
written by PRABHU, October 17, 2007
Do we have the Excel to CI feature in version 8.3
0
Software Enigineer
written by Ishtiaq Ahmad, October 24, 2007
Its really amazing step-by-step process to export data from excel to peopleSoft smilies/smiley.gif
0
Excel to CI
written by Tojo, October 27, 2007
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?
0
Engineer
written by saranga Aberatne, November 27, 2007
this is really good.
Brent Martin
Q&A
written by Brent Martin, November 28, 2007
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.
0
Q&A
written by Tarannum, December 03, 2007
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?
Brent Martin
RE: Q&A
written by Brent Martin, December 03, 2007
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/p...-2007.html.
0
Q
written by :-), December 05, 2007
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
0
RE:Q
written by Neetha Pai, December 10, 2007
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
0
PeopleSoft Developer
written by Richard, December 19, 2007
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.
0
PS...
written by Richard, December 19, 2007
append to the above...exluding the fact this can be achieved on the "submit to database" worksheet by removing the extra header rows.
Brent Martin
Inserting Parent/Child data with ExcelToCI
written by Brent Martin, December 20, 2007
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)
Brent Martin
Inserting Parent/Child data with ExcelToCI
written by Brent Martin, December 20, 2007
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!

0
re: Inserting Parent/Child data with ExcelToCI
written by Richard, December 20, 2007
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.
0
...
written by abcd, December 30, 2007
The best explanation every seen on ExcelToCI...

Thanks..
0
...
written by Eric, January 03, 2008
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?
0
...
written by Pumpkin, January 04, 2008
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 ?
Brent Martin
RE: Truncating Numbers
written by Brent Martin, January 04, 2008
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.
Brent Martin
RE: Pumpkin
written by Brent Martin, January 04, 2008
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...
0
SENDSOAPREQUEST_GETCISHAPE error
written by SAS, January 05, 2008
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.
0
...
written by Pumpkin, January 05, 2008
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


Brent Martin
RE: VCHR_EXPRESS
written by Brent Martin, January 07, 2008
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.
0
...
written by Pumpkin, January 08, 2008
Thanks for this info brent. I will look into different approach ..
0
Spreadsheet Template
written by martin, January 09, 2008
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?
0
PeopleSoft Developer
written by Richard, January 30, 2008
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."
0
PeopleSoft Developer
written by Richard, January 30, 2008
append to above...On closer look Brent has mentioned this further up this page. Just goes to show you have to pay attention.
0
...
written by Barb, March 14, 2008
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
Brent Martin
RE: Performance
written by Brent Martin, March 16, 2008
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.
0
...
written by Jeanie, July 10, 2008
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!
0
...
written by sri, July 31, 2008
Hi,

I am trying to read the functional documents of some of the financial module.Could you please let me know the link.
Brent Martin
PeopleSoft documentation
written by Brent Martin, July 31, 2008
PeopleSoft documentation is at http://www.oracle.com/technolo...tent.html.
0
...
written by Gee, December 13, 2008
I have used the VCHR_EXPRESS component to build a CI, when I test it in app designer, it works fine, however, when I use excel to load it it i get the following error. Any help?
Errors:
#1 - SQL error. Stmt #: 908 Error Position: 0 Return: 8602 - [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'VNDR_AFFILIATE', table 'EPDEV.dbo.PS_VOUCHER'; column does not allow nulls. INSERT fails.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been termi
#2 - INSERT INTO PS_VOUCHER_ADD_VW(BUSINESS_UNIT,VOUCHER_ID,VOUCHER_STYL
E) VALUES(:1,:2,:3)
#3 - Error saving Component Interface. {VCHR_UPLOAD} (91,37)
#4 - Error saving Component Interface. {VCHR_UPLOAD}
Thanks
0
...
written by laura, December 13, 2008
Hello,
I've been reading the ocmments related to your article and they are great.I have a question for you: could you please indicate me how can i import the open items/invoices in Receivables using the Excel to Ci- in psftV9? I have found a ci dc_penditems but this only imports the items/invoices from billing that are normally in pending tables before ar update, but what can i do with the open items/invoices for a customer that are already into the customer balance? I have to create separate component interfaces for all the tables that contain these open items in receivables.Thanks a lot for your help- i mention that i am a functional girl and i cannot use the VB codes smilies/sad.gif
0
...
written by laura, December 13, 2008
for GEE: as i know for now from using excel to ci, you don't have to have into the excell the values NULL because you will have to the message you got.try to delete the values nULL and leave blank for taht column.That usually worked for me everytime
Brent Martin
RE: DC_PENDITEM
written by Brent Martin, December 14, 2008
Laura, not sure if I understand your question, but PS_GROUP_CONTROL and PS_PENDING_ITEM are the staging tables for everything that happens in AR. You can add to or take away from item balances by staging additional entries in PS_PENDING_ITEM, as long as you use the same BU and ITEM of the ones you're wanting to adjust. When AR Update runs, it'll apply the new transactions to the item's balance.

When I created an ExcelToCI to do this a couple of years ago, I ended up writing my own Component Interface to do it. I can't remember what was wrong with the delivered DC_PENDITEM but it seems like it didn't have all of the fields I needed. At any rate it simply moved items into PS_PENDING_ITEM and PS_GROUP_CONTROL, and AR Update did the rest of the work.

You really can't use ExcelToCi to hit other AR transaction tables tables (like PS_ITEM or PS_ITEM_ACTIVITY), since there's no on-line functionality that adjusts them directly and Component Interface only works with what you can already do online.

Hope this helps!
Brent Martin
RE: VCHR_EXPRESS CI / NULL Error
written by Brent Martin, December 14, 2008
Make sure you include the level that VNDR_AFFILIATE is on, and that you include the field in your template. You might also include that VOUCHER_STYLE field in your template just so you know it's being included.

There's a lot of things that go on behind the scenes when you work with Component Interface that aren't easy to diagnose. My rule is to include any fields (along with the relevant parent keys) the error messages complain about. Once you get something working, you can start removing columns one at a time until it breaks.
0
...
written by laura, December 16, 2008
thank you brent..i'll try to use the ci dc_penditem to load an open item for a customer and then launch the ar update to see if everything will be updated.i'll come back if i 'll have further questions
0
...
written by laura, December 17, 2008
hello Brent, i 'm back with another question for you : i also had to create anoter dc_penditem_ci because the old one didn't have the fields of vat included from the PS_PENDING_ITEM table, bu now i have another problemWhen trying to submit data , i got the following message "#1 - Warning -- The Accounting Date entered is not the current date. Please verify that the date entered is correct. (6000,815)

#2 - Warning -- The Accounting Date entered is not the current date. Please verify that the date entered is correct. (6000,815)

#3 - The highlighted field is required. You must enter a value for it before proceeding.{U_DC_PENDITEM_CI.GROUP_TYPE} (15,54)

#4 - The highlighted field is required. You must enter a value for it before proceeding.{U_DC_PENDITEM_CI.ORIGIN_ID} (15,54)

#5 - Error saving Component Interface. {U_DC_PENDITEM_CI} (91,37)

#6 - Error saving Component Interface. {U_DC_PENDITEM_CI}".I have checked the group type and the origini id and i have them setup in the PSFTV9.Then i excluded those 2 columns from my CI but i also get the same error.I mention that i have already excluded columns- that are required as these 2- but i managed to make the Ci work.Could you please give me an advice?



smilies/sad.gif smilies/sad.gif
0
developer1
written by Rupa, February 25, 2009
If I change the name of the delivered ExcelToCI.xls, I cannot see the log file, what do I need to do to be able to see the log file?
0
Developer
written by pindiwall, April 13, 2009
Once data got loaded in staging tables (vchr_hdr_stg, vchr_line_stg, vchr_dist_stg). If for some reason we need to delete that data from these staging tables, Is there any way we can do it using peoplesoft. Is there any mechanism provided by PeopleSoft to clean up these tables without using a backend SQL
Brent Martin
RE: Data Loaded
written by Brent Martin, April 15, 2009
I don't know of anything PS delivers to delete rows from the voucher staging tables. In general, PeopleSoft doesn't make it easy to delete data once it's in the system. There is some archiving functionality but I've never known any of my clients to use it. Just do what everybody else does -- write a SQL script to delete the data.
0
PSFT Consultant
written by Sjois, April 22, 2009
I am trying to upload time sheet entries using TE_TIME_ENTRY CI. The fields where we enter the hours (Time1, Time2, etc) are work record fields. I can see the work record in the CI properties, but these fields are not visible when I use the CI in the Excel to CI. Any idea how to get these work record fields into the template?
Brent Martin
RE: TE_TIME_ENTRY
written by Brent Martin, April 22, 2009
Go to app designer, open the TE_TIME_ENTRY_CI. Make sure all of the fields you want to add are listed as properties. Then on the Template tab create a new template and the new fields should be visible.
0
multiple child row insertion
written by AGNEL, April 27, 2009
i want to insert multiple child rows( withholding details) for a single vendor_id. we have added two new table VENDOR_WTHD_JUR and VENDOR_WTHD in the VNDR_ID CI(delivered). but it shows the error "ROWS ALREADY EXISTS WITH THE SPCIFIED KEYS" the child tables are in level3. can u please help me insert multiple rows for the same vendor_id.
Brent Martin
RE: VNDR_ID CI
written by Brent Martin, April 28, 2009
The obvious suggestions would be to regenerate the ExcelToCI template, include all of the key and subkey fields, and be sure you populate them on your data input sheet.
0
Creating an Excel to CI template
written by Aditi Baveja, May 07, 2009
Hi Brent,

I want to configure the Excel to CI utility for a given CI and generate data input sheet for the end user. In the sense, the end user should not have to bother with the connection setup, connecting to the CI, creating a new template or selecting the input cells. He/She could directly enter values and submit them. Any ideas ?
Thanks and Regards,
Aditi
Brent Martin
RE: Creating an Excel to CI template
written by Brent Martin, May 07, 2009
Just set up the spreadsheet for the user the way it needs to be set, hide or protect the tabs you don't want them messing with, and ask them to use that specific spreadsheet going forward.
Melanie Hollandsworth
...
written by a guest, May 20, 2009
Excel to CI voucher load
Brent:
I am trying to create an Excel to CI load of vouchers using a Quick Invoice Entry CI. I get an error: First operand of .null -----. Is there any workaround for this? How to pass search record values to the Pre build code? Is there some command to use that will pull data from the worksheet into the search record?
Brent Martin
RE: First operand .null in CI
written by Brent Martin, May 22, 2009
Unfortunately ExcelToCI is limited in what you can do with it. You can't directly program the API, and if you need to specify certain values to the specific search records that aren't part of your template, you're probably out of luck. I'd either do a PeopleCode trace or use the PeopleCode debugger to identify the problem statements, figure out what values would make those statements happy, then decide if there's anything you can do in the ExcelToCi spreadsheet to provide those values. If not, you might have to "roll your own" spreadsheet macro to call the CI through VB code or create your own SoapToCI XML messages and publish them to your web server.
Melanie Hollandsworth
Excel to CI for voucher import using Quick Invoice Entry CI
written by Renee, June 04, 2009
Using FSCM 8.8 tools 8.49.10. We used the Vchr_quick_pnl to build CI and am getting OK status upon submitting the data when we do not fill out the chartfields in the data spreadsheet. If we fill out the chartfields either on the vchr_line_qv or vchr_dist_qv, we get the an error. If we fill out the chartfields in the vchr_dist_qv fields, we get the following error.

#1 - Invalid value -- press the prompt button of hyperlink for a list of values (15,11)
#2 - Error changing value. {VCHR_QUICK_PNL_CI.VCHR_LINE_QV.VCHR_DIST_QV(1).BUSINESS_UNIT_PC_3} (91,34)
#3 - Error saving Component Interface {VCHR_QUICK_PNL_CI}.

This is the data that is entered(when done manually) thru the PC_fields page via a link 'Projects' on the VCHR_QUICK_PNL. I am thinking this might be a PC change but cannot identify where and what.
Anyone have any ideas? Thanks.
chandrashekhar
Re : Excel to CI for voucher import using Quick Invoice Entry CI : Renee
written by chandrashekhar, June 05, 2009
make sure the field BUSINESS_UNIT_PC_3 is added into properties of CI.
Melanie Hollandsworth
...
written by Renee, June 05, 2009
I appreciate your prompt feedback. Field BUSINESS_UNIT_PC_3 is a property of CI under the VCHR_DIST_QV Record. Any other ideas? Thank you.
0
Excel to CI for voucher import using Quick Invoice Entry CI
written by Renee answered, June 22, 2009
Discovered business_unit_pc_3 was trying to update pc_fields and this was not going to happen. This has been solved by replacing business_unit_pc_3 with business_unit_pc from the vchr_dist_qv in the CI and spreadsheet to CI. Now it updated the vchr_dist_qv business_unit_pc fields. Repeated efforts for all the project info fields in the CI.

Now I need to find out how to allow it to insert multiple invoices without having to hit 'submit data' once per invoice in the Excel to CI. Anyone have any suggestions?
0
Excel to CI for voucher import using Quick Invoice Entry CI - multiple invoices
written by Renee answer, June 23, 2009
I was able to get the Excel to CI to load multiple invoices by changing Record Peoplecode saveEdit event and the component prebuild event. I set an indicator on the saveedit and if it was set once it hit the prebuild event, I initialized the record again. If you need more detail, let me know.
Brent Martin
RE: Voucher Import
written by Brent Martin, June 24, 2009
Thanks for following up, Renee. I know that was a lot of trial and error to get that going and I appreciate that you posted answers to your own questions here! You're probably the first person I've known to get voucher import working on ExcelToCI. Congrats!
0
How to Grey-out fields in 'Staging & Submission' tab or make fields in 'Staging & Submission' tab non-editable
written by Junaid, July 21, 2009
URGENT: How to Grey-out fields in 'Staging & Submission' tab or make fields in 'Staging & Submission' tab non-editable?
This can avoid user to enter/change values in 'Staging & Submission' tab
0
Developer/Analyst
written by Bryan (Boise State), July 29, 2009
Brent,

You have a lot of great comments and information here. I have been studying them very carefully in my attempt to get the batch load of Location Amounts through the CI working. However, and this is yet another parent/child relationship issue, I keep getting an error. I do the initial load using "Create" as the Action. Then I am attempting to load, or update, new data with a different effective date. I am positive that the key structure is unique, however, I am getting the error message "Data being added conflicts with existing data." What I don't know is where the data conflict is, at the header record (ex_loc_amount) or the child record (ex_loc_amt_dtl).

I noticed that in creating Component Interfaces, EFFDT is never added to 'FINDKEYS', 'GETKEYS', and 'CREATEKEYS' in the component interface in App Designer. It just seems that EFFDT is not being used as part of making the key structure unique when loading data through the component interface, however there's not a problem when going through the web interface.
0
Data Input Area missing Half of the Tables
written by Misha Lauenstein, August 01, 2009
Hi,
I am trying to use component interface VNDR_ID. My problem is that when I click "New Data Input", I only get the cells from the first section of the Template (rows 12 - 29). At first I thought it was an overflow problem, because I had more than IV columns, but I removed a bunch of columns that I don't need to populate, and I still get nothing from rows 64 - 70 (Record Type A21 through A27). PS version is 8.8. Excel version is 2003.
Any ideas?
Thanks in advance
0
Error
written by Young, August 26, 2009
SOS!!
Errors:
#1 - Property or collection SCC_IWI_TBL was not found in the component interface at the given level

What does it mean? And how to insert data into the record?Thanks for your help!
0
Time Out values for resolvetimeout, connecttimeout, sendtimeout, and receivetimeout.
written by Fred, September 29, 2009
The Visual Basic code needed to have 4 timeout settings added: resolvetimeout, connecttimeout, sendtimeout, and receivetimeout. According to the Microsoft web site "http://msdn.microsoft.com/en-us/library/ms760403(VS.85).aspx" , if this is not added the default is infinite for resolve, 60 seconds for connect, 30 seconds for both send and receive timeouts. Are there recommended timeouts based on users experience?
Tamara Cooksey
...
written by Tamara Cooksey, October 15, 2009
This document is EXACTLY what I've been looking for. I've always used the AR Upload and GL Upload templates that were delivered with PS. Now I'm trying to find a way to get data into the Billing Tmp tables. I have the generic ExcelToCI template and am trying to configure it for our data. My only question is, what Component am I supposed to use for Billing? I "think" it's BI_INTFC, but I'm not sure. Does anyone know if there is a list of valid CI's? I know you can get it through App Designer, but I don't have access to it. I am a functional user. My PS Developer is swamped with other projects so I thought I'd give it a try. (I'm getting my IT Degree in December and have had all the IT classes etc., so I am familiar with all of this.)

These are the CIs that I found within this blog posting (Time Entry is my next big task!)

TE_TIME_ENTRY CI
CONTACT_INFO
VCHR_EXPRESS
SALARY_GRADE
IN_MST_ITM_XLS
DC_PENDITEM
PS_PENDING_ITEM
PS_GROUP_CONTROL
VNDR_ID
VCHR_QUICK_PNL_CI

Thanks again!!!!
-tam

Brent Martin
RE: List of CI's
written by Brent Martin, October 16, 2009
Tamara, I'd be glad to stamp your hand and let you into the technical club. If your mean admins won't give you app designer access (tell Aaron and Cynthia I said hi by the way) then you can get a list of CI's with a query like this:

SELECT DISTINCT A.BCPGNAME, A.BCNAME,A.DESCR FROM PSBCDEFN A ORDER BY A.BCPGNAME

BCPGNAME is the component name, and BCNAME is the Component Interface name that you'll need to plug in to your ExcelToCi spreadsheet.

Not all components have corresponding Component Interfaces created, so if you can't find a CI for INTFC_BI (and I'm thinking INTFC_BI_N_VW might work), you'll have to get your developer to create one for you.

Good luck!
0
Excel to CI
written by shruti, November 18, 2010
While creating ExceltoCI for acadmeic institution table I had entered value for Description say Indian Institute of Technology corresponding to Institution IIT , on submitting data it is saying value not found
Description field is neither promipt table nor lookup
0
programmer
written by Woj, February 15, 2011
I have read all of the posts a couple of times and found them to be helpful. I am using Excel 2007 with the ExcelToCI2007 component interface spreadsheet. I've been looking for the log that I expected to find in the C:temp directory, but can't find it even though I clicked the Generate Log option on the Login window. Secondly, I'm wondering why I get errors about entering values in required fields when those fields have data in them. Any help would be appreciated.
Deb Woj
CI gives error about required fields, but the fields are populated in the spreadsheet
written by Deb Woj, February 15, 2011
I created a CI in App designer, tested it there and then used the ExceltoCI2007 to create the data entry spreadsheet. I typed in 5 test rows and staged data for submission. Errors were returned for each line indicating that I'd left required fields blank. I posted the data and entered the required fields, but I continue to get the errors when I re-submit. I've tried to locate the log that I requested with login, but it is not in the C:Temp directory that I saw referenced above. Can you tell what I did wrong and where the log file is for ExceltoCI2007?

Thanks, ds
0
...
written by Syed Faheem Abbas, July 15, 2011
I want to add multiple programs for one application number on ADM_APPL_ENTRY1 Component. For this purpose I have created a new CI, by inserting new rows in program, plan and sub plan on CI template and repeat these rows for three times according to their template number. After the selection I got three times program, plan and sub plan columns on data input. But when I run this CI it generate some unknown errors, on the other side when I use this same practice on ACAD_HISTORY component for subjects studied in previous education this CI run successfully, So how can I create a CI for ADM_APPL_ENTRY1 component which can add multiple programs for one application?

Regards,
Syed Faheem Abbas
0
...
written by Syed Faheem Abbas, July 15, 2011
I want to add multiple programs for one application number on ADM_APPL_ENTRY1 Component. For this purpose I have created a new CI, by inserting new rows in program, plan and sub plan on CI template and repeat these rows for three times according to their template number. After the selection I got three times program, plan and sub plan columns on data input. But when I run this CI it generate some unknown errors, on the other side when I use this same practice on ACAD_HISTORY component for subjects studied in previous education this CI run successfully, So how can I create a CI for ADM_APPL_ENTRY1 component which can add multiple programs for one application?

Regards,
Syed Faheem Abbs
faheem
Question:
written by Syed Faheem Abbas, July 15, 2011
I want to add multiple programs for one application number on ADM_APPL_ENTRY1 Component. For this purpose I have created a new CI, by inserting new rows in program, plan and sub plan on CI template and repeat these rows for three times according to their template number. After the selection I got three times program, plan and sub plan columns on data input. But when I run this CI it generate some unknown errors, on the other side when I use this same practice on ACAD_HISTORY component for subjects studied in previous education this CI run successfully, So how can I create a CI for ADM_APPL_ENTRY1 component which can add multiple programs for one application?
0
PS Devloper
written by Abdul Rabb, November 15, 2011
Hi,
it was nice experince to see this much benificial info here..
I have also one question
" How we can update the EFFDT field by using EXCEL TO CI...."

By online application it can possible by using correct history but through Excel to CI it is giving error.

Thanks
Abdul
0
...
written by Joe Chan, December 07, 2011
I encounter a problem that when I click "Submit Data" button, an error message is prompted. It said "The response text is not valid XML. Login Data cleared." The login user's permission is already attached specified CI and WEBLIB_SOAPTOCI, they are granted full access. So I don't know the cause of this error message, can anyone solve it? (This ExcelToCI is in 2003 Excel version and I use it to upload data to a PeopleSoft 9.1 environment which deploys PT 8.52)
0
Nice Post
written by Prasannababu, December 14, 2012
Nice Post

Nice Explanation

Write comment

security code
Write the displayed characters


busy