SFTP Syncs: Troubleshooting CSV formatting errors

This article contains the following:
1. Trailing commas
2. Extra commas in field values
3. Leading zeros

To avoid CSV formatting errors, we recommend exporting data from your SIS directly in CSV format, and uploading those files directly to Clever without manipulating them. Excel is commonly used to view CSV files, but it often imposes formatting that skews data and results in SFTP Upload Errors. For error-free viewing and saving of CSV files, we recommend using an application built for CSV compatibility, such as CSVEdit, Sublime Text, Apache OpenOffice, TextEdit, or Notepad.

Trailing Commas

Saving an Excel file as a CSV file can create extra commas at the end of each row. Trailing commas can result when columns are deleted or column headers removed. When the file is uploaded to Clever, an extra trailing comma will skew subsequent rows of data, preventing them from being processed.

To identify this issue, we recommend opening the file in a text editor (like Notepad for PC, or TextEdit for Mac). You’ll see trailing commas at the end of each row, like this:

Take these steps to resolve the issue:

1. Open a new, blank spreadsheet in Excel
2. Type in only the column headers for the data you will include in your upload
3. Save the file as MS-DOS Comma Separated with a new name (e.g., “students2.csv”)
4. Open the old file (with the extra commas) and copy the rows below the column headers
5. Paste the rows into the new file under your typed column headers
6. Save your new file as MS-DOS Comma Separated again
7. Once you’ve saved the data into the new file, you can close it and rename it to “students.csv” (the required file name for uploading to Clever)

Extra commas in field values

Your data may sometimes include a comma within a field value (e.g., if a student’s last name is “Potter, Jr.”), and the comma-separated values file will treat the comma as a field separator unless the entire field value is enclosed in double quotation marks.

The following record would NOT be processed to Clever. The three commas in the data row separate that record into four fields, but there are only three fields in the header row.

This record would get skewed to look like this:

The following record WOULD be processed to Clever. Enclosing the "Potter, Jr." field value in double (straight) quotation marks tells Clever that the entire last name is "Potter, Jr."

 
This record would be processed as:

Leading zeros

Numerical values (in fields like ‘school_id’, ‘student_id’, and ‘teacher_id’) often begin with “0”, but Excel removes these leading zeros, which changes the value listed for that field. For example, student_id “009844” becomes “9844”. When uploaded, Clever no longer recognizes that student: student “009844” is deleted and a new student, “9844”, is created.

If you need to open your CSV file in Excel, import your data as text to avoid dropping leading zeros:

1. In Excel, click Data > Get External Data > Import Text File…
2. Browse to select your CSV file and click ‘Get Data’
3. Choose “Delimited” as the file type and click ‘Next’
4. Check the “Comma” box as the delimiter and click ‘Next’
5. Select all columns in the data preview and change their column data format to “Text”
6. Click ‘Finish’