FORMATTING THE CSV FILE FOR CE IMPORT
Page 1
1. Formatting Excel File for CE Import Process
If you receive student enrollment data as an Excel
spreadsheet it must be re-formatted as a comma
delimited or comma separated (CSV) file before it can
be used in the CE Upload process for Direct Certification
or Direct Verification.
TDA recommends that you use the file structure
described on page Error! Bookmark not defined. of
the Direct Certification User Manual
1.1 Save Copy of File
Save another copy of the file to reformat. A good file
name would include the month and year, a description
of what the data is, CSV (so it’s easy to distinguish
from the original file) and the date that the file was
prepared.
Example:
Month_SchoolYear_Student_Enrollment_CSV_YYMMDD
Aug_2016_Student_Enrollment_CSV_150806
Tip: Writing the date as YYMMDD makes it easier to
sort files by date created.
1.2 Right Columns/Right Order
Check that the columns are in the correct order:
• A - Social Security Number (SSN)
• B - Last Name
• C - First Name
• D - Date of Birth (DOB)
• E - Gender
FORMATTING THE CSV FILE FOR CE IMPORT
Page 2
• F - CE Identification Number
If there are any additional columns, delete the column.
Select the letter at the top of the column, right-click
and select Delete (Figure 55).
1.3 Format Social Security Number
Please note – The SSN cell cannot be blank. If you do
not have a SSN for a child, enter a number (0, or any
number) and format it as follows to have 9 digits.
• Select Column A (SSN)
FIGURE 1 DELETE COLUMNS
FORMATTING THE CSV FILE FOR CE IMPORT
Page 4
1.3.1 Find and Replace
If there are dashes or hyphens in the SSN, remove
them.
• Select Column A
• Go to Find and Select on the Home tab on the right
side of the screen (Figure 58)
• Select Replace from the drop-down list
• Type a dash (-) in the Find what field
• Leave the Replace with field blank
• Select Replace All. Select Close.
FIGURE 4 FIND AND REPLACE
FORMATTING THE CSV FILE FOR CE IMPORT
Page 5
1.4 Format Date of Birth
If you do not have a date of birth for a child, enter
1/1/1900 and format as follows.
• Select Column D. (DOB)
• Right click and select Format Cells. Select Custom
and enter mm/dd/yyyy under the word Type (Figure
58).
1.5 Format CE ID
• Select Column F (CE ID) .
• Right-click and select Format Cells. Select Special >
Zip Code (Figure 60).
• Note: You may have to change the locale to English
(U.S.) to get the 5-digit zip code.
FIGURE 5 FORMAT DOB
FORMATTING THE CSV FILE FOR CE IMPORT
Page 6
A common error is to fill the CE ID column all the way
to the bottom (1 million rows). The system will not run
the file if this happens.
Check to see if you have CE IDs going past the student
data.
If so, click in the 1
st
cell with a CE ID under the student
data.
Select the Ctrl + Shift + Down arrow keys at the same
time. This will select the column all the way to the
bottom.
With the mouse hovering over the selected cell, right
click and select Clear Contents.
FIGURE 6 FORMAT CE ID
FORMATTING THE CSV FILE FOR CE IMPORT
Page 7
1.6 Other Common Errors
Use the TEA County-District Code instead of the TX-
UNPS CE ID
Columns in the wrong order or leaving out a column
Formatting the CSV and then opening the file again
before uploading (It loses all the formatting)
Header Row (Delete the header row)
Blank cells
1.7 Check the File in Notepad
The best way to check for errors is to open the file in
Notepad. Right click on the file name in the Explorer
Window and choose Open with Notepad. (This only
works with the CSV file.)
It will be easy to see in Notepad when there are
commas and no data or spaces between text and
commas. Also, quotation marks are “invalid data” and
will keep a file from uploading.
FORMATTING THE CSV FILE FOR CE IMPORT
Page 8
FIGURE 7 CORRECTLY FORMATTED FILE
FIGURE 8 INCORRECTLY FORMATTED FILE
