BASE Embedded Firebird SQL Code needed

Justin100 · October 17, 2020, 9:12pm

Hello. On a Windows 10 machine running LO 7.0.0.3 I’m trying to export table data from an embedded Firefird base file. A few fields in the database have multiple lines with (soft) line breaks which I would like to preserve when exporting.

My understanding is that the exported csv file should have the text data in double quotes.

I have tried copying the database to Calc and this works great EXCEPT for the (soft) line breaks which are removed. The resulting data is correct in each exported field, although visually a mess without those line breaks.

Question 1: Is there a way to copy/paste using Calc that will protect those line breaks? I assume not as I’ve tried most options by now.

Question 2: Using SQL code on the table in question should be a perfect solution however my code seems to have an error regarding the “INTO OUTFILE”. Can anyone suggest the correct code to use? Below is what I am tying to use although unsuccessfully.

SELECT field
FROM table
INTO OUTFILE ‘C:\Users\Justin\Documents\Backup\2020-10-17_1619.csv’
FIELDS ENCLOSED BY ‘"’
TERMINATED BY ‘;’
ESCAPED BY ‘"’
LINES TERMINATED BY ‘\r\n’;

The field and table names above are correctly entered in the final code, the problem appears to be the Outfile which does not get created. The FB code error shows the problem beginning with “INTO”.

All help is appreciated! Thanks!

frofa · October 17, 2020, 9:37pm

Hello Justin100: for your Question 1, have you tried the Paste Special… command under the Edit menu? There are some options there you might want to try out when pasting your table data into a Calc sheet. Report back and tell us what (if anything) works. I will check out out your Question 2 shortly and post another comment/answer soon.

frofa · October 17, 2020, 9:55pm

I have some info that might be relevant to your Question 2 HERE using the OUTPUT ISQL command. Let us know if it works (or not).

Justin100 · October 18, 2020, 12:33am

Those suggestions did not work for me. The paste special did not offer any advantage unfortunately and I was not able to find the ISQL utility in the LO program files. If it makes a difference, I am using the embedded Firebird database.

frofa · October 18, 2020, 6:12am

Hello: You said …the problem appears to be the Outfile…, but was ANY file generated? And if so would you explain what exactly the problem was - and especially what happened to the ‘soft’ line breaks? Is this POST any use? Can you supply a SAMPLE Base file with a few non-sensitive (anonymized) records for us to play with?

frofa · October 18, 2020, 6:50am

Also, I see you have POSTED a while back regarding a work-around for IMPORTING line break data. Could this process be reversed to EXPORT such data?

Justin100 · October 18, 2020, 7:22pm

No output file is created.

frofa · October 17, 2020, 9:37pm

Using a demo Base 1-line table I created (Firebird DB), I have been able to create a 2-line entry in a notes column of its linefields_example table using the following SQL:

UPDATE  "linefeeds_example"
SET "note" = 'This is line one.'||ASCII_CHAR(13)||ASCII_CHAR(10)||'This is line two.'
WHERE "id_le" = 1;

I then copied the record to the clipboard (including the column headers) - just select the table in the table list and do a COPY. NOTE: when the new record (row) is created it will NOT show the two-line record as such in the "note’ column - it will only show it as 1-line. Ignore this because it is a display problem only.
Open a new blank Calc sheet
Do a Paste Special… (EDIT menu) into the (blank) Calc Sheet
Use the Rich Text Format (RTF) option in the Paste Special… dialog window.
See the SCREENSHOT appended here.