-2

I have a file that is extracted from a system in a format over which I have no control. It is CSV (UTF-8) and there is a column that contains carriage returns (I'm not sure exactly what they are, but they were originally uploaded from an Excel file with information in the same column but with CTRL+ENTER to change line in the column).

Excel interprets them and changes line, so it creates a new line that does not fit with the headers and is not handled properly when the file is saved as a .csv file again. The text is within text delimiters ", so I'm guessing there should be a way to specify the handling of the text inside this delimiter.

I know there's probably no easy way to do that, but is there some VBA code I could use to handle this?

Here is an exemple of what it looks like when it is saved as a csv :

HEADER, GoalLibraryEntry, GUID, PARENT_GUID, LOCALE name, etc.

ADD, GoalLibraryEntry, 710103, 710100, en_US, "Test:,,,,,,,

a) Test1,,,,,,,,,,,
b) Test2,,,,,,,,,,,
c) Blabla,,,,,,,,,,,
d) Blablabla,,,,,,,,,,
e) Test5",,,,,,,,,,,

My problem is very similar to this one : Generating CSV file for Excel, how to have a newline inside a value

However, I've tried saving in ANSI before opening it in Excel without success. I also tried to add UTF-8 BOM at the start, but Excel does not seem to interpret it in any way.

Community
  • 1
  • 1
JSCoulombe
  • 1
  • 3
  • How is it handling them? How do you want to handle them? Keep in mind that I can't see your worksheet or what you mean by, "it creates a new line that does not fit with the headers." – Marc Oct 07 '15 at 19:33
  • What do you mean by it creates a new line? do you mean it creates a new row for the data following the carriage return? Could you post an example of what it is doing. and what you need it to do? – Clint Street Oct 07 '15 at 20:01
  • Sorry, I need this to be in only one line so that it can be imported corrrectly back in the system. What I mean by "does not fit with the headers" is that there are now empty columns created to fit the number of columns in the csv file. – JSCoulombe Oct 07 '15 at 20:30

1 Answers1

0

What you're most likely seeing is multiline cells made when the user does a alt-enter on the keyboard.

The function you're looking for is clean (eg.) which you could use in VBA.

=clean(a1)

This removes non-printable characters from a cell.

ergonaut
  • 6,929
  • 1
  • 17
  • 47
  • I've read about that function, but I don't need to remove the characters, I need them to stay there but not create a new row. The file is then reimported in the system and must contain this information for display purpuses in the system. – JSCoulombe Oct 07 '15 at 20:34
  • There's probably something you're not telling us. What does the csv look like? I've exported one with cell line breaks, and it looks like "foo^Mbar" and it imports fine, and still shows the cell line break. – ergonaut Oct 07 '15 at 20:43
  • It looks a little bit like this : Row 1 - Header1, Header2, Header3, Header4. Row 2 - Content1, Content2, Content3 : Row 3 - Content3 continuity Row 4 - Again Content3, Content4 – JSCoulombe Oct 08 '15 at 15:23