Product Navigation

Guide to Repair CSV File

CSV file corrupts due to invalid formatting, use of invalid characters, line breaks in fields and other reasons. We will explore about causes of CSV file corruption, import errors and rules of creating CSV file and solutions to recover comma separated value file.

What is CSV File?

Comma separated values (CSV) is a file format used to store alphanumeric tabular structure data in plain text format . File contains data record and each record is a collection of two or more fields separated by commas. CSV files can be imported or exported from software programs that store data in row column structure, such as Microsoft Excel or Open Office Calc.

csv file

Specification :

  • Plain text file using character set such as ASCII and Unicode.
  • Each line of CSV file is a data record.
  • Each record contains fields separated by delimiters such as comma, semicolon, tab or space or other characters.
  • In CSV file each record has fields in same sequence.
  • All records have same number of fields.

Causes Of CSV File Corruption

  • Non-printable ASCII control characters like CR or LF at the end of line.
  • The CSV contains separator characters in the text. Eg. semicolons. The parser treats delimiter as an additional column.
  • Line breaks given in the fields without enclosing with double quotes.
  • Use of invalid characters like (-) hyphen, () parentheses, (.) period, colon(;), Asterisks (*) and other characters.
  • Blank lines at the end of CSV file that contains separator.
  • Long numbers with leading zeros.
  • Invalid CSV file format.
    To overcome the causes of CSV file suer need to repair CSV file.

CSV File Import Errors

  • Use of extra characters in the file.
  • Handle field has spaces and invalid characters.
  • Last field in the record is followed by comma.
  • In case double quotes are used to enclose all the fields ,then double quote appearing inside the field is not preceded by another double-quote.
  • Two or more records in a file is located on same line in a file.
  • CSV file is not using UTF – 8 encoding.

Solutions To Repair CSV File

Solution : To Resolve issue CSV file is not UTF-8 encoded

  • Open CSV in Excel.
  • Click File > Save As.
  • Save As dialog box opens up.
  • Enter the file name.
  • Select from drop-down "Save as File Type" CSV (comma -delimited).
  • Click Tools button at the bottom of dialog box and select Web Options.
  • Go to Encoding drop-down under "save this document as" and choose Unicode(UTF-8).

Solution : For problem of spreadsheet software corrupting long numbers with leading zeros to recover CSV file

Don't save the CSV file in case numbers are not displayed as expected. Use any of the two options to solve the problem :

  • Option 1: Use some other software to open CSV file.
  • Option 2 : Follow steps to Import File in Excel and repair CSV file
    • Open new workbook in Excel.
    • Go to –Data Tab– and select –From text– option in Get External Data section.

      external-data

    • Browse CSV file and click import.
    • Choose Delimiter and click Next.
    • Select Comma, click Next.
    • Select column containing numbers and choose "Text".
    • Repeat steps 6 for all columns containing numbers.
    • Click Ok.
    • Follow the above steps whenever file is opened in excel.

Solution # Foreign language characters are corrupted or missing

  • To handle this error use Google Sheets, OpenOffice,LibreOffice ,Notepad++ advance applications to Repair CSV file.

Solution # For error Import error message: Line 1 has too many columns (xx) max is 15

  • Create a new CSV file with data in 14 columns using excel, notepad++ or other applications and recover Excel CSV data.

Solution # CSV corrupts due to use of ASCII control characters, special characters or extra delimiter characters

To repair corrupted CSV file follow steps :

  • To solve issue of special characters, control characters use excel.
  • Delete the spaces from end of file.
  • Fix CSV file extra commas error by deleting the extra delimiter characters used to separate the fields and retrieve CSV file data.

Solution # Create CSV file in different editors by following rules to repair CSV file :

Using Notepad (or any of the text editor)

  • Enter the text data in file with each field separated with comma.
  • Separate each row with new line.

    csv rule

  • Save this file with .csv extension

Using Excel Spreadsheet

  • Enter the data in excel sheet.
  • Click File select Save As option.
  • Choose Save As Type " CSV (Comma delimited)".

Follow Rules For Creating CSV File & fix Corrupted CSV file

  • Individual field in file is separated with comma but if data has comma then enclose data field having comma with double quotes (")

    csv repair rule

  • Double quotes are part of data field then quotation mark should be preceded by data field double quotes.

    csv rule 2

  • Each record should be located on separate line.

    rule 3

  • Data Fields containing line breaks, double quotes or commas should be enclosed with double-quotes.
  • Each and every row should have same number of columns.
  • Separate data fields with a delimiter character , usually a comma. Always use a single character as delimiter.
  • In the first line of the file, include a header with list of the field names in the file. This is optional but it is strongly recommended for self-documentation.
  • Acceptable CSV Mime Types :
    • Application/octet-stream
    • Text/CSV
    • Text/comma-separated-values
  • Avoid leading and trailing white spaces.
  • Fields having line breaks should be enclosed with double quotes.

Using these rules check CSV file for errors and restore the crucial data.

Conclusion

The above given simple solutions repair CSV file. But Sometimes these solutions fail to rebuild data from CSV file then in that case user should use professional tool to recover CSV file. To recover deleted, overwritten, unsaved, corrupted, previous version of CSV file use a third party tool. User should follow rules while creating CSV file to avoid file corruption errors.