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

Creating tables and queries

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

Postby Tron » Thu Nov 10, 2011 4:58 pm

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!
Last edited by Tron on Fri Nov 11, 2011 6:59 pm, edited 1 time in total.
OpenOffice.org 3.3.0 Windows XP Version 2002 Service Pack 3
Tron
 
Posts: 6
Joined: Wed Nov 09, 2011 9:45 pm

Re: Importing .ods from Calc into a Base Table

Postby r4zoli » Fri Nov 11, 2011 9:39 am

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.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Importing .ods from Calc into a Base Table

Postby Tron » Fri Nov 11, 2011 6:59 pm

That was successful! Thank you!
OpenOffice.org 3.3.0 Windows XP Version 2002 Service Pack 3
Tron
 
Posts: 6
Joined: Wed Nov 09, 2011 9:45 pm

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

Postby macketh » Thu Dec 15, 2011 11:32 pm

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!
OpenOffice 3.1 on Windows XP
macketh
 
Posts: 4
Joined: Thu Dec 15, 2011 11:25 pm

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

Postby Villeroy » Thu Dec 15, 2011 11:47 pm

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.
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: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby macketh » Fri Dec 16, 2011 12:04 am

Excellent, thank you very much! This worked.

I was well off the mark the first time around..
OpenOffice 3.1 on Windows XP
macketh
 
Posts: 4
Joined: Thu Dec 15, 2011 11:25 pm

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

Postby macketh » Fri Dec 16, 2011 7:47 pm

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.
OpenOffice 3.1 on Windows XP
macketh
 
Posts: 4
Joined: Thu Dec 15, 2011 11:25 pm

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

Postby Villeroy » Fri Dec 16, 2011 8:13 pm

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.
viewtopic.php?f=83&t=23260
http://hsqldb.org/doc/guide/ch06.html
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: 26765
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby r4zoli » Fri Dec 16, 2011 8:14 pm

AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

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

Postby macketh » Fri Dec 16, 2011 8:36 pm

Thanks! This looks applicable to what i am doing.
OpenOffice 3.1 on Windows XP
macketh
 
Posts: 4
Joined: Thu Dec 15, 2011 11:25 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 2 guests