Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more


How to import a csv-file, when some fields contain line brakes, into columns in Excel?

Posted on 2011-10-01
Medium Priority
Last Modified: 2012-05-12
I need to import a large CSV file (exported from win 2000 MSSQL-server), which contains of fields like this.

"this is the title","And this is a lot 
of text.

<b>This should be in bold</b>

And the text ends here for this entry","334"
"title number 2","and this title also has
linebrakes in it...

<i>which is causing me</i>a headache,"897"

Open in new window


I need to get this into Excel, with each data field in one column. But it seems that if there are linebrakes in the file, it all gets messed up - and the . I thought that was the reason why you can configure Excel to use the double quotation character as a text delimiter during import, but that doesn't help when it comes to the linebrakes.

I've been testing all different types of delimiters, using comma,tab, semicolon - and single and double quotation marks as text delimiters. But nothing works so far...

How do I go about this?

The reason I need to set this up like this in Excel, is because I need to add some data to each entry. Therefore I thought it would be simplest to import it into delimited columns, and then just add the new columns - and export it again as a new csv-file.

So if you happen to know a way how to add for example the data field: "additional data" between "title" and "text" in the whole document (without the need of using columns in the first place), then that would solve my problem to!
Question by:andre_st
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 13

Assisted Solution

Superdave earned 1000 total points
ID: 36898167
Excel likes to use linefeeds to separate lines within the fields, and carriage-return/linefeed combinations to separate records.  I don't know about MS SQL Server, but because they're both MS products I'd expect them to be designed to cooperate, so maybe some intermediate software such as the editor you're using to add to the fields is converting the LFs to CRLF.  If you can fix the final result to be in that format it should work.

Author Comment

ID: 36899111
Thanks for your reply. I had seen the settings for CRLF/LF when exporting - but I didn't know what they meant. So thanks for pointing that out. But it still doesn't work. I've once again tried all different delimiters , this time with an LF-exported file from MSSQL.

I began to wonder if the exported file actually was in the LF-format, as I saw no difference at all after importing to Excel. So I found a third party tool to convert CRLF to LF. And after I used it I could at least se difference when opening the file in notepad - as there were no linebrakes at all. But when importing to Excel, the fields still get all messed up as before. I can't really understand how Excel divides the data into cells/columns. One problem seems to be though that it is also dividing the data based on the font tags for bold and italic <i>, <b>. But it also still seems to be dividing based on linebrakes. I don't know how Linebrakes/Line Feeds actually work. But if I for example copy the text from textfile I have - where in Notepad I can see no line brakes at all, and copy it to MS Word, all line brake show up again.

I am thinking that it is probably s simpler solution for me to find out how to insert a datafield into the document, without having to sort this column problem out. I guess this could be done in Excel as well. To make it find out for example the fourth semicolon of each entry, and add specific data there. Do you know how to do that?

Accepted Solution

experts1 earned 1000 total points
ID: 36901651
Use your third party editing tool to replace all  "CRLF" to "<br>" in the csv-file,
before importing into Excel.

The <br> will serve as a line break in the HTML code but will not
disrupt Excel display of each line.

Author Comment

ID: 36902331
I couldnt do that with the third party tool I was using at first. But I found out it can easily be done with Notepad++

In there you can chose to view all characters, including CR/LF by:

View > Show Symbol > Show all characters.

Then you can use the search and replace function. Enable the "extended search mode", and search for "\r\n", and replace with <br />. (it is not possible to search for "CRLF".

Worked like a charm, and now I can import to excel, with all the fields in the right cells/columns.

Thanks for your help guys

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question