Ask Your Question
0

BASE Embedded Firebird SQL Code needed

asked 2020-10-17 23:12:13 +0100

Justin100 gravatar image

updated 2020-10-18 20:36:03 +0100

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!

edit retag flag offensive close merge delete

Comments

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 gravatar imagefrofa ( 2020-10-17 23:37:32 +0100 )edit

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

frofa gravatar imagefrofa ( 2020-10-17 23:55:01 +0100 )edit

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.

Justin100 gravatar imageJustin100 ( 2020-10-18 02:33:42 +0100 )edit

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 gravatar imagefrofa ( 2020-10-18 08:12:19 +0100 )edit

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?

frofa gravatar imagefrofa ( 2020-10-18 08:50:59 +0100 )edit

No output file is created.

Justin100 gravatar imageJustin100 ( 2020-10-18 21:22:08 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-10-17 23:37:55 +0100

frofa gravatar image

updated 2020-10-19 04:02:55 +0100

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;
  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.

  2. Open a new blank Calc sheet

  3. Do a Paste Special... (EDIT menu) into the (blank) Calc Sheet

  4. Use the Rich Text Format (RTF) option in the Paste Special... dialog window.

  5. See the SCREENSHOT appended here. image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-17 23:12:13 +0100

Seen: 57 times

Last updated: Oct 19