Helpful Excel Formulas and Tips to Format CSV Data Imports

When setting up a CSV Data Import, you may encounter data mapping issues when taking information out of one system and importing it into Faculty Success, as different systems tend to capture the same data in different ways. You can speed up your CSV preparation and improve data integrity by using some simple functions and formulas in Excel.

Creating Flat Files to Import DSA Data

Some screens contain Dynamic Sub-Answers (DSAs), which can be instantly imported as long as the formatting requirements are met. You should keep each record on one row, and repeat any fields that need to hold more than one sub-record as additional columns, as pictured here:

In the example above, taken from the Yearly Data screen, we repeated the fields within the College/Department DSA in order to include all College and Department assignments for a given publication in a single row of the CSV file.

Note that you can repeat the fields contained in a DSA as many times in your data import file as needed; however, each of the headers in your data import file must be unique within in that file. To differentiate between sets of DSA fields, simply append numbers to the headers within each set.

Formatting for Importing Checkbox List Values

 Data associated with Checkbox list values can be instantly imported under the following formatting standards: 

When updating a record with multiple Checkbox values, separate each value with a pipe delimiter as shown in the example above. As with any other field, these values will need to be placed under the column that contains the appropriate field code. Note the system will mirror what is in the file so if you are updating data, the cell has to have the existing values listed as well, or else the checkmark will be removed on the screen.

Formatting ID Columns to Remove Scientific Notation and Avoid Truncating Values

This is an Excel formatting problem that most of us have run into at some point. You want to paste a long number into a cell and Excel truncates it to something like ‘1.79E+11’. This happens because when Excel sees long numbers, it tries to format it using scientific notation (or the ‘E’ notation). Even if you save the file as CSV and open it up in a text editor, it will still show as ‘1.79E+11’.

This is an easy problem to fix. You need to select the cell, and then format that cell as a number with no decimal places. Here's a short video to illustrate how to address this issue.

Find and Replace

One of the simplest ways to edit your information quickly is to use the Find and Replace tool. This can be particularly helpful for drop-down list fields. In Faculty Success, values provided within an import file for a drop-down field must exactly match the predefined options for that field. For example, your source information may use a code of UG to indicate Undergraduate, but in Faculty Success, your drop-down list uses the full word.

1. Select the column for which you want to change values.
2. Open the Find and Replace tool by typing Ctrl+F and selecting the Replace tab, or select Edit>Replace in the Home Tab. 
3. Enter “UG” in the “Find what” text box.
4. Enter “Undergraduate” in the “Replace with” text box.
5. Select Replace All.

The Replace tab appearance may differ based on your operating system and version of Excel.

Title-Case, Lowercase, Uppercase

Several formulas exist for adjusting the format of words and phrases from your source system.

1. PROPER capitalizes the first letter of each word and makes the rest of the letters lowercase.
2. UPPER capitalizes all letters.
3. LOWER changes all letters to lowercase.

Date Formulas

Other formulas can be useful if you need only a portion of a date stored in a cell. In Faculty Success, date fields are often broken apart into components, such as term and year or month, day and year. Your dates must be parsed into the relevant columns and formatted accordingly.

1. YEAR returns the year portion of a date.
2. MONTH returns the month portion of a date.
3. DAY returns the day portion of a date.

Split a Value or Combine Values

Sometimes you may need to split apart text in your information, similar to taking a portion of the date. Each column of your CSV data import file must contain a different piece of information, corresponding to a single input field in Faculty Success. If your source information combines some of that information, you may need to separate it and these formulas will help you.

1. LEFT returns the number of character you specify starting from the left end of the value.
2. RIGHT returns the number of characters you specify starting from the right end of the value.
3. MID returns the number of characters you specify, given a starting position and length.

You may also encounter a situation where you need to combine the information from different cells and/or add static values to your values. Use the CONCATENATE formula to do this. Note that if you want to add static values or spaces to your information, you may, but they must be enclosed in quotes.

Leading Zeroes

Because we require that you save all import files in comma-delimited (CSV) format, leading zeros are a common issue, particularly in Scheduled Teaching imports. If a course has a section number that begins with a zero, that section number may need to be padded to ensure the course number is represented correctly. 

One way to ensure section numbers have the appropriate number of leading zeros is by changing the format of your cells. In Excel, select the column that contains section numbers and apply a custom format (from the drop-down in the Number section of the Home Tab), using zeros to indicate how many total digits the number should be. Excel will add zeros to the beginning of any number with less than this number of digits. 

Duplicate Detection and Deletion

If you are concerned that there may be duplicated information in your source information, you can use the Remove Duplicates tool found in the Data Tools section of the Data Tab.

1. Highlight the rows and columns you want to evaluate for duplicates.
2. Select Remove Duplicates.
3. The Remove Duplicates window will open and you can make selections to indicate which columns need to be the same in order for a record to be considered a duplicate. In most cases, you will leave all the columns checked.
4. Select OK to remove all the duplicated rows in your information.

Delete Columns Used in Formulas

When you use formulas to modify your information, you may need to take some additional steps prior to saving your file.

1. Highlight the columns that contain formulas in the file.
2. Copy and paste as values (you can paste right over the existing columns).
3. Select any columns that have information that was used in your formulas that is no longer needed (for example, the cells that contain the entire date, when you needed only a portion of it).
4. Right-click and select delete.
5. Save the file as a CSV.