I want to export 6 Human Resource fields from PeopleSoft (Emp #, LastName, FirstName, Dept, etc.) and use it in an MS Access database. First, how do I do the export. Second, can the export be saved as a re-useable template. What formats can be exported (text file, access, dbf, ???) Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
PeopleSoft HR Export 1
- Thread starter evalesthy
- Start date
peterdozzi
Technical User
I would create an ODBC connection then use this connection to access the PeopleSoft database/tables through MS Access. Then either LINK the table or create an import from that table, although a LINK is more dynamic. If you link the table, you can then create a query in Access to grab those field.
Hope this helps.
Ruth
Hope this helps.
Ruth
-
1
- #3
Evalsthy,
You have several options available to you: 1) The option that Ruth mentioned of creating a direct ODBC link to the database, 2) creating a pass-through query to copy only the data you want to your access database, 3) use the PeopleSoft ODBC driver to pull the data you want via a PeopleSoft query, and 4) simply use PeopleSoft query to export a spreadsheet and import the spreadsheet into Access.
Now for the Pros and Cons:
Option 1: A direct link into the database. This is by far the simplest method. Of course with the good comes the bad. If you accidentaly change a piece of data in your linked table, it will also be changed automatically in your production database. Developers in my organization tend to avoid this option for the simple reason that it creates a potential security hole.
Option 2: A pass-through query. This is definitely a more secure option than option 1 (though still not the best). You would create a new query and instead of using the graphical tool for creating your query, you would choose the SQL view and code it yourself. You would then go up to the Query menu item and select SQL specific, and pass-through. You will need to fill in some ODBC information but this is typically not a difficult task. The nice part about using pass-through is that it is simpler to code complex subselects (like is used when retrieving the most current effective dated row from job data). You don't end up having a direct link to the database which prevents you from accidentally changing some production data. Of course you have circumvented your row level security with this option. Depending on the application, this may not be acceptable to you. In addition, there's the whole problem of putting the vendor's database ODBC drivers on the correct machines. If the access database is simply being used for developers that's not quite as big of a deal. Installing this on end users machines (and maintaing it) gets to be more time consuming.
Option 3: Using the PeopleSoft ODBC driver. Now this was a really cool idea the PeopleSoft came up with. You basically call a query that has been created with the PeopleSoft query tool. You get all of the features that you would get with the query tool: row level security, prompts, the works. The method for doing this is similar to the method for calling a stored procedure inside of a database. Unfortunately, this driver has had its share of struggles. Earlier versions of the driver (PeopleSoft 7 or earlier) would not allow you to export numeric values. You had to convert them to string values in PeopleSoft query. In addition, the call to the stored procedure had some sort of resource allocation problem. You were limited on the number of times you could call the "stored procedure" then you would have to close your connection to the ODBC driver. I haven't seen how recent versions of the driver operate so they may have fixed some of this in version 8.
Option 4: Export to Excel, Import into Access. This is the method we use at our shop. It gets developers out of the support loop. In addition, most end users that are savy in Access cut their teeth on Excel. The technique is very straight forward. You execute your query inside of PSQuery and export the results to Excel. Here is the only "gotcha" in this technique. Excel version 5 and earlier can only have maximum of 16,000 rows. NVision by default creates the exported spreadsheet in Version 5. If you have a large amount of data being exported, you must be sure to save the spreadsheet in a later version of Excel or you will loose data. As for the import into Access, Excel is one of the provided file types. The wizard walks you through the process of importing the spreadsheet into access.
Hope this helps
Rekclaw
You have several options available to you: 1) The option that Ruth mentioned of creating a direct ODBC link to the database, 2) creating a pass-through query to copy only the data you want to your access database, 3) use the PeopleSoft ODBC driver to pull the data you want via a PeopleSoft query, and 4) simply use PeopleSoft query to export a spreadsheet and import the spreadsheet into Access.
Now for the Pros and Cons:
Option 1: A direct link into the database. This is by far the simplest method. Of course with the good comes the bad. If you accidentaly change a piece of data in your linked table, it will also be changed automatically in your production database. Developers in my organization tend to avoid this option for the simple reason that it creates a potential security hole.
Option 2: A pass-through query. This is definitely a more secure option than option 1 (though still not the best). You would create a new query and instead of using the graphical tool for creating your query, you would choose the SQL view and code it yourself. You would then go up to the Query menu item and select SQL specific, and pass-through. You will need to fill in some ODBC information but this is typically not a difficult task. The nice part about using pass-through is that it is simpler to code complex subselects (like is used when retrieving the most current effective dated row from job data). You don't end up having a direct link to the database which prevents you from accidentally changing some production data. Of course you have circumvented your row level security with this option. Depending on the application, this may not be acceptable to you. In addition, there's the whole problem of putting the vendor's database ODBC drivers on the correct machines. If the access database is simply being used for developers that's not quite as big of a deal. Installing this on end users machines (and maintaing it) gets to be more time consuming.
Option 3: Using the PeopleSoft ODBC driver. Now this was a really cool idea the PeopleSoft came up with. You basically call a query that has been created with the PeopleSoft query tool. You get all of the features that you would get with the query tool: row level security, prompts, the works. The method for doing this is similar to the method for calling a stored procedure inside of a database. Unfortunately, this driver has had its share of struggles. Earlier versions of the driver (PeopleSoft 7 or earlier) would not allow you to export numeric values. You had to convert them to string values in PeopleSoft query. In addition, the call to the stored procedure had some sort of resource allocation problem. You were limited on the number of times you could call the "stored procedure" then you would have to close your connection to the ODBC driver. I haven't seen how recent versions of the driver operate so they may have fixed some of this in version 8.
Option 4: Export to Excel, Import into Access. This is the method we use at our shop. It gets developers out of the support loop. In addition, most end users that are savy in Access cut their teeth on Excel. The technique is very straight forward. You execute your query inside of PSQuery and export the results to Excel. Here is the only "gotcha" in this technique. Excel version 5 and earlier can only have maximum of 16,000 rows. NVision by default creates the exported spreadsheet in Version 5. If you have a large amount of data being exported, you must be sure to save the spreadsheet in a later version of Excel or you will loose data. As for the import into Access, Excel is one of the provided file types. The wizard walks you through the process of importing the spreadsheet into access.
Hope this helps
Rekclaw
- Status
Similar threads
- Locked
- Question
- Locked
- Question