Issue 97762 - Exporting a base table into CSV text file: Unicode code replaced by ? sign
Summary: Exporting a base table into CSV text file: Unicode code replaced by ? sign
Status: CLOSED IRREPRODUCIBLE
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOO300m7
Hardware: Unknown Windows XP
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: dbaneedsconfirm
QA Contact: issues@dba
URL: http://user.services.openoffice.org/e...
Keywords: needmoreinfo
Depends on:
Blocks:
 
Reported: 2009-01-05 08:19 UTC by nbrouard
Modified: 2010-11-29 08:22 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
database with sample table created in 3.0.0 (10.07 KB, application/vnd.sun.xml.base)
2009-01-05 18:39 UTC, r4zoli
no flags Details
database with sample table created in 3.0.0 (10.07 KB, application/vnd.sun.xml.base)
2009-01-05 18:40 UTC, r4zoli
no flags Details
database with sample table created in 3.0.0 (10.07 KB, application/vnd.sun.xml.base)
2009-01-05 18:40 UTC, r4zoli
no flags Details
database with sample table created in 3.0.0 (10.07 KB, application/vnd.sun.xml.base)
2009-01-05 18:41 UTC, r4zoli
no flags Details
database with sample table created in 3.0.0 (10.07 KB, application/vnd.sun.xml.base)
2009-01-05 18:41 UTC, r4zoli
no flags Details
exported text from 3.0.0 (50 bytes, text/plain)
2009-01-05 18:42 UTC, r4zoli
no flags Details
export from m37 (50 bytes, text/plain)
2009-01-05 18:43 UTC, r4zoli
no flags Details
ods file with copied data from db table (11.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-01-05 20:40 UTC, r4zoli
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description nbrouard 2009-01-05 08:19:39 UTC
From the forum discussion link it appears that a way to export a table from a
base database into a CSV consists in the following steps. It works but ...:
-------------------------Solution quoted-----
To start this ( and, this can also be accomplished by a macro if you so desire )
. . . the steps are:

   1. Open your database
   2. From the menu: Tools -> SQL...
   3. under Command to execute, enter your SQL
   4. an example might be ( change output_csv_file_name to your desired OUTPUT
name, and, your_table_name to the HSQL table you are using )

      Code: Select all   Expand viewCollapse view
          SELECT * INTO TEXT "output_csv_file_name" FROM "your_table_name"

   5. Press the Execute button
   6. Press the Close button

The bottom line . . . this will create a TEXT CSV file for you . . . located in
the same directory where your database ODB file is located. The file will have a
file type of CSV .

Additionally, your database will have a NEW TEXT 'table' as defined by the name
you gave it in the SELECT above. This 'database' file may be deleted.

Also, you can include a WHERE clause above if you wish to only output a part of
the table to your CSV file. And, an ORDER BY clause will further define the CSV
output.
-------------------------
... the problem encountered, even on latest 3.00m7 version is that my accented
characters disappeared. I know that Windows has difficulties is writing text
files in Unicode but I am not sure that it is a Windows problem.

I haven't found an option for the SELECT command or for the INTO TEXT command to
add something like UTF-8 encoding or similar. In both, the .CSV file as well as
the new table created into the database, all accented characters have been
replaced by ? signs.

PS: Priority is set to P2 because last time I used P3 it last more than a year.
I won't be frustrated if you confirm the bug and that if there is no easy work
around to be downgraded to a lower priority.
Comment 1 christoph.lukasiak 2009-01-05 14:32:10 UTC
clu->nbrouard:

1. may you add a small sample database file that shows the problem
2. have you tried that manually (without sql code)?

thx

p.s. how fast an issue is confirmed, mostly depends on the quality of the
description (fast and easy is the best), the complexity of the issue, the
reproducability and how many user it may concern (rather not the priorisation)
.. this issue is not a prio 2 issue, so please do not send it as an p2 (wrong
prio do not help the issue but bugs the confirming guys) .. please read:
http://qa.openoffice.org/scdocs/ddIssues_EnterModify.html#priority

thx
Comment 2 r4zoli 2009-01-05 18:38:38 UTC
Same for me results with OOo 3.0.0 and DEV300m37 under winXP.
See demo database, attached.
Comment 3 r4zoli 2009-01-05 18:39:52 UTC
Created attachment 59143 [details]
database with sample table created in 3.0.0
Comment 4 r4zoli 2009-01-05 18:40:19 UTC
Created attachment 59144 [details]
database with sample table created in 3.0.0
Comment 5 r4zoli 2009-01-05 18:40:35 UTC
Created attachment 59145 [details]
database with sample table created in 3.0.0
Comment 6 r4zoli 2009-01-05 18:41:03 UTC
Created attachment 59146 [details]
database with sample table created in 3.0.0
Comment 7 r4zoli 2009-01-05 18:41:39 UTC
Created attachment 59147 [details]
database with sample table created in 3.0.0
Comment 8 r4zoli 2009-01-05 18:42:26 UTC
Created attachment 59148 [details]
exported text from 3.0.0
Comment 9 r4zoli 2009-01-05 18:43:56 UTC
Created attachment 59149 [details]
export from m37
Comment 10 r4zoli 2009-01-05 18:46:20 UTC
I uploaded sample file, site not responded as usual give an empty page. I tried
again and again. Sorry for this. 
Comment 11 r4zoli 2009-01-05 20:39:37 UTC
If you used db as data source in Calc, F4, select table and click into left
upper corner(select all data), drag and drop into sheet, save as csv, you can
export with correct coding. 

If you select database table in Base and copy with CTRL+C, CTRL+V into sheet,
you can have some trouble. see my attached Calc spreadsheet.
Comment 12 r4zoli 2009-01-05 20:40:40 UTC
Created attachment 59153 [details]
ods file with copied data from db table
Comment 13 nbrouard 2009-01-06 11:51:33 UTC
Thank you for confirming the bug with your nice files examples.

The work around which is also a simpler way was also mentioned in the quoted forum.


Using OOdev-300m7 build 9371 I can confirm that opening a new sheet, typing F4
to register the database given as an example, clicking on the top-left grey
empty square of the table of the database in order to select all data and
draging into the empty sheet fills the sheet with correct hungarian accentuation.

I haven't been able to reproduce the bug of CTRL_C from Base to CTRL-V in a new
spread sheet: I get only the top left element (0) to be copied. But if I drag
directly from Base and drop into the empty sheet it works: no need to use F4.
And all characters are OK: U umlaut is still a U umlaut.

The original first reported bug comes probably from the fact that on Windows as
well as on Linux, while writing or reading a text file like a CSV, calc asks for
the underlying code, UTF-8 or another old one. Anything against UTF-8  by default?

Comment 14 r4zoli 2009-01-06 12:17:42 UTC
CTRL+C and CTRL+V works as you decribed in open database table, but if you
select whole table (in main table window)and copy into the ods file, will show
my results. 

Őő, and Űű are oldest problems with Hungarian keyboard drivers and setting, it
allways caused problems until UTF was introduced, but haunting now. 

Under winXP when saved from Calc to csv needs to select win1250 coding for
correct characters.
If I save as UTF-8 total mess.   
Comment 15 nbrouard 2009-01-06 13:07:54 UTC
>CTRL+C and CTRL+V works as you decribed in open database table, but if you
>select whole table (in main table window)and copy into the ods file, will show
>my results. 

Ok I understood the différence (copying the whole table) but on my French XP
there is no errors and I get:
ÁRVÍZTŰRŐTÜKÖRFÚRÓGÉP

>Őő, and Űű are oldest problems with Hungarian keyboard drivers and setting, it
>allways caused problems until UTF was introduced, but haunting now. 
On Linux there is no error too.

>Under winXP when saved from Calc to csv needs to select win1250 coding for
>correct characters.
>If I save as UTF-8 total mess.  
Did you use a simple notepad to read it? (or a recent emacs). Because I verified
that the output in utf-8 was correct too. 
At least csv files in utf-8 are read correctly by oocalc on Windows (and Linux).
Concerning Excel it can be read as is, I agree.
ÁRVÍZTŰRŐTÜKÖRFÚRÓGÉP
Comment 16 thierrym 2009-12-21 10:44:11 UTC
Hi,
I confirm the problem with the "translated" file with the command :

SELECT * INTO TEXT "output_csv_file_name" FROM "your_table_name"

All the characters with an accent (acute/grave/circumflex) are replaced by "?".
It's an encoding problem (I use the french language under Windows Vista).
I found that it was possible to specify the type of encoding to read a file text
in OOo Base with an instruction like that :

SET TABLE "MaTable" SOURCE "monFichier.csv;encoding=UTF-8"

Maybe, there is a solution by this way ?
Regards


Comment 17 drewjensen.inbox 2010-03-07 18:10:38 UTC
You can also change the HSQLdb GLOBAL varialbe

textdb.encoding

To UTF-8, by default it is ASCII
Comment 18 ocke.janssen 2010-11-29 08:21:50 UTC
So I close this one.

Steps to go are:

SET TABLE "MaTable" SOURCE "MaTable.csv;encoding=UTF-8"
SELECT * INTO TEXT "MaTable" FROM "your_table_name"

and the chars should be o.k.

Comment 19 ocke.janssen 2010-11-29 08:22:12 UTC
Closing