How to make linebreaks in an import - i.e. text for the "Description" field (TinyMCS)?

Hi good people.

How can I format my text for the "Description" field in my csv file so that I get a nice text with line breaks once I´m done importing into Sugar?

Here is my attempt in the csv file:

that produces this results:

As you can see, the <br> was stripped out but did not produce a new line. Get the same result with <p>, i.e. no new line.

Thanks,

KGM

Parents
  • I would suggest adding \r\n instead of <br> and/or <p> into the text that you are importing from. I think that should give you the expected results.

    The field description in your emails_text table is what you see on the screen when you open up to read an email. To insert a linebreak into an sql table, you need \r\n\

  • Excellent idea, Viktor Ranga  Tried it but still no line break:

    Any other ideas?

    Thanks, KGM

  • Most likely there is some code that automatically escapes the \r\n during the import. Can you query what you see in the database in emails_text for this record?
    Query should be:

    SELECT description FROM emails_text WHERE email_id IN (SELECT id FROM emails WHERE id = 'id here');

    Replace id here with the actual uuid of the email you are viewing. I.e. http://sugar_ip/sugar_installation_folder/index.php#Emails/the_id_is_a_long_string_of_numbers_and_letters_here

  • Hi again Viktor Ranga 

    I´m actually using the Description field of a Lead record, not email. I.e. I am importing leads into Sugar and want to be able to format the "Description" field of those Leads.

    Thanks, KGM

  • In this case a similar principle still applies, you just need a slightly different query to check your result:

    SELECT description FROM leads WHERE id = 'the id of the lead here';

    If you could grab this result, we can check how it looks in the Database, and then we can work out the appropriate fix.

    My suspicion is that in the database, you will see \\r\\n (referring to your latest screenshot here) but it would be nice to have it confirmed from your end.

  • No Viktor Ranga, it was the same (my Linux admin)  :/  But I found a way to do this.

    By entering return in the CSV document with alt-enter.  This CSV:

    results in:

  • Kristjan Geir Mathiesen Viktor Ranga,

    You will find that the values in the database are the escaped version of the entry. So in the DB you should see the text "\r\n" (which I think you do). The reason is the the CSV file thinks this is what you want to enter, it is a text file so copies what you write. It will be escaping the entry at source so that it copies into the DB as you typed. It has done that

    Adding line breaks in a text file for CSV by hand is complex unless you use sed or awk etc. All GUI text editors (including Excel) will automatically try to escape anything you put that is a special character. The easiest way is how the OP managed it, by simply using the Enter or Return key on the keyboard and having the text enclosed by "" (Excel does this for you).

    If you are going to try to construct the CSV by hand adding the line breaks manually, then there are a couple of things to consider. Firstly, the three main OS'es all use different character sets for the line break. Windows uses the code you have above, \r\n ("carriage return" + "new line" - think about how an old typewriter works mechanically!), Linux and other Unix deriviatives use \n (just "new line") apart from MacOS (also a *nix) which uses \r (just "carriage return"). Secondly, you will need to make sure you understand how your operating system works and with which operating system the resultant CSV file will be imported.

    Unfortunately, what this means is usually a CSV file created on a Mac cannot be imported using tools on Windows or Linux system as the line breaks just don't work (Linux ignores the \r and Windows doesn't put the new line part!). If you create a CSV on Linux then you can import it using Windows as the \n completes the line break command and the import just looks for that. If you create it on Windows then you should be able to import it with either a Mac or Linux device as both of their line breaks are represented

    Confusing? Only if you don't find the following funny...

    There are 10 types of people in the world. Those who read binary and those who do not.

    Despite that I still get Christmas and Halloween mixed up as to me 25dec === 31oct

     

    JH.

  • Interesting point about windows/excel. I have not considered that someone might be using windows (or Excel) to manage csv's in a general sense... Thanks a lot for the insight!

    Although

    You will find that the values in the database are the escaped version of the entry. So in the DB you should see the text "\r\n" (which I think you do).

    This hasn't been the case for me. If I want \ to appear in the DB, I have to add \\ in the code. (On Linux) Although I use a custom perl script to add certain records to the DB, so this might explain why it didn't work like it works with Excel.

  • Viktor Ranga

    You are right about having to escape the \ character if you are using any form of scripting - which is why I excepted tools such as sed and awk (and Perl) for which you will always have to escape any special character.

    My explanation was more for people who are using some sort of UI editor (Notepad, Excel, Gedit etc.) which will always assume you are trying to enter what you type and will automatically escape it for you when saving. Reading it back in the same (or any other) UI editor will reverse this and show you what the un-escaped string was, in your case the \.

    If you were to enter \n in such a text editor and then read the file with any type of raw viewer (sed, awk, Perl commands included) you would see that the raw data is \\n because the UI will have tried to help you out. If you use sed, awk, Perl etc. and put in a \n then you have specifically told the editor to put in a newline character. [*] Viewing that file in Notepad etc. then shows it as a newline.

    Unfortunately for programmers, UI text editors are not what they state. None of the usual UI tools are raw text editor / viewers, rather they are a representation of what the O/S thinks the user wants to see. Some will have an option to view non-printing characters and some, like WordPad etc., have options to insert a non-printing character such as a line-break. You work with what you have

    Thanks,

    JH.

    [*] Yes, I know tools like sed have special characters for start-of and end-of line to use

Reply
  • Viktor Ranga

    You are right about having to escape the \ character if you are using any form of scripting - which is why I excepted tools such as sed and awk (and Perl) for which you will always have to escape any special character.

    My explanation was more for people who are using some sort of UI editor (Notepad, Excel, Gedit etc.) which will always assume you are trying to enter what you type and will automatically escape it for you when saving. Reading it back in the same (or any other) UI editor will reverse this and show you what the un-escaped string was, in your case the \.

    If you were to enter \n in such a text editor and then read the file with any type of raw viewer (sed, awk, Perl commands included) you would see that the raw data is \\n because the UI will have tried to help you out. If you use sed, awk, Perl etc. and put in a \n then you have specifically told the editor to put in a newline character. [*] Viewing that file in Notepad etc. then shows it as a newline.

    Unfortunately for programmers, UI text editors are not what they state. None of the usual UI tools are raw text editor / viewers, rather they are a representation of what the O/S thinks the user wants to see. Some will have an option to view non-printing characters and some, like WordPad etc., have options to insert a non-printing character such as a line-break. You work with what you have

    Thanks,

    JH.

    [*] Yes, I know tools like sed have special characters for start-of and end-of line to use

Children
No Data