| View previous topic :: View next topic |
| Author |
Message |
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
 Posted: Wed Jul 18, 2007 7:55 pm Post subject: Table Relationships |
 |
|
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 |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Jul 18, 2007 8:52 pm Post subject: |
|
|
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:
- Open your database in Openoffice
- Press Queries on the left side
- Under Tasks, press Create Query in Design View...
- Under Add Table or Query . . . press the Table you want to use in the query, press Add button
- Repeat step above for each Table you want to include in your query
- 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 |
|
|
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
| Posted: Wed Jul 18, 2007 9:09 pm Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Jul 18, 2007 9:17 pm Post subject: |
|
|
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 |
|
|
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Jul 18, 2007 9:36 pm Post subject: |
|
|
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 |
|
|
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Jul 18, 2007 9:55 pm Post subject: |
|
|
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 |
|
|
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
| Posted: Wed Jul 18, 2007 10:04 pm Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Wed Jul 18, 2007 10:09 pm Post subject: |
|
|
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 |
|
|
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
| Posted: Thu Jul 19, 2007 9:15 pm Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Fri Jul 20, 2007 8:09 am Post subject: |
|
|
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 |
|
|
smartfinds
General User
Joined: 18 Jul 2007
Posts: 8
|
| Posted: Sun Jul 22, 2007 9:20 pm Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Sun Jul 22, 2007 10:01 pm Post subject: |
|
|
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:
- Open your database file . . . OR . . . create a new empty one
- From the Menu: Tools -> SQL...
- 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 ); |
Press the Execute button
If an error, based on message, correct it
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"; |
Press the Execute button
Repeat above items each TEXT FILE you are creating
When finished . . . Close button
From the Menu: View -> Refresh Tables
Your TEXT TABLES should be visible
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 DocumentsI hope this helps, please be sure to let me / us know.
Sliderule |
|
| Back to top |
|
|
|
