OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Mass find & replace in table?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
jdavis72
Newbie
Newbie


Joined: 20 Feb 2006
Posts: 2

PostPosted: Mon Feb 20, 2006 2:48 pm    Post subject: Mass find & replace in table? Reply with quote

After importing a large item cost list from OOo Calc to Base, I see where all of the items that have double quotes (representing inches, as in 14" long) in them changed to a few odd characters. I can easily go into the table and delete and replace them with the correct double quotes, but is there a faster way? I don't see a Find & Replace choice in Base. The file is in the default OOo .odb file format, in WinXP. I found one solution after searching these forums, but I assumed they were for connecting to a larger DB (SQL, etc.). Thanks in advance!

Jeremy
Back to top
View user's profile Send private message
bramkuijper
General User
General User


Joined: 03 Sep 2006
Posts: 5

PostPosted: Sun Sep 03, 2006 1:35 pm    Post subject: Reply with quote

good one. looking for that one too. Unfortunately, such a simple function has apparently been forgotten. Not there.
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Sep 03, 2006 2:13 pm    Post subject: Reply with quote

Sure you can do this.

You will have to SQL - or perhaps a macro.

First some simple changes.

make yourself a test table to get the hang of this
Table1( ID IDENTITY, DATA VARCHAR(50))
would work just fine.

Add some records

If I wanted to change all the entries in a column to upper case I could use this command in the SQL window. 'Tools>SQL'

UPDATE "Table1" SET "DATA" = UCASE("DATA" )

All text in the column DATA is now uppercase.

Now what about replacing data...yup there is a REPLACE function.

I can replace all occurrances of A with *, with this statement

UPDATE "Table1" SET "DATA" = REPLACE( "DATA", 'A', '*' )

You can find a full list of supported text functions in the HSQL users manual, downloadale from http://hsqldb.org

You can also find them listed in this spreadsheet
http://www.paintedfrogceramics.com/OpenOffice/hsql_functions.ods

If you need help with the actual update statement, let me know and I will try to help

HTH

Drew
Back to top
View user's profile Send private message Send e-mail Visit poster's website
remdelyon
Power User
Power User


Joined: 11 Sep 2006
Posts: 62
Location: Lyon, France

PostPosted: Thu Oct 12, 2006 8:11 am    Post subject: How to use SQL code in Base macros? Reply with quote

Hi Drew, (or anybody else ? )

a question, you are saying that this code (which works properly in the SQL window) can be also used in a macro. I tried but it does not work, probably because the SQL does not know that the code must be applied to this database.

I searched everywhere but I was not able to find the "magic" statement to write in the begining of my macro to solve the problem (it probably looks like "CreateUnoService("com.sun.star.sdb.DatabaseContext") " or sthg like this...

I would be so glad to get the answer....
_________________
Ubuntu / Vista - LO 3.4.3
Back to top
View user's profile Send private message
remdelyon
Power User
Power User


Joined: 11 Sep 2006
Posts: 62
Location: Lyon, France

PostPosted: Fri Oct 20, 2006 1:25 pm    Post subject: Reply with quote

OK, found.

If anybody wants the same information, the following example can help ( for a query counting the items in table "Vue1"... )


Code:

im DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim Statement As Object
Dim oDoc
Dim total2 as Object

oDoc = ThisComponent
oDoc = oDoc.getParent()
 oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
 oDataSource = oContext.getByName(oDoc.getURL())

Connection = oDataSource.GetConnection("","")
Statement = Connection.createStatement()

Total2=Statement.executeQuery("select count(""ID"") from ""Vue1"" ")
total2.next
compte = total2.getint(1)
Print "Total :",compte


_________________
Ubuntu / Vista - LO 3.4.3
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group