[DirtyDog]

I need to import multiline data into excel so that several lines of text are in each cell. My CSV data looks like this:

"line 1
line 2
line 3"
;
"line 1
line 2
line 3
"

etc

But it imports every line into a cell and I can't figure out how to format the csv file to correct this. Any suggestions?

 

[jacksonpt]

Let me see if I understand... right now it's importing each line into its own cell? But you want each "group" (by group I mean the links of text between the semicolons) to have it's own cell? Is that right?

Save the CSV as a text file. Open the text file in wordpad (assuming you're on windows), do a find and replace - replace all the quotes with nothing, basically removing them from the file. Resave, then open the text file in excel. When you do, it'll ask how the fields are delimited, check the box for semicolon, make sure all other boxes are unchecked. See if that works for you. The only kicker might be that there is no space between the last character of one line and the first character of the next, i.e. a cell might read line 1line 2line 3.

 

[DirtyDog]

Let me see if I understand... right now it's importing each line into its own cell? But you want each "group" (by group I mean the links of text between the semicolons) to have it's own cell? Is that right?

That is correct. Removing the quotes didn't change the result - each line in its own cell.

 

[DRB]

If the data is actually on separate lines, then regardless of the delimiter it is going to put them in separate rows.

If your data lines are grouped together on the same line then what Jackson said is perfect. But if you are formatted exactly like you showed then its something I've always had troubling doing and never figured a good way of dealing with it.

 

[binary visions]

There's no good way to do it on import if it's formatted as you say. There are some ways to do it with a formula after the fact, though. You could also edit the data file itself - Word will do a search and replace for carriage returns, so you could just delete all of them or replace them with a space.

 

[sanjuro]

Boy, this is one for regular expressions. Unix Rulz!

BTW, when I worked as mainframe operator at college, I would secretly sneak student data onto the email server, which was AIX, and edit data loads there.

EDIT: Actually, if each data set is the same amount of lines, then I would just write a macro in the editor of your choice to manually delete the newline and replace it with a delimiter character. I could do easily in Emacs but you should be able to do it in Word.

 

[DirtyDog]

I see several problems then -

I need the line breaks to be displayed as line breaks in Excel

A line of data that contains a space would be indistinguishable from the spaces used to delimit lines of data

There must be a way to do this.....

 

[DirtyDog]

Actually, if each data set is the same amount of lines, then I would just write a macro in the editor of your choice to manually delete the newline and replace it with a delimiter character. I could do easily in Emacs but you should be able to do it in Word.

Formatting the csv file is not a problem - I can do anything I want with that. The question is - what format is actually going to give me the desired result in Excel?

 

[sanjuro]

Replace "\n;\n" with just ";". Then your datasets are not separated by new lines but just a single semi colon.

Instead of

"line 1
line 2
line 3"
;
"line 1
line 2
line 3
"

it would look like

"line 1
line 2
line 3";"line 1
line 2
line 3
"

Alternately, separate every line in your dataset with another character, then after loading your csv into Excel, replace that character with a new line.

"line 1*
line 2*
line 3"
;
"line 1*
line 2*
line 3
"
Would look like this in excel:

cell a1: line1*line2*line3
cell a2: line1*line2*line3

Then replace every asterisk with a new line.

 

[binary visions]

Format the CSV file to replace the carriage returns with a unique character (for this example, I'll use a #).

Your data should then be:

line1#line2#line3;
line1#line2;

Import it. Do a search and replace. Search for # and in the replace box, hold ALT and type (on the keypad) 0010. Nothing will appear in the replace box, but hit Replace All and it'll insert your carriage returns.

edit: sanjuro has it, but you have to know the alt-code to insert it as a new line since Excel doesn't allow you to replace things with formatted text.

 

[sanjuro]

Format the CSV file to replace the carriage returns with a unique character (for this example, I'll use a #). Import it as a semi-colon delimited file.

Your data should then be:

line1#line2#line3
line1#line2

Do a search and replace. Search for # and in the replace box, hold ALT and type (on the keypad) 0010. Nothing will appear in the replace box, but hit Replace All and it'll insert your carriage returns.

edit: sanjuro has it, but you have to know the alt-code to insert it as a new line since Excel doesn't allow you to replace things with formatted text.

Good info to do the Microsoft way.

You and I together make a superior computer professional.

 

[binary visions]

We should do business together. Start a consulting firm. Make a mint and retire at 40.

 

[sanjuro]

We should do business together. Start a consulting firm. Make a mint and retire at 40.

All we need to do is to come up with some bullsh*t idea, like a way for the socially retarded to communicate with each other, like myspace.

 

[binary visions]

a way for the socially retarded to communicate with each other, like myspace.

Or Ridemonkey.

 

[DirtyDog]

All we need to do is to come up with some bullsh*t idea, like a way for the socially retarded to communicate with each other, like myspace.

I'll start another thread about this

 

[sanjuro]

Or Ridemonkey.

Yeah, Beerdemon made his dotcom millions, retired, and is now doing Excel dataloads as a hobby.

The problem is with Ridemonkey is that people here have a life. We need to find losers who don't ride.