Dbase

Discuss the database features

Dbase

Postby bandslak » Thu Nov 28, 2019 7:48 pm

So, I finally found out how I could import my Open Office spreadsheet into the database worksheet

My problem now is that it will not allow me to enter/edit any of the data. Years ago I use to work with a few database programs but have since forgotten a lot.

Also, at one point I was able to change the Field properties but sometimes it seems I cannot even access them. When I create an entry form (and if I have set the properties (length etc.) the fields on the form are way out of wack.

I am going to seach here for more documentation but in the meantime does anyone have suggestions?

Thanks
OpenOffice 4 Windows 8.1
bandslak
 
Posts: 8
Joined: Fri Feb 03, 2017 4:09 pm

Re: Dbase

Postby Villeroy » Thu Nov 28, 2019 8:59 pm

bandslak wrote:So, I finally found out how I could import my Open Office spreadsheet into the database worksheet

What did you do exactly?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27606
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dbase

Postby bandslak » Thu Nov 28, 2019 10:16 pm

do you just mean to import my data (the rest of my post is not shown here). If so, I just followed the instructions on the screen to import the data......
OpenOffice 4 Windows 8.1
bandslak
 
Posts: 8
Joined: Fri Feb 03, 2017 4:09 pm

Re: Dbase

Postby Villeroy » Thu Nov 28, 2019 11:43 pm

There are at least 2 ways. What exactly did you do? I don't see any instructions on my screen.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27606
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dbase

Postby UnklDonald418 » Fri Nov 29, 2019 12:36 am

If you followed the instructions given at another of your posts Re: importing data
then your database tables are read only because that type of connection results in tables without primary keys.

For one approach to get database tables that can be edited
Select what you want to copy from the spreadsheet into the clipboard (<Ctrl>-C)
Create a new database, that first one has the wrong type of connection and won't work.
Select Tables in the left pane of the new database and in the blank area under the heading Tables paste the contents of the clipboard (<Ctrl>-V)
That will open a Copy Table dialog.
You can use the default name assigned to the table or type a different name if you like.
You want to select the Definition and data option
if your spreadsheet has column headings then select Use first line as column names.
Select Create primary key.
Select Next to get to the Apply columns page where you select which columns and in which order you want to copy into your database table.
Select Next to get to the Type formatting page.
Along the left of the dialog you will see a column named ID at the top of the list. That is very important so don't mess with that.
Select each of the other column names and look in the Column information area to see what type of information is being copied.
By default all columns are set to Text. If you have columns with numbers or dates you can select the Auto button towards the bottom right of the dialog page and it will guess at what data type to use. In my experience it doesn't always get it right.
You can type in a different name for the column if you like.
If you don't like the type you can select a different Field type from the list of options
Finally select Create to copy the table into your database.
Now if you open the table you can edit the data, but the formatting there is quite limited.
Forms have many more formatting options, so for a simple table use the Wizard to Create a Form.

Don't be afraid to delete a table (or Form) and start over if things don't turn out right. But if you end up deleting a few tables select Tools>SQL and execute this command
Code: Select all   Expand viewCollapse view
SHUTDOWN COMPACT;

When you exit the database any wasted space will be recovered.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1280
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Dbase

Postby bandslak » Fri Nov 29, 2019 7:18 pm

Thanks for the reply....

I copied my data from the spreadsheet to the clipboard. The spreadsheet consisted of 7 fields and 707 rows. (Which created a problem)

I attempted to create a database but Open Office would not open so I could not access the menu choice to create it.

I could for instance, copy only the first 50 rows but don't want to have to manually enter the remaining data by hand, and as yet I haven't seen anything that will allow me to enter it in bulk or say 50-100 rows at a time. Is that possible?
OpenOffice 4 Windows 8.1
bandslak
 
Posts: 8
Joined: Fri Feb 03, 2017 4:09 pm

Re: Dbase

Postby UnklDonald418 » Fri Nov 29, 2019 7:49 pm

Yes, create a database table with the first 50 rows of data.
Then additional rows can be added by selecting Append data option on the first page of the Copy Table dialog.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1280
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Dbase

Postby Villeroy » Fri Nov 29, 2019 8:17 pm

menu:File>New>Database...
Connect to existing database
Type: Spreadsheet
---------------------------------------
This creates a pseudo-database which is connected to your spreadsheet. Your data are still saved in the spreadsheet and your preferred spreadsheet application remains the editor for these data. Such a pseudo-database is good enough to fill serial letters and labels with data from a spreadsheet list.
Image

###########################################################################################################

To create a real database and fill it with sheet data you start with
------------------------------------------
menu:File>New>Database...
Create new database
After saving the new database, you get the same window with 4 sections Tables, Queries, Forms and Reports but with a different status bar:
Image
-------------------------------------------------------------------------------------------------------------
At this point I use to recommend to create a complete relational database first and then paste data into the tables but I have a gut feeling that you don't know much about databases.

Copy the whole spreadsheet range.
Select the Tables section and right-click>Paste... into the blank space.
A multi-step dialog pops up.
Enter a table name, option "definition and data" .
Check option create index field.
If the copied sheet cells include a first row of column names, check that option too.
In the next step you can choose which fields (columns) should be imported from clipboard.
The third step may be most important for a functional database. Specify the name and correct data type for each column. If you do nothing, everything will be imported as text plus the index field as integer. Since your source data come from a spreadsheet and all spreadsheets tend to be a huge mess, you can expect various import errors, for instance if a column of numbers includes one text value.

As already mentioned, you could avoid a lot of trouble if you manage to develop a working database with some dummy data first and then import well tested sheet data into the working database structure. Some spreadsheet formulas should test the sheet data for consistency.

##################################################################################

An intermediate type of database can be created like this:
Create a new directory and save individual sheets as dBase (dbf) files within the new directory.
Then connect a new Base document to the directory.
menu:File>New>Database...
Connect to existing database
Type: dBase
---------------------------------------
Image
Base can be edit, filter and sort a dBase database in many ways that are not possible in spreadsheet applications but the overall database functionality is very limited, i.e. there are not much calculation functions availlable and you can not have interrelated tables.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27606
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests