Forum Discussion
Excel Problem with Importing .csv file with carriage return in a field
Excel - Problem with importing a .csv file using the From Text on the Data tab, if a Carriage Return has been entered in a field encapsulated by “ “ it is producing a new row can anyone suggest a way forward that will prevent new rows form beinfg add after a carriage return?
Note we are using commas as the field delimiters.
3 Replies
GeoffCommdirect The only way I could replicate the problem is by using the text import wizard on a Mac. See first picture.
Just opening the csv file from within its folder/directory opens it without fault, but with a warning for "Possible Data Loss". But that's no problem. Just save as a regular xlsx file. See next picture.
In windows environment, importing via "Get & Transform", without any change in settings, causes no problems. See next picture.
And directly opening the file has a similar effect as on a Mac. No fault (just ignore the number formats in B and C) but the same "Possible data loss" message.
Are you on a Mac? If so, perhaps not "best practise" but in this case, just open the file, i.e. don't import.
Riny,
Thank you for the advice, however we currently use the Excel Data\From Text import as we have fields that contain greater than 15 numbers in a string normally 16 characters.
If we open the file in excel the row does not wrap after the carraige return, however excel converts any numbers after the 15th character to a zero , thus currupting the data we are trying to import.So the carriage return not being interpreted by excel in the import causes us a problem even though when import we have the Tesxt qualifier as " and the the file being imported uses " " to define the contents of the field.
GeoffCommdirect Yes, both methods for opening a .CSV file in Excel have issues. Numbers of 16 or more digits are truncated with zeroes if you open the .CSV file directly or carriage returns break records. if imported. Our workaround has been to replace carriage returns in a text editor (some handle very large files efficiently) with a unique string, such as zzzzzzzzzz, import the file using Data / From Text, and then process the column(s) containing the zzzzzzzzzz placeholders to replace them with carriage returns. Annoying, but effective. (Two-and-a-half years late, but may be useful for others. 🙂
