Solved

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

Posted on 2011-10-01
4
534 Views
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.

"title","text","ID"
"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


etc.

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!
0
Comment
Question by:andre_st
  • 2
4 Comments
 
LVL 13

Assisted Solution

by:Superdave
Superdave earned 250 total points
Comment Utility
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.
0
 

Author Comment

by:andre_st
Comment Utility
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?
0
 
LVL 9

Accepted Solution

by:
experts1 earned 250 total points
Comment Utility
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.
0
 

Author Comment

by:andre_st
Comment Utility
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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now