File Parser Tips and Tricks
So you’ve got this list of names/test scores you want to load into PeopleSoft, and you’ve figured out how to set up File Parser. Great! File Parser is a great tool for loading all sorts of data. It’s configurable (even customizable but SHHH! We don’t like that word!) But you can make everyone think you are a File Parser expert using it right out of the box if you just know a few little tips and tricks. This blog will not show you how to set up a basic File Parser, but it will show you how to enhance it to your advantage.
Tip/Trick 1: Your prospect list has SOME student ids (Emplids) but not all
It is quite possible to load a list of prospective students where you have Emplids for some students, but not for all of them. You can just “trick” File Parser into knowing when to set a record to “Update Id (UE)” and when to set it to “Loaded (LD)”
- In your Conversion Definition, create a Field Conversion Definition for Constituent Staging Status with the Conversion Field being your Student ID/Emplid field. Make sure to set a default value of “UE” for Update/Edit and enter a row with a blank value for the Field Value with a corresponding Internal Value of “LD” for Loaded.
- In your File Mapping Definition, add the Field Conversion to SCC_STG_STATUS on record SCC_STG_CONSTIT. File Parser will look at your incoming Emplid field, and for everything that has an entry (the ones where you have an Emplid) it will not find a corresponding conversion, so it will set to the default constituent status of UE. Anything with a blank for Emplid (the ones where you don’t have an Emplid) will be set to LD and be put through search/match.
- CAVEAT: You must be VERY sure that the Emplids in your incoming file are correct. If they are incorrect, sometimes they will be left in staging and not updated because there is no actual Emplid in PeopleSoft to match on in your incoming file. But it is possible to post data to the wrong person if the ID does exist. So buyer beware. But this is a good way to solve an integration problem between say, a CRM and PeopleSoft. If you’ve pulled Emplids from PeopleSoft to your CRM and need to update from the CRM back to PeopleSoft, it’s a good way to skip search/match in PeopleSoft for the ones you know and keep from potentially creating duplicate records.
Tip/Trick #2: I want to put a list of students into a student group once they are loaded via File Parser
Process Student Groups is like a population update in that you can identify who to put in the group with either an external list that you manually upload (not great for automation), a query to identify the records to select, or an equation engine. If you are jumping at the bit to write a new equation engine, then you probably don’t need this blog, but if you’d like to automate your student group, then keep reading! Without this trick, you would have either take the external list approach once you search/match/post your file parser job, and export to a csv file then manually upload. If this is a one-time data load, it might be a good solution. But for repetitive loads, no one wants to be in the business of manually loading student groups. The other alternative would be to keep all of your file parser loads in staging tables and never purge them, and write some sort of query to pull them as part of your Process Student Groups process. Queries are problematic for repetitive file parser loads, unless you can clearly identify who you loaded in each process. I have found performance can be impacted as staging tables grow as well, so that is not an optimal solution either.
Routine automated jobs would have less required intervention than external lists or querying staging tables. If only there were some way to flag records that you loaded in a stored field that you could query later in an automated student group update….. Here, Common Attributes are your friend. Basically, you load a common attribute with a Yes/No value, or some other type value that you can query later as part of your initial File Parser data load. Then you write a query to pull all people with that flag, who aren’t yet in your student group (don’t forget the STDNT_GRP_BIND record). Then you use Process Student Groups to add your student group to anyone in your query. You can even automate this through Process Scheduler. You can purge records from staging at your leisure.
Tip/Trick #3: I need a File Parser calculation that exists in another file mapping definition. Do I have to get my IT folks involved?
It really depends here. I’m going to describe a way that you can hijack a delivered calculation without any IT involvement. But make sure before you proceed that it really is the same calculation that you need. For example, there are delivered mapping files for prospects that provide calculations to strip spaces and characters from names to help with search match. Oracle has delivered a mapping definition for SAD_CFA_PROSPECTS to load prospective students from College Fairs. On the SCC_STG_NAMES record of this delivered mapping definition, there are staging fields for Last_Name_Search and First_Name_Search with calculations:
The calculations at the end of the row are:
You can hijack these calculations in any mapping definition. There is only really one rule to keep in mind:
- Oracle’s delivered calculations specify the fields to use by their sort order in the File Layout tab.
That means that if we look at SAD_CFA_PROSPECTS mapping definition File Layout tab, we find that LAST_NAME_SEARCH and FIRST_NAME_SEARCH have sort orders of 65 and 45 respectively:
So long as you use the same sort orders on your Last Name Search and First Name Search fields in your File Mapping Definition, you can use these same calculations, exactly as specified for SAD_CFA_PROSPECTS even if you don’t use SAD_CFA_PROSPECTS as your Context Definition:
This will strip out non-alphabetic characters and spaces to use in search/match.
Tip/Trick #4: I cloned a delivered mapping definition, but it doesn’t have some records I need. What do I do?
Let’s say you are loading a prospect list you received, and you would like to give them all a recruitment category. There is no batch process for loading recruitment categories, and you don’t want to have to do it manually. Never fear! There are many delivered staging tables you can add to a file mapping definition. First though, you’ll want to start by adding the staging record to your Context Definition. If you’ve cloned that, and the original does not have the record you need, just add it to your copy. That will make the record available to you in your file mapping definition. Recruitment categories do have a staging table (SAD_PRS_RCR_STG), but it isn’t available in the Context Definition for SAD_CFA_PROSPECTS. You’ll need to know what the parent record should be (in this case SAD_PRS_CAR_STG). Also, before you go too far down this rabbit hole, look at other delivered Context Definitions and you might find one with the records you need. There are two ways to do that: looking at other delivered Context Definitions, and looking in the Entity Registry. When you look at other existing Context Definitions, SAD_ACT does use the record SAD_PRS_RCR_STG. You can use that Context Definition even when you are only loading prospect data and not any type of test scores. If you have access to the entity registry, you can see all staging tables that currently exist by search for staging record “SCC_STG.” This allows you to browse.
My recommendation is to always look for things that have most of what you want, and then add what you still need. I have only rarely had to recreate or clone Context Definitions beyond the initial clone for initial setup. Additional modifications to existing setups can usually be accomplished by adding what you need to your existing Context Definition.
CAVEAT: If you DO need to create a cloned/edited Context Definition, think it through BEFORE you start editing your File Mapping Definition. You can go back to the existing Context Definition and add records without too much pain. But if you change the Context Definition for your file mapping definition any file mapping you have done will be wiped out and you will have to start again. As in most of life, a little preparation and planning before you start will save you pain in the end.
Where Do I Find More Information?
11.20.19 Leveraging the Power of File Parser
File Library: HEUG Education Series - 2019 E-Academy Series | By: William Culey | Nov 20, 2019
Leveraging the Power of File Parser Let's take a look at the powerful tool File Parser and what it can do... for your institution. We will go over some basics of setup and then dive into how it is being used... by WSU in a variety of ways to solve day-to-day problems. Let's find solutions together as we learn... about this amazing tool and how it works. Speaker(s)William Culey, System Admin, Washington State University...
File Parser- How to make the system to work for you
File Library: US Alliance Archives - 2018 NWHEUG | By: William Culey | Aug 01, 2019
The presentation will go over the basics of file parser and ways that WSU uses it to streamline and automate menial tasks that were once manual entry.
Abbott and File Parser - Who's Loading First?
File Library: US Alliance Archives - 2018 SEARUG | By: David Ehrlich | Sep 24, 2019
A discussion and description of how PeopleSoft and Slate Admissions data play ball. Hear about how File Parser was utilized to send data around the bases... and help keep the systems in shape. Hear about the lobs and curve balls.
Avoid Carpal Tunnel: Streamline with File Parser
File Library: Alliance Virtual 2020 - Student Records | By: Scott Nishizaki | May 21, 2020
This session is not just for Student Records! The Oracle delivered File Parser utility allows for data stored in a file to be directly... a file parser from concept to production. During... is using filer parser, to import transfer credit rules. We will also discuss... loaded in to PeopleSoft records. Even better, both the set up and utilization can be done by the functional... a few other ways Azusa Pacific is making use of this tool to reduce manual entry time.
How to Create and Use Reusable Calculated Fields for File Parser Loads
File Library: Alliance Archives by Track - Alliance Archives: Admissions | By: Rachel Leah Sullivan | Apr 29, 2020
upon loading a file or in the staging pages, and consistent loading of data.In...Developing reusable calculated fields is one way to reduce the amount of time that it takes to create and test brand new PDL loads. Other benefits include one-time development effort, fewer errors to resolve... this session, we will discuss the following:? Factors to use when determining if a Calculated... Field is reusable across multiple loads.? Defining the requirements for a reusable Calculated Field... Calculated Field for standard name formatting in each of the name part fields.
11.19.20 Updating Transfer Credit Rules Using File Parser
File Library: HEUG Education Series - 2020 E-Academy Series | By: Sarah Bryan | Nov 03, 2020
Updating Transfer Credit Rules Using File Parser Join us for a summary of how Azusa Pacific University uses file parser... to update Transfer Credit Rules (and you can too). We'll talk about File... Parser in general, the Transfer Subject Area records, considerations..., and implementation steps. Speaker(s)Scott Nishizaki, Azusa Pacific University
Growing Pains to the re-design of electronic test score data transfer processes
File Library: Alliance 2018 - Alliance 2018: Admissions | By: Desiree Abohasen | May 08, 2019
processes for ACT, SAT, TOEFL, CLEP, PERT, and FTCE utilizing File Parser... File Parser custom setups build. This session will benefit...Miami Dade College has implemented over the years electronic data transfer processes to load and post students? test score data from third party agencies. With the transition from our legacy Enterprise... system into PeopleSoft, MDC completely re-design all existing electronic test scores data transfer....
NO MORE PAPER TRANSCRIPTS
and use file parser process to load/post transcript...Lone Star College has been working on a process to convert incoming paper transcript to csv file for PeopleSoft Campus Solutions' consumption. Lone Star College is leveraging Hyland...?s OnBase Optical Character Recognition (OCR) engine to read data elements from paper transcript... data into PeopleSoft core tables. During this presentation, Lone Star College will showcase the overview... of the process, lesson learned, challenges, and future enhancements.
Using PDL for Admissions Data Loads
set up of File Parser to load admissions applications...? Understand the delivered functionality of PDL and File Parser... files. Specific examples of the set up and configuration of the file mappings...This presentation will cover the functional setup and configuration of the Prospect Data Load (PDL) functionality to load admissions specific data. It will also discuss how to use the delivered test score...? Provide learning opportunities and tips and tricks related to data loads