[Solved] SELECT * INTO TEXT table_name + fs=\semi?

[MusX]

Already searched forum and lots of google results too.
How can I specify field separator during SELECT * INTO TEXT ?
Default is comma, would like to use semicolon.
This way I would like to export table data to semicolon separated csv file.
Copy-paste usually crash applications because of big amount of data to be pasted.
Already read about SET TABLE "table_name" SOURCE "file_name.csv;fs=\semi"
But there are no info how to use 'fs=' during SELECT INTO TEXT.

I also have no option for csv file in Edit->Databases menu. There are only "advanced settings" available with some CR+LF, etc useless in my case setting.
OOO 3.2 polish.
Any solution?

[evwool]

MusX, what do you want to do with your export? There may be a better way.

[MusX]

open in ms excel, maybe calc but even there I prefer semicolon instead of comma, comma is much easier to made by mistake while people filling forms

[eremmel]

Are you aware of the possibility to retrieve data from a table (Base document) directly into Calc? There is a Calc tutorial: Using registered datasources in Calc. From Calc you might find it more easy to export.

[Sliderule]

Musx:

You asked:

How can I specify field separator during SELECT * INTO TEXT ?

This can be accomplished . . . by telling the database engine ( HSQL ) what you want the textdb.fs ( for a text database . . . the fs = field separator ) to be.

Just for reference . . . http://www.hsqldb.org/doc/guide/ch09.ht ... ty-section says:

SET PROPERTY

SET PROPERTY <double quoted name> <value>;

Sets a database property. Properties that can be set using this command are either boolean or integral and are listed in the Advanced Topics chapter.

and

textdb.fs is the "Field Separator" property

HSQLDB also recognises the following special indicators for separators:

special indicators for separators

1. \semi - semicolon
2. \quote - qoute
3. \space - space character
4. \apos - apostrophe
5. \n - newline - Used as an end anchor (like $ in regular expressions)
6. \r - carriage return
7. \t - tab
8. \\ - backslash
9. \u#### - a Unicode character specified in hexadecimal

Bottom line . . . from the Menu ( of course, change "MyTextTable_TXT" and "MyTable" to the text output file and source database file names you desire ):

Tools -> SQL...

Code: Select all

-- Following statement will make the field separator a semicolon
set property "textdb.fs" '\semi';

-- Statement to create a new CVS file "MyTextTable_TXT" from database file "MyTable"
Select * INTO TEXT "MyTextTable_TXT" From "MyTable";

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[evwool]

Be aware, if your ultimate aim is to get this into Excel/Calc that both these programs have a limit to how many rows they can handle (65, 536 rows with 1024 columns in each sheet ). From your description, MusX, I wonder if your file exceeds those limits.

[MusX]

thanks, that is what I was looking for. Will try it soon.
65k rows and 1k columns should be yet enough.
this set property looks like a bit hard to find in documentation.
thanks again

[Sliderule]

MusX:

Just so you know . . . two things.

1. The Set Property command only needs to be entered once. This command gets written to the 'internal' .properties file. I would recommend, if you have any PREVIOUS defined TEXT FILES in your database, that are NOT set to semi-colons . . . you first delete them. Otherwise, when you open your database file, it may be 'confused' since some of the text files are defined, by default, with commas, and, others now with semi-colons.
2. Just as an FYI ( For Your Information ) . . . as of the date this is being written ( 2010-07-07 ) . . . the embedded database engine is . . . HSQL 1.8 . To the best of my knowledge, OpenOffice 3.3 is scheduled to include HSQL 2.0 and the syntax for writing a text file is changing.
   
   Put another way, when OpenOffice 3.3 is used with HSQL 2.0 ( in the future ), you will have to use different syntax to create the CSV file. I just mention this so you will know for future reference.

Added 2010-07-09: Per the recent post by r4zoli, make that OpenOffice 3.4 for HSQL 2.0:

http://user.services.openoffice.org/en/ ... 13&t=32211

Sliderule