Excel import txt file with multi-line field

[ls62]

Hi,

I have a delimited text file where one of the fields I want to import as if it was multi-lined. This is like you would do by typing alt-enter when entering a field.

I setup the field with \n newlines between sub-lines and quoted the whole thing, but when I import it still takes the 2nd and 3rd line and treats it like a new record.

Does anyone know how to do this? Am I using the wrong character to separate sub-lines, or is this just not possible?

Thanks for any ideas.

LEE

 

[SkipVought]

Hi,

What is \n supposed to do? Is there some Excel documentation on this?

What does your source data look like?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ls62]

Skip,

Sorry, made a sample sheet up with multiple lines in a cell and then saved it as a .csv to see what the reverse would look like. The csv separated fields and in the field with multiple lines it separated those with some charcter, I think a cr or dec 13, but but I'm not sure how tell when I look at the file with notepad it shows a solid block type char in that spot.

Oh, the \n is just short for 'newline' in unix or in dos I guess it would be crlf.

As far as excel doc... I couldn't find any.

Sample data:


"test1\ntest2\ntest3",test 4,test5

 

[SkipVought]

When you say, "...it still takes the 2nd and 3rd line and treats it like a new record." do you mean that it does this importing...

test1
test2
test3
test 4
test5

or

test1  test2  test3  test 4  test5

or something else happens?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ls62]

Skip,

My actual data has the multiline cell at the end, so to be exact heres what I have:

test1,"test2\ntest3\ntest4"
test5,test6
test7,test8

Here's what I get when I import, and the quotes don't seem to make any difference.

test1 test2
test3
test4
test5 test6
test7 test8

Its treating the \n as new record , not a next line within the cell. I've tried some other chars, but they just make the quoted data show as one line with a wierd char between the test2 test3 and test 4.

Lee

 

[SkipVought]

Why don't you read the file and write into Excel? Then you can control that case.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ls62]

Skip,

I'm not familar with doing that, can you give me some idea on how and what to look into so I could see it it will work?

I'd be willing to check into that option.

LEE

 

[SkipVought]

Well I see that you're a programmer. Have you ever used VBA in Excel?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ls62]

Skip,

Yes, but I'm by far no expert! Takes me quite a long time and alot of trial and error to get my vba macro's working. I'm actually using one to import this txt file, sort and format the spreadsheet. I can get that part.. just can't figure out how to get this one cell to show up with multiple lines when needed.

I'm not sure what vba command(s) you are refering to.. if you can just point me in the right direction, I'll check it.

Are you talking about using vba to edit the cell and create the multi-lines somehow?

LEE

LEE

 

[SkipVought]

Maybe.

A few more questions.

When you have the multi-line case, on that row, is column B ALWAYS empty and in ALL OTHER CASES is column B NOT EMPTY?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ls62]

Skip,

In the test...yes, using my real data set then no. My actual data has abount 10 columns, the last is this multi line cell. When I try to import it I get a value in A and blank B cell for the 2nd, 3rd,...nth multiline. But that is not to say that any of the other rows don't have a blank B cell... A might have a value , B blank, but C through ? column has data.

All I can say is that the rows that are created because of this multiline cell only have A filled in... nothing in subsequent cells on that row.

Lee