Import data from spreadsheet to base

users
#1

I've checked both LibO and OOo for a how-to or a chapter on this, without luck, and I've checked the Nabble interface to the users maillist.

Is there any easy way to get an address list in a spreadsheet into a base database?

TIA

//James

#2

Is your spreadsheet set up as a database with fields? Meaning in the top row, something like Column 1 <firstname>, Column 2 <lastname>, Column 3 <street>, etc. If it is set up like that, I know you can import that data into base. If the spreadsheet is setup as a database with fields in the top row, you could probably do it from there if it is saved as a database and not just a spreadsheet document. I really don't know though since you are getting out of the realm of my knowledge. I know you can import database information in calc into base, and I know you can have great success printing labels from base with the table style templates.

#3

Thanks, Don, this isn't my day.

I can see no way to save my calc as a database under Save as. I can find no import option in base. And copying the entire sheet and pasting to the first field on a form doesn't work.

Another thing: the column names in the base table are not in exact conformance with the column names in the calc sheet. The one is in English, for example, the other in Swedish, and I was hoping for a procedure to assign field names in the one to field names in the other.

//James

#4

1. Open Calc and the sheet you want to export to Calc
2. Activate the data source pane (F4)
3. Select the data that need to be exported (Ctrl + * will select all the data)
4. Drag the selection to the left pane of the source pane, onto the database you're targetting. When the table branch is shoiwn, you might see the cursor changing shape
5. Drop

A wizard should show up. Just follow the directions and you should be all set.

HTH,

#5

Hi Jim,

In Windows I used Word Perfect Office until OpenOffice was in late beta, roughly 10 years ago. I moved to OpenOffice back then, except then OpenOffice 1.0 did not have a database, so I continued to use Paradox from the Word Perfect Office suite. Once OpenOffice had a database, I think it was in version 2.0, I migrated my Paradox database to OpenOffice base. That process involved exporting the Paradox data to a QuatroPro spreadsheet, converting it to an Excel spreadsheet so it could be read in OpenOffice calc, and then importing the data into OpenOffice base. That was all done 7 to 8 years ago, so I don't remember the details. I did find a tutorial on line back then that guided me in the process, so maybe you can find a tutorial which will help you out. I'm sorry I can't do better.

Don

#6

I can see no way to save my calc as a database under Save as. I can
find no import option in base. And copying the entire sheet and
pasting to the first field on a form doesn't work.

1. Open Calc and the sheet you want to export to Calc

Is that Open Calc and the sheet you want to export to Base?

2. Activate the data source pane (F4)

F4 doesn't have any effect on my Mac. Cmd+F4 closes the sheet. Shift+Cmd+F4 has no effect. I double clicked on the Base table and got up something that looks like a Calc sheet with only one line.

3. Select the data that need to be exported (Ctrl + * will select all the data)

I only selected the columns in the Calc sheet that are equivalent.

4. Drag the selection to the left pane of the source pane, onto the database you're targetting. When the table branch is shoiwn, you might see the cursor changing shape

It did indeed change shape. I got a green + attached to it.

5. Drop

A wizard should show up. Just follow the directions and you should be all set.

No wizard. :frowning:

But thanks for trying.

//James

#7

Oups,

1. Open Calc and the sheet you want to export to Calc

                                                    ^^^^
                                                    Base

#8

1. Open Calc and the sheet you want to export to Calc

Is that Open Calc and the sheet you want to export to Base?

well, yes, indeed :slight_smile:

2. Activate the data source pane (F4)

F4 doesn't have any effect on my Mac. Cmd+F4 closes the sheet.

Dunno the English menus. Should be smthg like Display/Data sources.

3. Select the data that need to be exported (Ctrl + * will select
all the data)

I only selected the columns in the Calc sheet that are equivalent.

4. Drag the selection to the left pane of the source pane, onto the
database you're targetting. When the table branch is shoiwn, you
might see the cursor changing shape

It did indeed change shape. I got a green + attached to it.

5. Drop

A wizard should show up. Just follow the directions and you should
be all set.

No wizard. :frowning:

:((

Sorry, I have no clue about LibO/OOo for Mac.

Hopefully someone with a better knowledge of these systems will show up.

#9

Hi James,

James Wilde schrieb:

I've checked both LibO and OOo for a how-to or a chapter on this, without luck, and I've checked the Nabble interface to the users maillist.

Is there any easy way to get an address list in a spreadsheet into a base database?

Yes, it is easy.

Version A
You do not have a database already, you want to retain the spreadsheet:
File > New > Database
Connect to an existing database > Choose 'Speadsheet' > Next.
Browse to your Calc document > Next > Finish.

Version B
You have already a database and want to import your speadsheet as new table:
Open the odb-File and go to 'Table' section. Drag the window to half of the screen.
Open the calc-File, drag its window to the other half of the screen.
Mark your data in the spreadsheet and drag them to the table section in the data base window. (You ca use the clipboard as well.)
Follow the wizard. If you do not want to use the generated primary key, you have to select one (or more) of your columns to be the primary key in the second (or third?) step. Use the context menu then.
Do not trust the type detection, but set every type by yourself.

Version C
You have already a database and want to import your spreadsheet into a given table.
Nearly the same as in version B, but on the first dialog page of the wizard you have to choose to append your data.
In the second step you can match the columns of the spreadsheet to the columns of the data base table. Rearrange the columns in the dialog list of the spreadsheet source, not in the part of the database table.

Kind regards
Regina

#10

Hi James,

2. Activate the data source pane (F4)

F4 doesn't have any effect on my Mac. Cmd+F4 closes the sheet. Shift+Cmd+F4 has no effect. I double clicked on the Base table and got up something that looks like a Calc sheet with only one line.

Do you have a "Fn" key on your Mac keyboard ? If so, press that first
and while keeping pressed, press on F4. If it a tower Mac or Mac mini as
opposed to a Macbook then I'm not sure because it will depend on your
keyboard. This is something that has always bothered me with the default
key bindings that both OOo and LibO use, because most of them just
didn't work as the documentation / help stated. It is partly Apple's
fault for assigning specific OS functionality to the function keys, but
also one would have thought that the OOo/LibO development cycle would
have picked this up by now.

3. Select the data that need to be exported (Ctrl + * will select all the data)

I only selected the columns in the Calc sheet that are equivalent.

You can select all of the data in the sheet, the import wizard will let
you choose which columns to import and how to match them to your Swedish
entries. The real gotchas are the field type detection, which seems
sometimes to have a mind of its own against all commonsense, so as
Regina has suggested elsewhere, you are better off checking that the
field types suggested by the import wizard correspond to the type of
data being imported.

Alex

#11

We of the Documentation Team are in DESPERATE need of Mac users to modify
these key / strokes for the Documentation, could you consider joining the
documentation@libreoffice.org mailing list and see what can be done ?

Thanks either way

Rogerio

#12

Hi Rogerio,

We of the Documentation Team are in DESPERATE need of Mac users to modify
these key / strokes for the Documentation, could you consider joining the
documentation@libreoffice.org mailing list and see what can be done ?

I am already on it :-)) but unfortunately am a bit busy translating the
Getting Started with Base Guide into French :-/ The problem with the key
bindings on Mac is that I don't actually know all of them myself anyway !!

Alex

#13

Hi James,

James Wilde schrieb:

I've checked both LibO and OOo for a how-to or a chapter on this, without luck, and I've checked the Nabble interface to the users maillist.

Is there any easy way to get an address list in a spreadsheet into a base database?

Yes, it is easy.

Version A
You do not have a database already, you want to retain the spreadsheet:
File > New > Database
Connect to an existing database > Choose 'Speadsheet' > Next.
Browse to your Calc document > Next > Finish.

Thanks, Regina, this works. I'll try and live with this method.

Version B
You have already a database and want to import your speadsheet as new table:
Open the odb-File and go to 'Table' section. Drag the window to half of the screen.
Open the calc-File, drag its window to the other half of the screen.
Mark your data in the spreadsheet and drag them to the table section in the data base window. (You ca use the clipboard as well.)

This step doesn't work. I mark the entire sheet and try to drag a cell - any cell, they're all marked - into the table section of the odb window, but nowhere does it let me drop with any result.

I have, however, found out that it starts working - it's still running in the background as I type - if I have an empty database with no tables. Finished now - "The data content could not be loaded. S1000 General error java.lang.NullPointerException in statement [SELECT * FROM "Medlemmar"].

Follow the wizard. If you do not want to use the generated primary key, you have to select one (or more) of your columns to be the primary key in the second (or third?) step. Use the context menu then.
Do not trust the type detection, but set every type by yourself.

Version C
You have already a database and want to import your spreadsheet into a given table.
Nearly the same as in version B, but on the first dialog page of the wizard you have to choose to append your data.

Since B doesn't work, this one is obviously not going to work either. It doesn't.

In the second step you can match the columns of the spreadsheet to the columns of the data base table. Rearrange the columns in the dialog list of the spreadsheet source, not in the part of the database table.

//James

#14

Hi James,

2. Activate the data source pane (F4)

F4 doesn't have any effect on my Mac. Cmd+F4 closes the sheet. Shift+Cmd+F4 has no effect. I double clicked on the Base table and got up something that looks like a Calc sheet with only one line.

Do you have a "Fn" key on your Mac keyboard ? If so, press that first
and while keeping pressed, press on F4. If it a tower Mac or Mac mini as
opposed to a Macbook then I'm not sure because it will depend on your
keyboard. This is something that has always bothered me with the default
key bindings that both OOo and LibO use, because most of them just
didn't work as the documentation / help stated. It is partly Apple's
fault for assigning specific OS functionality to the function keys, but
also one would have thought that the OOo/LibO development cycle would
have picked this up by now.

Thanks Alexander. I was doing it on a Mac mini, but I can try and create the db on my Macbook and move it over.

//James

#15

Hi James,

James Wilde schrieb:

Version B You have already a database and want to import your
speadsheet as new table: Open the odb-File and go to 'Table'
section. Drag the window to half of the screen. Open the calc-File,
drag its window to the other half of the screen. Mark your data in
the spreadsheet and drag them to the table section in the data base
window. (You ca use the clipboard as well.)

This step doesn't work. I mark the entire sheet and try to drag a
cell - any cell, they're all marked - into the table section of the
odb window, but nowhere does it let me drop with any result.

Do not mark an entire sheet but only the data. I have written a HowTo
with pictures for you. Perhaps it makes clear how it should work:
http://www.rhenschel.homepage.t-online.de/HowToImportDataFromCalcToBase.odt

Kind regards
Regina

#16

Thanks, Regina. I'll take a look at it tomorrow.

//James

#17

There's so far been no message from me saying thanks for solving the problem, mainly because I'm having problems with the different solutions and I want to have them all documented before I reply.

Regina's method 1 works. I get error messages with method 2 and, I think, method 3, and I want to run them both again.

As Arnie said, I'll be back.

//James