none
How can I import data (CSV) with line breaks?

    Question

  • I need to import data which include line breaks.
    Data migration manager supports CSV files which doesn't allow line breaks.
    I tried to convert all line breaks (CRLF) to "\n" with the result that after
    migration I get "\n" displayed in MS CRM.

    My next try would be to convert those "\n" to CRLF in the databse directly,
    although direct changes in the database are not recommended by Microsoft.

    Any better ideas?
    Friday, April 17, 2009 10:39 AM

Answers

  • What i've always done (this is a hack but it works) is to save my CSV file in html format (via Excel).  Then you can open the HTML file and do a find-and-replace for all <br>'s (line breaks) and <p>'s (paragraphs) with an escape character like " / ".  Then save the file, re-open in Excel, and save-as CSV.  This gives you a new CSV file without the line breaks.

    Also open the file in Notepad and turn off wordwrap in notepad to make sure you don't see any additional line breaks.  If there are just one or two, you can delete them out.  Or better than notepad, a good (and free) text editor I use alot when working with txt/csv/html files is ConTEXT.  http://www.context.cx.

    Ryan | Zero2Ten | http://www.zero2tencrm.com/
    Friday, April 17, 2009 4:33 PM

All replies

  • I would consider Scribe Software.  You should be able to maintain and import your line breaks in a supported fashion.  Otherwise you will want to use the SDK if you plan to do a direct update to the SQL database so that you do it in a supported way.

    Scribe is a value priced solution that will save you a lot of time and trouble

    Scribe

    www.scribesoft.com

    SDK

    http://msdn.microsoft.com/en-us/library/bb928212.aspx

    If you do an unsupported insert of records to the database, I can almost, if not, guarantee that you are going to have issues downstream due to the relationship of records in the database and various other elements in the application.
    Best Regards, Donna
    Friday, April 17, 2009 2:04 PM
    Owner
  • Donna, thank for suggestions. I will consider trying the SDK.

    First, I will try http://mscrmbulkupdatetool.codeplex.com/

    Maybe this is an easier way then the SDK.
    Friday, April 17, 2009 2:57 PM
  • Hi Roman,

    Here's a suggestion. Import the CSV to a temp database table, then write query to retrive the rows that's not blank. Then export it back to CSV and then use CRM Data Import  Tool to import the file. Or if it's a complicated import, you may consider using SQL Integration Services (SSIS) instead of writing a application to do it via the SDK.
    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    Friday, April 17, 2009 3:10 PM
    Moderator
  • Darren, thanks for your help. Unfortunately, I don't understand your recommendation.
    The original data to be migrated is in a database (SQL 2005)  table. There exists one colum (nvarchar(max)) that holds general comments made by employees. These comments include line breaks.
    If I save the table in CSV format I get line breaks in the CSV file which can't be read by migration manager. Even if I put the fields in double quotes then data migration manager doesn't recognize them.
    Friday, April 17, 2009 3:37 PM
  • I see. I didn't know that you already getting the data from SQL. Are you trying to populate the description field?

    You can still use SSIS to import the data.


    Darren Liu | 刘嘉鸿 | MS CRM MVP | English Blog: http://msdynamicscrm-e.blogspot.com | Chinese Blog: http://liudarren.spaces.live.com
    Friday, April 17, 2009 4:07 PM
    Moderator
  • Yes, it is the description field.
    Friday, April 17, 2009 4:14 PM
  • What i've always done (this is a hack but it works) is to save my CSV file in html format (via Excel).  Then you can open the HTML file and do a find-and-replace for all <br>'s (line breaks) and <p>'s (paragraphs) with an escape character like " / ".  Then save the file, re-open in Excel, and save-as CSV.  This gives you a new CSV file without the line breaks.

    Also open the file in Notepad and turn off wordwrap in notepad to make sure you don't see any additional line breaks.  If there are just one or two, you can delete them out.  Or better than notepad, a good (and free) text editor I use alot when working with txt/csv/html files is ConTEXT.  http://www.context.cx.

    Ryan | Zero2Ten | http://www.zero2tencrm.com/
    Friday, April 17, 2009 4:33 PM