Error importing a CSV file

Summary

Some CSV files contain new line characters (Carriage Return, Line Feed, etc.) that are not supported by the importer. Usually, these characters are included in the Address or Notes fields.

Resolution

Remove the new line characters manually, by using Find+Replace in Google Sheets, or by using the Excel macro method. 

Find+Replace Method

1. Open the file in Google Sheets.

2. Click Edit->Find and Replace.

3. Type or paste \r\n|\r|\n into the Find field.

4. Add a comma and a space to the Replace field.

5. Select the range you'd like to edit.

6. Select "Search using regular expressions"

7. Click Replace All.

Excel Macro Steps

1. Open the file in Excel.

2. Select the data from which you'd like to remove new line characters.

3. Create the macro using the Visual Basic Editor (Tools > Macro > Visual Basic Editor) and insert a Module for that sheet.

4. Paste the following code into the new Module.

Sub RemoveCarriage()'Update 20131216Dim Rng As RangeDim WorkRng As RangeApplication.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualOn Error Resume NextxTitleId = "KutoolsforExcel"Set WorkRng = Application.SelectionSet WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)For Each Rng In WorkRng Rng.Value = Replace(Rng.Value, Chr(10), " ") Rng.Value = Replace(Rng.Value, Chr(13), " ")NextApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticEnd Sub

5. Save the Module and return back to the Excel file

6. Run the Macro (Tools > Macro > Macros) Note: Make sure you only select the data that needs to be updated

6. Save the CSV file and re-import the file

 The above code scans through the selected cells and replaces any Line Feed (LF) and Carriage Return (CR) with a space.

Was this article helpful?
2 out of 3 found this helpful

Have more questions? Submit a request

Start free trial