From Calc to Base

[desdichato]

Hello all,

I have a number of spreadsheets that are rapidally becoming large and cumbersome so I would like to put the into Base, last time I did anything like this was about 15 years ago was at an adult learning class and that was with the oppositions software.

Everything I have tried regarding transferring the data across from the spreadsheets to Database has ended up in a single cell.

Is there anyway I can get all my data across preferably without having to retype everything????

Any suggestions?

Thanks

P

[path32]

Hi..

Yes..
1.) Create a database
2.) Register it by
[from base]
Tools> Option> Open Office.org Base> Databases> New [then your database path]
3.) Set a ColumnName From Spreadsheet (Sample EmployeeName then your data)
From Spreasheet press F4
4.) Copy the columns that you want to copy
5.) Drag it to your table

[DACM]

We seem to be getting a bunch of similar questions lately so I've begun compiling a list of the answers:
Spreadsheets to Base and back

• ...and perhaps specifically this link in your case, which is essentially the same as path32's answer above:
  Drag-&-drop Calc spreadsheet to Base to create relational database tables

[desdichato]

Thanks Path 32 and DACM,

I have tried both these suggestion and have been unsuccesful...I'm actually wondering whether it may a computer problem as at the moment I am using a notebook.

Thanks again guys

P

[RoryOF]

It shouldn't be a computer problem, as OpenOffice doesn't know what sort of computer it is running on. Make a simple spreadsheet of three or four lines and two or three columns, and try with that.

[Villeroy]

Hello all,

I have a number of spreadsheets that are rapidally becoming large and cumbersome so I would like to put the into Base, last time I did anything like this was about 15 years ago was at an adult learning class and that was with the oppositions software.

Use pencil and paper to plan your database.
Check the consistency of your sheet data against your plan and fix all inconsistancies (missing data, wrong data types, orphaned relations).
Create the empty database as a complete skeleton to import valid row sets of raw data.
You might need temporary tables for the raw sheet data and later run SQL INSERTs in order to fill detail tables from imported data. If you are familiar with spreadsheets you may be able to use the spreadsheet for creation of foreign keys.

A few spreadsheet formulas should help to find inconsistencies and determin the right field types for the database:
=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 text or number
=MAX(LEN(A$2:A$999)) [entered as array with Ctrl+Shift+Enter instead of Enter] returns the maximum text lenght in A2:A999
=ISNUMBER(MATCH($A2;other_column;0)) returns TRUE if the id in $A2 has at least one counterpart in some other column.
=COUNTIF($A$2:$A$999;$A2)=1 tests if the id in A2 is unique within A2:A999

By the way: Base is not a database. It can be connected to many different types of relational databases. The above outlined data transfer can be used with any type of writable data source once it has been connected to a Base document.

[myrmidon]

I need to do almost the same thing but backwards (I need to use Calc to populate data in Base). If I build Calc to match the existing Base file I should be good to go right?

[Villeroy]

Drag the icon of the table or query into a sheet. That's all.
What else: [Tutorial] Using registered datasources in Calc

You can fill up a free grid with well structured data easily, but putting the unstructured values of a cell range into a structure can be challenging.