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

Table Relationships

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


Joined: 18 Jul 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 7:55 pm    Post subject: Table Relationships Reply with quote

Hello,

After finally figuring out how to import 3 CSV files into separate tables, the next question is how to add each of these tables to a Query and setup a relationship amongst them to run a Query.

Right now the only thing we get is being able to add a single Table to the query. It will not allow us to add another. If we try to setup a relationship we get "this database is not setup for relationships".

What are we doing wrong?

Thanks.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jul 18, 2007 8:52 pm    Post subject: Reply with quote

smartfinds:

You asked:
smartfinds wrote:
the next question is how to add each of these tables to a Query and setup a relationship amongst them to run a Query.

Of your two questions . . . adding tables to your query:
  1. Open your database in Openoffice
  2. Press Queries on the left side
  3. Under Tasks, press Create Query in Design View...
  4. Under Add Table or Query . . . press the Table you want to use in the query, press Add button
  5. Repeat step above for each Table you want to include in your query
  6. Press the Close button

NOTE: If you wish to add another table to the query once started, you can either,
  • from the Menu: Insert -> Add Table or Query
  • Press F7 key

To use your words, in order to "set the relationship amongst them to run a Query:" , please see the 'graphic explanation' in the link below:

http://www.oooforum.org/forum/viewtopic.phtml?t=51739

Note, another option ( to "set the relationship amongst them to run a Query") is, from the Menu: Insert -> New Relation... THEN fill in the popup.

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

Sliderule
Back to top
View user's profile Send private message
smartfinds
General User
General User


Joined: 18 Jul 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 9:09 pm    Post subject: Reply with quote

Hi,

Thanks for your response. Unfortunately that is not working. Once we have the first table added, we are unable to add the next table to the same query. Under the "Insert" menu, the option for F7 to add another table is grayed out.

Seems like we're missing a step somewhere and what is unclear is whether the tables being CSV file imports are the problem or not?

Thanks.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jul 18, 2007 9:17 pm    Post subject: Reply with quote

smartfinds:

You said:
smartfinds wrote:
Once we have the first table added, we are unable to add the next table to the same query. Under the "Insert" menu, the option for F7 to add another table is grayed out.

How many tables have you defined into Base ( I am assuming you are using HSQL Base, rather then another database . . . such as Access, MySQL, SQLite etc ) ? ? ?
Is your CSV file defined as a TEXT table, OR, a table ?

If you open your OpenOffice database table, and, rather Press Tables icon on the left . . . how many 'tables' are showing?

Sliderule
Back to top
View user's profile Send private message
smartfinds
General User
General User


Joined: 18 Jul 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 9:24 pm    Post subject: Reply with quote

Hi,

We followed the directions we found in the HOW TO area to import a CSV file. The process says to use TEXT as the option. Here are the procedures we followed:

http://documentation.openoffice.org/tutorials/dennisdaniels/OpenOfficeImportCSVFiles.swf

There were 3 CSV files and they imported - what looks like - as 3 tables into the database.

Thanks for your continued help.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jul 18, 2007 9:36 pm    Post subject: Reply with quote

smartfinds:

It is very difficult for me to 'see' what you have on yuor machine. Might I suggest the following.

Place an screen image of your database TABLE screen . . . you can post it here:

http://imageshack.us/

The web site above will allow you to post the image so I can 'see' it, and perhaps, help.

Just a thought.

I have no problems importing a CSV file to Base . . . and then populating my Base table. I do it often. The technique I use might be different . . . but . . . first . . . let me see the 'list' of tables you have, by having you post the screen at the above site.

Sliderule
Back to top
View user's profile Send private message
smartfinds
General User
General User


Joined: 18 Jul 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 9:45 pm    Post subject: Reply with quote

Hi,

Lets try this screenshot and see if this helps.

http://www.smartfindsmarketing.com/openoffice.jpg

Thanks.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jul 18, 2007 9:55 pm    Post subject: Reply with quote

smartfinds:

Based on the graphic . . . those are 'tables' . . . not "text files" and that is fine.

How about this . . . for each of your tables . . . right click on them and Edit.

This should bring up the 'column' definitions of the tables . . . and . . . a screen shot of each.

That would be helpful. What I want to confirm, is you have Primary Keys . . . and 'clean' talbes for each.

One step at a time.

Sliderule
Back to top
View user's profile Send private message
smartfinds
General User
General User


Joined: 18 Jul 2007
Posts: 8

PostPosted: Wed Jul 18, 2007 10:04 pm    Post subject: Reply with quote

Hi,

Ok...think we found our problem. The import a CSV file did not work "correctly". When you see this screen shot you'll see what I mean.

So our problem goes back to what is the correct procedure to import a CSV file content to a table?

http://www.smartfindsmarketing.com/openoffice2.jpg

Thanks again.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jul 18, 2007 10:09 pm    Post subject: Reply with quote

smartfinds:

OK, let's review.

If I understand correctly . . .

First, you have a few ( perhaps three ) CSV files, and, you want to populate them ( the CSV files ) into a Base ( HSQL ) database. Is this correct ?

Second, will you be 'updating' this database from CSV files on a regular basis ( inserting NEW records from CSV files ) ?

Sliderule
Back to top
View user's profile Send private message
smartfinds
General User
General User


Joined: 18 Jul 2007
Posts: 8

PostPosted: Thu Jul 19, 2007 9:15 pm    Post subject: Reply with quote

Hi,

Sorry I was not able to respond yesterday further.

yes, we have 3 CSV files we need to populate a Base database. Once we run a Query on these 3 CSV's ( or tables) we will delete them. The next time such a query is needed there will be a new set of 3 CSV's files with new data for the query.

Let me know if this helps.

Thanks.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Jul 20, 2007 8:09 am    Post subject: Reply with quote

smartfinds:

If I am understanding your question now, in your case, you are NOT keeping a database of tables, but rather, you desire to get all of your 'data' from CSV ( Comma Separated Value ) tables.

Put another way, you ONLY need to read the CSV data, and, each CSV file will contain all of the data needed ( you said "3 CSV's we will delete them. The next time such a query is needed there will be a new set of 3 CSV's fiels with new data for the qeury." ), rather then merely be 'additive' to data already in the database.

If this is true, then perhaps, this can best be done by creating three database TEXT tables in Base. A TEXT table means, you have a 'definition' of a table including Table Name, Field Name, Field Type -- it does NOT contain index. This way, ALL the data, is residing in the CSV files, located in the same directory as your ODB file ( in Windows, typically in the Documents and Settings\user\My Documents\ directory . . . not in the database.

Queries may then be created and executed as is necessary.

If this technique is what what is needed or, best in your case, I will 'walk' you through the process. It is NOT difficult. Additionally, it would be helpful to have an idea of ABOUT how many rows each of the three CSV's have. If the number is 100,000 each, then, a TEXT table is NOT the best approach. If it is not too many then TEXT tables, in your case, might be best.

Sliderule
Back to top
View user's profile Send private message
smartfinds
General User
General User


Joined: 18 Jul 2007
Posts: 8

PostPosted: Sun Jul 22, 2007 9:20 pm    Post subject: Reply with quote

Hello,

Yes, your description of the situation is accurate. We tried to following some procedures to import CSV files that we found on the OpenOffice website, but it clearly was not accurate or we missed something.

The amount of date we are importing at a time would be less than 5,000 records at a time in any one given table.

If I understand what you wrote, then it sounds like we need to create 3 TEXT tables, define the fields and somewhere along the line we will ask it to grab the data from the CSV files.

This is our first run at using OpenOffice and we would certainly like to learn how to use this over the M$ option on the market. I have to admit that importing a CSV file in the M$ product is very easy. I'm sure it is here too, once we can figure out the details.

Thanks for your help.
Back to top
View user's profile Send private message Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Jul 22, 2007 10:01 pm    Post subject: Reply with quote

smartfinds:

OK . . . let's talk about creating a TEXT FILE ( reading a CSV file ) within OpenOffice BASE.

According to HSQL documentation found here: http://www.hsqldb.org/doc/guide/ch06.html#N1102D
HSQL Documentation wrote:
Definition of Tables

Text Tables are defined similarly to conventional tables with the added TEXT keyword:

CREATE TEXT TABLE <tablename> (<column definition> [<constraint definition>])

In addition, a SET command specifies the file and the separator character that the Text table uses:

SET TABLE <tablename> SOURCE <quoted_filename_and_options> [DESC]

Therefore . . . to proceed:
  1. Open your database file . . . OR . . . create a new empty one
  2. From the Menu: Tools -> SQL...
  3. In the Command to execute box enter the CREATE TEXT TABLE SQL, for example:
    Code:
    CREATE TEXT TABLE "export-nuts-global" ("your_field_name_01" DATE, "your_field_name_02" TIME,  "your_field_name_03" DATETIME, "your_field_name_04" VARCHAR(50), "your_field_name_05" INTEGER,  "your_field_name_06" NUMERIC );

  4. Press the Execute button
  5. If an error, based on message, correct it
  6. When Command successfully executed . . . tell OpenOffice the location and name of your CSV file . . . AND . . . how it is delimited AND whether the first line contains column names ( ignore_first=true OR ignore_first=false ). For example:
    Code:
    SET TABLE "export-nuts-global" SOURCE "export-nuts-global.csv;ignore_first=true;all_quoted=false";

  7. Press the Execute button
  8. Repeat above items each TEXT FILE you are creating
  9. When finished . . . Close button
  10. From the Menu: View -> Refresh Tables
  11. Your TEXT TABLES should be visible
  12. OpenOffice will be READING your CSV ( text files ) from the same directory where your .odb file is located. In Windows, this is typically . . . C:\Documents and Settings\user\My Documents
I hope this helps, please be sure to let me / us know.

Sliderule
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