Excel 2007 : importing csv bug?

[fordry]

I have an issue importing a csv file. overall it works fine, separating the fields by the correct delimiter and placing them in the correct columns, unless there is an "enter" forming another line in between delimiters. I have multiple lines inside one field in the csv file and Excel is reacting by placing them in a new row starting in column A and each new line gets placed on the next row down in column A, even though "enter" or a new line, is not set as the delimiter. I have searched around and haven't seen anything about this problem and I can't find any settings to have excel ignore enter as it is importing and just keep everything within 1 field in the same cell.

I will mention that libre office calc imports the same csv file flawlessly so I have to think excel should be capable of doing it.

[arlu1201]

It would be good if you can attach a sample workbook so that we can help you out easily.

[Andy Pope]

I just did a simple test and new lines will be accepted in a field if enclosed in quotes.

As suggested post example csv file.

[fordry]

Here is a test file, excel test, saved in standard excel format with a cell with multiple lines in it (used alt+enter to make multiple lines in the cell). Save it as a csv text file(i used comma separated, it also has tab separated). then try importing that file, it will place the extra lines in the multiple line cell into their own rows, not even staying in the same column, they will go to the first column.

If you don't want to do it all, the excel test 2 file is the result of its import.

I just tried with quotes and they didn't change anything and besides that, even if it did work, that really hurts csv compatability. Most other applications won't think that the quotes are there to say there are multiple lines, most applications will display the quotes as they are in the text as the quotes are not defined as anything other than a quote character. If you used quotes and got it to work, I don't think you were doing the same thing.

and again, as i said in the first post, libreoffice calc takes the csv file excel saved and imports it keeping everything in the correct cells.

[martindwilson]

here's what i get,looks ok to me

[fordry]

that didn't look like excel 2007.......

[martindwilson]

and? it doesn't make any difference. but if you insist

[Andy Pope]

You need to post the original csv file not a workbook where you have already loaded it.

different apps behave differently when handling csv files.

[chipbite]

Yeah, excels CSV import functionality has several flaws.

* Open works differently from linking, I believe
but most importantly:
* excel does not respect the text quote start/end w r t crln:
Excel will always interpret CRLN as a new datarow, regardless of if it is within a textfield or really at the end of the datarow.
SO: A CSV with a multiline field, quoted correctly, will not be imported correctly by excel.
Excel will break to a new datarow in the middle of the field and continue reading off the previous field data in the new row, breaking the import completely.

However, if the multiline fields in the CSV use linefeed to break between lines, excel parses it correctly.

It's a really annoying bug.