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

Posted on 2011-10-01
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
  • 2
LVL 13

Assisted Solution

Superdave earned 250 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 250 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 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