[Solved] Importing .ods from Calc into a Base Table

[Tron]

I am trying to import an .ods spreadsheet from Calc into a Base Table but am having a hard time figuring out how to do this. Does anyone have any advice? Thank you in advance!

[r4zoli]

The data on sheet needs to be in form that Base can consume. Each column has same data in it (formatted with same format), has name in first row, which can be used a field name in Base.
Drag sheet from Calc to Base Table pane, the copy table wizard starts, add primary key as new field or select on field, which has different values in each row. In next step select fields, data types, field length, etc.
That's all.

[Tron]

That was successful! Thank you!

[macketh]

Can someone please expand on this: "Drag sheet from Calc to Base Table pane..". I am lost. Tried it every which way. Am i missing something?

Thanks!

[Villeroy]

If the database is registered hit F4, drop down the registered name, drag the selected list range onto the icon which is labeled "Tables". Type in the name of the table where to append data and map the fields to each other.
Unfortunately you can not drag it onto the specific table where you want to append data to. This works well when you open the database window. There you can drag (or paste) the cell range onto a specific table in order to append data.

Don't let the wizard create a new table from spreadsheet data. Spreadsheets tend to be inconsistent. Design your database tables carefully with the required field types, primary key and indices, nullable or not, with or without default values before you import any data from dirty sources. You may get some error message when data are inconsistent (unallowed types, duplicates, missing values). But that is a good thing actually. Tweak your spreadsheet data until they comply with the consistency rules of your database.

[macketh]

Excellent, thank you very much! This worked.

I was well off the mark the first time around..

[macketh]

After some time trying this, I have to ask: Is there a different way of doing this? I am working with a datasource (csv) of 500,000+ records and Calc appears to freeze up everytime i go beyond ~25k records.

Any help is appreciated.

[Villeroy]

So we do not talk about a spreadsheet at all. CSV is a database exchange format. There is no need to load csv into a spreadsheet unless you really want to use the data in a calculation model of a spreadsheet.
Connect a Base document to a csv directory with similar csv files and drag (copy) tables from one Base document to another.
It is also possible to connect a HSQLDB table directly to a csv file.
http://user.services.openoffice.org/en/ ... 83&t=23260
http://hsqldb.org/doc/guide/ch06.html

[r4zoli]

[Tutorial] Using csv/text files as editable data source.

[macketh]

Thanks! This looks applicable to what i am doing.