7 - Importing
If you have data in another database or in a spreadsheet that you want to import into the Online WBL Database, you may use the "Import from Excel" function. Reasons for using this function may include:
- You use a different database (such as MOSES or your own organization's database) for tracking job and internship placements for your program, but would also like to use the Online WBL Database to create Work-Based Learning Plans. In this case, you can import placement data into the Online WBL Database and then sign in and create job descriptions, skills and tasks and reviews.
- You have a spreadsheet listing new placements from a summer program or other program, and would like to import these into the Online WBL Database.
- You are converting to the Online WBL Database from another database system and want to import data.
- On some occasions - you may want to use an export and import to create a spreadsheet to review and update data in the database (less common).
The process for importing includes the following steps.
PLANNING THE UPLOAD
- Study the list of fields in the Online WBL Database and the fields in your database and decide what data you will import. You can read the "Tips - What Fields to Import" below, and the "Database Field List" in the SKILLSPAGES.COM/MASSWBL resource site.
- Decide what group of records (and what time period) you want to import and how often.
- The import routine is set up so that if your spreadsheet includes records that have already been imported the routine will not create duplicates - it can either update the existing records in the Online WBL Database or it can skip the records and not make any changes. (You specify which option you want.)Therefore, you should decide what fields you want to use as a duplicate check. Options include any combination of Start Date, Participant ID, Participant Name, Date of Birth, Employer ID, Employer Name, and Record ID (from the original database). Remember that participants may have more than one placement, and therefore Start Date or Record ID should be one of the fields used as a duplicate check.
- Decide if you need to include a "Staff Name" in your spreadsheet to facilitate sharing. Records that you upload will be saved under your username and visible to you and co-workers you share with. If you are uploading records that belong to someone else, you might need to include the StaffName, and at least once, the Staff Email (which is the username) in order to share these records.
CREATNG THE SPREADSHEET
- Create the spreadsheet with the fields that you want to import. Use a standard format, with field names along the top row and data in the rows below. Make sure there are no merged cells or extraneous data such as summary data in the spreadsheet; it should be just a worksheet with rows and columns of data. It is helpful if field names match the names in the Online WBL Database, but not necessary.
- Create a range name called ALLDATA in your spreadsheet. Here is how: (A) highlight the data, including the field names along the top row; (B) on the menubar click Insert, Name, Define and type in the name ALLDATA.
-- OR --
Make note of the range in the file, such as A1 to Z100.
- Save the spreadsheet with any filename using the Excel extension ".xls" (NOT .xlsx)
UPLOADING THE DATA
- Sign into the database at https://masswbl.org
- From the main menu, go to the Reports/Admin menu, scroll down, and find the button for "Import from Excel" which is in the third column of the list of reports and admin screens.
- The first step asks you to upload the spreadsheet. Just click Browse, find the spreadsheet on your computer, and then click the button to continue to the next step.
- The second step asks you to match up field names. If the field names in your spreadsheet match the field names in the Online WBL Database then the field names are already matched. If not, you can choose the corresponding field names from a dropdown list. Under each of your field names, there is a dropdown list with the field names in the Online WBL Database. Match up all of your fields, and then click the button to continue to the next step.
- The third step asks a series of questions about key fields and about duplicate record checks:
- Do you want to identify all records as "Connecting Activities", "YouthWorks", and/or "WIA Youth"?? The Online WBL Database has a series of checkboxes that identify program enrollment, including Connecting Activities, YouthWorks, WIA Youth, and other options. You can have the import process fill in an "x" for any of these fields for you. Other options are to leave these checkboxes field blank during the import and fill it in manually later, or to include these fields in your import process.
- Do you want to fill in an Activity or Program Name? If your spreadsheet did not include the "NameActivity" field, you can specify a program or activity name at this step.... such as "YouthWorks Summer 2010" or "Mill Valley School to Career Program."
- Do you want to fill in an Organization name? If your spreadsheet did not include an organization name, you can specify one at this step.... such as "Mill Valley Career Center" or "ABC Youth Program." This field is also useful for reporting.
- What fields do you want to use as duplicate checks? As explained above, you can include any combination of Date, Participant ID, Participant Name, Date of Birth, Employer ID, Employer Name, and Record ID (from the original database).
- If there are duplicate (already uploaded) records, do you want the import routine to update the Online WBL Database with the newly imported data or skip and make no changes?
- Once you have filled in this screen, click the button to continue to the next step. The data will be imported.
- The fourth screen provides a confirmation, with a list of the records that have been imported. The list will include the status of the record (New or Existing) and the status of the employer record (new employer or existing employer). It will list the participant name, employer name and start date. You can print this list or copy and paste it to Excel for your records, or just review it and then go back to the menu or sign out.
[SEE SCREEN PRINTS BELOW]
TIPS: What fields to include in your spreadsheet
Review the Database Field List to determine what fields to include in the spreadsheet that you will import. You can include any fields from the WBL1 and Employers tables except for PlanID, Username and Region, which are generated automatically.
Some points to know about the import process:
The import process allows you to include fields for Lastname and Firstname OR a single field for ParticipantName (which is usually in the format Lastname, Firstname).
The import process allows you to include a field for StartDate OR three separate fields for StartMM, StartDD, and StartYY.
The import process allows you to include a field for EndDate OR three separate fields for EndMM, EndDD, and EndYY.
The import process allows you to include the IndustryCluster field (which uses categories unique to this database) OR a NAICS code (which will be converted into an IndustryCluster).
You may include a RecordID from your database, if available, as a duplicate check to know if each record has been previously uploaded.
Other options for duplicate checking include Participant Name, Participant ID, Date of Birth, Employer Name, Employer ID, and/or StartDate. Therefore, if you have consistent Participant ID or Employer ID fields in your database you may want to include them and use them as duplicate checks.
The import routine allows you to specify certain fields at the time of upload - including the Connecting Activities checkbox, YouthWorks checkbox, WIA Youth checkbox, Name of Activity and Organization name. If you don't include these in your import, you can specify these fields as you upload.
Some key fields that are required or important for Connecting Activities reporting are Start Date, End Date, Hourly Wage, Hours Per Week, HasElementG, HasElementR, HasElementO, HasWBLP (Yes or No), Employer Name, Employer Type, and the checkbox for Connecting Activities, SchoolProgram (School name), SchoolCode (8-digit code), Graduation Year, Name of Activity, and Organization.
Connecting Activities reports are based on placements with start dates in the current year (July 1 to June 30) and so if a summer jobs starts in the last few days of June, it is usually entered with a July 1 start date. FYI, quarterly reports are cumulative (1st quarter reports include placements with a start date from July 1 to Sept. 30; 2nd quarter includes July 1 to Dec. 30; 3rd quarter includes July 1 to March 31; 4th quarter includes July 1 to June 30).
Key fields that are required for YouthWorks include the YouthWorks checkbox and all of the fields specified as "Required for YouthWorks" in the Database Field List.
There are several ways of sharing data in the Online WBL Database. Records that you import will be saved with your username, and you will have access to those records. You can list up to three people who you share records with when you create or update your username and account information. Or, you may share information by listing the STAFFNAME and STAFF EMAIL in each record. (Or just list the STAFF NAME, and enter the STAFF EMAIL once into the database, since it is stored in a related table.)
Here is a sample list of fields to include:
GradYear (four-digit year)
SchoolProgram (Name of School)
SchoolCode (8-digit ESE code)
EmplType (Private, Nonprofit, Public)
TypeOfPay (Paid, Unpaid, Sponsored)
HasElementG (Yes-College and Career Plan, Yes-Other, No, Unknown, NA)
HasElementR (Yes, No, Unknown, NA)
HasElementO (Yes-Academic Support Program, Yes-Internship Class, etc. (see database field list), No, Unknown, NA)
HasWBLP (Yes, No)
SCREEN PRINTS - IMPORT SCREENS