Preparing Data for CSV-UTF-8 Imports

Preparing Data for CSV-UTF-8 Imports

When importing data into System Administration using a CSV-UTF-8 file, ensuring that the data is properly formatted is crucial for a successful import. This guide will cover what users need to know before attempting to upload data, along with common Excel-to-CSV dissimilarities that can cause import failures.

Before Beginning

1. Save File in the Correct Format: Always save the file as CSV-UTF-8 to avoid encoding issues. In Excel, this can be done by selecting Save As and choosing CSV UTF-8 (Comma delimited) (*.csv).
   
   • Common Mistake: A frequent error occurs when users rename an Excel file by changing the extension to .csv, without actually saving it in the CSV-UTF-8 format. While the file might appear to be a CSV, it remains an Excel file in the background, leading to failed imports and even potentially causing issues in System Administration’s import queue, affecting other users. Ensure that the file is saved using the proper format and not just by renaming the file extension.
2. Review Data for Consistency: Make sure all columns and rows are consistently formatted with the right data types (e.g., text, numbers, dates) to avoid mismatches during the import.
3. Check for Required Fields: Ensure that all required fields for import are populated. Blank or improperly formatted fields can lead to failed imports.

How CSV Headers Are Handled in System Administration

System Administration matches based on the headers contained within the CSV, not the file name itself. This means that even if the file has an unclear file name, System Administration will still be able to identify it as the appropriate data file as long as the correct headers are present.

• Headers: Headers are the column titles (e.g., ID, Name, Date) at the top row of the CSV file. For the import to be successful, the CSV must contain only the expected headers and must match exactly. If the CSV has extra or missing headers, or the names are not as expected, the import will fail.

This flexibility helps streamline the process, but it also means it’s crucial to ensure that the headers are correct and follow the exact required format to avoid import errors.

Common Excel-to-CSV Dissimilarities Causing Import Issues

1. Blank Rows:
   
   • Issue: If the Excel file contains blank rows that have not been fully deleted, the import process might recognize them as data and fail.
   • Solution: Delete the entire row, not just the contents, by right-clicking on the row number and selecting Delete.
     
2. Blank Columns:
   • Issue: Similar to blank rows, if the Excel file contains blank columns that haven’t been fully deleted, these columns may cause import errors or shift the data out of alignment.
   • Solution: Right-click on the column letter and choose Delete to fully remove the column.

3. Leading Zeros in ID Fields:

   • Issue: Excel may drop leading zeros in fields such as ID numbers when saving as CSV. For example, an ID like 012345 could become 12345. If re-imported into System Administration, this will result in incorrect data, as System Administration expects the full ID with leading zeros.

   • Solution: Format the column as text before entering or saving the data. This can be done by right-clicking the column, selecting Format Cells, and choosing Text to preserve leading zeros. 

4. Leading or Trailing Spaces:
   • Issue: Excel may ignore spaces before or after text entries, but these can cause data misalignment or fail validations when importing as CSV.
   • Solution: Use Excel’s TRIM() function to remove any leading or trailing spaces from the data fields before saving the file.

5. Date Formats:

   • Issue: Excel may display dates in the regional format, but when saving as CSV, the dates may be saved in a different format, leading to data mismatches.

   • Solution: Ensure that all dates are formatted in System Administration's required format (e.g., YYYY-MM-DD) before saving as CSV-UTF-8.

6. Em-dashes and Special Characters:
   
   • Issue: Excel may handle em-dashes (—) and other special characters without issue, but when saved as CSV, these characters may cause encoding problems, especially if not saved as UTF-8.
   • Solution: Convert em-dashes and other special characters into their plain text equivalents (e.g., hyphens instead of em-dashes) before saving the file as CSV-UTF-8.
   • Solution: Use Excel’s CLEAN() function to remove non-printable characters before exporting to CSV-UTF-8.
     
7. Different Field Delimiters:
   • Issue: Some CSV files use semicolons (;) or tabs instead of commas to separate fields, which could cause a misread of the data structure when importing.
   • Solution: Ensure that the CSV file uses commas as delimiters, or adjust System Administration's import settings to recognize the delimiter used.
8. Text Qualifiers:
   • Issue: Excel may enclose text fields with quotes automatically when saving as CSV, which can sometimes interfere with the import process.
   • Solution: Check the file in a text editor to ensure text fields are properly enclosed in quotes only when necessary.
9. Fields with commas: (e.g. middle name, some courses).
   • If there are commas then the field would need to be delimited with quotes

First Step: Test a Small Data Import

Before importing large files, it is best practice to first test the import with a small subset of data. This step will help to avoid major issues.

We recommend importing no more than 100 rows of data for each data type as your first import. This will help clarify the import process and ensure the formatting is correct, which will allow to user to more confidently handle larger datasets.

Uploading large files without prior testing can lead to failed imports, data loss, and significant time spent troubleshooting. Start small to avoid complications.