Creating a DataBase / Spreadsheet

[usascottwright]

I have 13513 records in an excel spreadsheet that I want to create a database for. I have used MS Access before and all it was is create your file which is the spreadsheet and then create a form so it looks better when you enter the data

I have created a database but am not able to import the data. I have looked for a tutorial and none of them gives step by step directions on how to do that. Couldn't find anything in youtube of any helpfulness.

Does somebody have step by step instructions on OpenOffice data base to get this up and running? via notes or video.

[Villeroy]

Copy or drag the cell range into the blank tables section of the new database. A wizard pops up where you specify the details about column types, column order, columns to skip.

Please consider:
Since most spreadsheets are full of errors, this method may fail since a database structure can not store any amalgam of numbers and text. The process is a little bit like turning water into wine or something wooden into steel.
A plain copy of a cell range copied into some database does not turn your spreadsheet into a relational database where multiple tables are connected to a consistent multi-dimensional data structure.
Spreadsheet ranges that do not show a simple list do not apply to databases (cross-tables, changing column count,...).
In my practical experience one should create an empty database with all the tables, relations, primary keys and indices. Then copy the raw data into the prepared structure.

In other words: Discussing software features without data is pointless when the topic is about data processing.
It would help if you could upload a small spreadsheet with a few rows of dummy data similar to your actual data.
[Forum] How to attach a document here

[usascottwright]

Copy and past did not work. Here are the steps I did to try that.
From the spreadsheet B2 and selected down to B5 / copy
Went near the bottom of the form entry screen and clicked on Data Source as a table and open showing the data base near the top
Went to the 1st entry of the "Last Name" and selected Paste
All it did was pasted the 4 names in to one record field.

I am going to be updating massive amounts of data into this data base, so detailed instructions on how to import data would be extremely helpful. It shouldn't be this hard to import data.

A data base is all of consists of a data source which in reality is a spreadsheet and the other part of the data base is you can view it in a form view.

I appreciate your help.

Perhaps a programmer could make this part a little easier as simple as: File / Import /Select your new data source / "Wizard pops up to match your data fields with current data fields". / ok / Thats it your data was added it took 24 seconds.

I have attached a few rows of data

[Villeroy]

So it is the mere size of data which makes you want a database instead of the spreadsheet?
Well, there is nothing to do other than copy and paste all the data as plain text. The first column "date" does not contain any dates and calculatable numbers are not involved.

This is what I do:
Copy A1:I10 (or only the columns you want)
Create a new database file.
Go to the tables section and paste.
Give some table name
Choose "Data and Definition"
[x] Create primary key
[x] Use first line as column names (the first row with labels date,p1lastgive,detail,city,...)
[Next]
Select the copied columns you want to include. You can also modify the order of columns (which is of no importance since you can pull any order of columns from the same table at any time).
[Next]
Now you should click every single field and think about some details. Do NOT use the automatic column type recognition.
Is this field required? I see column B as a candidate. If yes, the respective spreadsheet column must not contain any gaps.
What is the max. length of the field? Default is 256. The smaller you set the restriction, the better the performance of your database. One candidate is the "postal" column F with a maximum of 5 character, but only if the spreadsheet follows this rule.
After you have set some reasonable restrictions you hit [Create].
Now you have the same data in a database where you can filter, sort and edit with more performance, add input forms, output reports and queries to work with defined subsets of rows and columns.

[Villeroy]

A few useful spreadsheet formulas in this context:
=COUNT(A$2:A$999)=ROWS(A$2:A$999) returns TRUE when there are only numbers without gaps in A2:A999
=COUNTA(A$2:A$999)=ROWS(A$2:A$999) same with any content
=MAX(LEN(A$2:A$999)) [entered with Ctrl+Shift+Enter instead of Enter] returns the maximum lenght in A2:A999

[usascottwright]

1. Create your data in a spreadsheet
2. Save the spreadsheet
3. Copy all the data that is in the spread sheet

Open Office.org
4. Create a new data base / Next

At this point you said to paste the data. This is not an option.

5. Your choice to register or not to register your database
6. Save your new database / choose your location and name and click save.

Database "Select Tables"
You have three options to choose from

Create Table in Design View
Use Wizard to Create Table <<==============
Create View

Step 1
Category - Personal
Sample Tables - Addresses
Available Fieds
Select the fields you want and move them over using the > button
Click next

Step 2
Click Next

Step 3
Create a primary key / Next

Step 4
What do you want to name your table
Addresses

What do you want to do next?
O Insert data immediately / Finish

You are now on your first row
Edit
Paste
Error Message "OpenOffice.org 3.2" The inserted text exceeded the maximum lenght of this text field. The text wasa truncated. OK

did not create rows of data, it just tried to paste all data into the ID field.

[Villeroy]

Finish the database creation until you are prompted to save the new database document.
The registration is not that important. A registered database is visible in various places when you want to use the data in other office documents (Writer/Calc). You can catch up on this later (Tools>Options>Base>Databases).

When you see the window of the new database, switch from the forms section to the tables section and paste your spreadsheet data.

The wizards for creation of tables, queries and forms are not useful, sometimes even misleading.

"Create table in design view" would be more productive since you get a more adequate idea about your tables.
In your particular case, where ony text but no numbers nor dates are involved, you can create the table in one go with the clipboard import together with a new primary key.
The following screenshots are borrowed from another topic. You should check the option to create a new primary key:


This is the last step with your data, I selected the 2nd column, set it to mandatory with a max lenght of 50:

And the attached file is the database I get.

[usascottwright]

Creating a database, after I save the database, it automatically brings me to the Database - Tables area. I am not in the forms section so again a step must have been missed.

I have included a screen shot.

When you say "When you see the window of the new database, switch from the forms section to the tables section and paste your spreadsheet data." From this window where am I pasting it? You seem to be missing a step. Is a task supposed to be selected first?[attachment=0]databasescreenshot.jpg

[Villeroy]

Right-click the empty area below the work "Tables" and paste or call menu:Edit>Paste... or drag the selected cells into the empty area