[Solved] OpenOffice adds quotation marks to text cells

[bulka_tarta]

Hi!
OpenOffice adds quotation marks to text cells in my document - I've searched around and this seems like a frequent question, but no solutions that I found have worked for me.

Most of the post relate to a problem where OpenOffice Calculator adds quotation marks to cells with numbers, but I'm trying to import text, a lot of text. I have a number of columns, and I need to use | (pipe) to separate them, which works fine, but then text that contains apostrophe, gets some extra quotation marks!

Here are my settings when opening the file:


I've tried selecting Merge delimiters, Quoted field as text and Detect special numbers, but nothing worked. I also tried going to Data > Text to columns, and making sure the column type is selected as Text, but to no avail.

It seems to me when a cell that contains apostrophe, Calc is adding a quotation at the beginning of the text and at the end. Sometimes Calc duplicates the apostrophe to words like "can't, let's, won't" and I end up with "can''t, let''s, won''t".

So a cell with text I won't do it. turns into 'I won''t do it.'. I'm not really sure what else I can try, and going cell by cell to fix this manually will take forever (6000+ lines). Is there something I've missed? Any help is greatly appreciated!

[Villeroy]

Open the file with a simple text editor (notepad.exe) and see what the actual text delimiter is.
Post one of the lines copied from the plain text editor, so we can see some actual sample data.

[bulka_tarta]

Thank you for the reply, here's how text looks like in notepad:
3163|Speech|Hunter|From: AdvisorBeforeBattle|AdvisorBeforeBattle_256472_0013|Let's show them!|Let's show them!
(The text at the end repeats, which is fine, as it's a placeholder for translation).

What I've realised is that when opening the file, if I set the Text delimiter to ' then everything in OpenOffice works fine. However, if I set it to default '' then that's where problems arise. The issue is that if delimiter is set to ' , it looks fine in OpenOffice, but when I save it and open the document in notepad, I get this:
3163|'Speech'|'Hunter'|'From: AdvisorBeforeBattle'|'AdvisorBeforeBattle_256472_0013'|'Let''s show them!'|'Let''s show them!'

[Villeroy]

The text has no text delimiters and you specified the single quote to be a text delimiter.

[bulka_tarta]

I see, that makes sense. The text delimiter field had a drop-down menu and I only had a choice between " and ', now I realised you can just simply delete this field so it's empty. It's a long shot, but is there a chance to quickly remove all the added apostrophes?

[Villeroy]

Import the same file again without text delimiter and the text should be OK.

[bulka_tarta]

Thank you so much! I've managed to get it to work, I've been stuck on this for days and it feels good to finally be able to move on! Such a silly mistake!