Preserving line breaks when importing from Calc into Base

[stanton]

I am trying to import data from Calc into Base, and have done some preparation on the Calc sheet: The top row contains the column names, the remaining rows are data. Some of my data has line breaks, which I would like to preserve.

I have followed the standard procedure of selecting the entire sheet, copying it to the clipboard, then pasting it into the Tables window in Base. I have selected LONGVARCHAR as the data type for the fields which have line breaks.

However, when I look at the data, it seems the line breaks have disappeared.

Is there any way to get data from a Base spreadsheet into Base (using HSQLDB) and preserve line breaks?

[mikekaganski]

Not a Base expert, just guessing: can’t exporting to CSV and importing into a base table help here (if clipboard intermediate format breaks things)?

[Lupp]

(Not a database / Base expert at all!)
Did you try if your LONGVARCHAR field accepts linebreaks at all?
A linebreak directly entered into a Calc cell with Ctrl+Enter is LF (%0A). A different context may expect CRLF or LFCR or CR to code for a linebreak.
@mikekaganski: How should a linebreak be represented in csv?

[mikekaganski]

@Lupp:

How should a linebreak be represented in csv?

As per RFC 4180 2.6: “Fields containing line breaks (CRLF) … should be enclosed in double-quotes.”

[Lupp]

Thanks. I read it a few days ago and hadn’t it “mindy”.

[stanton]

@Lupp: Yes, line breaks can be entered directly by pressing Ctrl+Enter into a LONGVARCHAR field and are preserved. The line break format can be selected in the database properties. Default seems to be LF, CRLF can be selected if needed.

[stanton]

@mikekaganski: how can I import CSV directly into a Base table? The only way to get anything into Base that I know of is via clipboard, manual entry or from another database (via ODBC and the like).

[mikekaganski]

I meant ODBC.

[mikekaganski]

I meant ODBC.

[EasyTrieve]

Make sure to create a form from the table (possibly with a right click on the table, and selecting Form wizard…), and set the control’s Multiline input to Yes.

Also possibly try moving it into type Text (not sure if you need this step or not); (assuming HSQLDB1.8, and MySQL & MariaDB). (Not sure for FireBird as I for the moment i’m choosing to avoid it until it works a little better.)

Note in screenshots below I dragged the spreadsheet column size (which is set ‘Format cells…|Alignment|Wrap text automatically’ to yes) a bit after I took the last screen shot so the first and the last don’t show the same breaks, but the line breaks did come through in the transfer.