Connect Base to a spreadsheet

Discuss the database features
Post Reply
coppens1
Posts: 46
Joined: Fri Oct 22, 2010 4:26 pm

Connect Base to a spreadsheet

Post by coppens1 »

 Edit: Split from How To Import spreadsheet into Base? since this subject from a new person is different. Unless you are offering assistance to the person who originally opened the topic, coppens1, create your own topic when you need help. You can reference the other topic if you want, as I have done here.
-- MrProgrammer, forum moderator 
OpenOfficeBaseSupport 110122 Sheet 1 icon doesn't show

Dear OpenOfficeBaseSupport, I am trying to create an odb database using a spreadsheet. See the instructions below.

I am using a spreadsheet originally called Xmas Mailing Address List. The first time I tried the instructions, everything went well with the name Xmas Mailing Address List.odb. In the Open Office Base screen it showed a table.

But I made modifications to the spreadsheet, changed the data, added columns, and named it Xmas Mailing Address List1, then went thru the instructions again and named the new base file Xmas Mailing Address List1.odb. The new database shows no table in the tables section of the OpenOffice Base screen. I have tried several times. No luck. Then I tried a simple spreadsheet with only a few XXXs in it, and a table showed in the tables section of the OpenOffice Base screen. What am I doing wrong? JC

---------------------------------------------------------------------------------
1 Launch Base, the database component of OpenOffice.org.
2 Click "Connect to an existing database."
3 Click on the list box and select the "Spreadsheet" option.
4 Click the "Next" button.
5 Click "Browse." Locate and click the spreadsheet file you want to insert.
6 Click "Next" and then click the "Finish" button.
7 Save your new database file under a new filename.
8 Double-click the "Sheet1" icon in the "Tables" view port to see your data.
------------------------------------------------------
OpenOffice 3.1 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect Base to a spreadsheet

Post by Villeroy »

coppens1 wrote:and named it Xmas Mailing Address List1
What did you name "Xmas Mailing Address List1"?
The file name of the spreadsheet document?
A new sheet's name in the spreadsheet document?
Renamed the old sheet?
Another database document?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
coppens1
Posts: 46
Joined: Fri Oct 22, 2010 4:26 pm

Re: Connect Base to a spreadsheet

Post by coppens1 »

Reply to Villeroy:
Dear Villeroy, thanks for your resp;onse.

1.What did you name "Xmas Mailing Address List1"?
2.The file name of the spreadsheet document?
3.A new sheet's name in the spreadsheet document?
4.Renamed the old sheet?
5. Another database document?

Answers 1,2,3,4
I made modifications to the spreadsheet, changed the data, added columns, and saved it as "Xmas Mailing Address List1" a Microsoft Excel 97 2003 worksheet.

Answers 5
I used the instructions listed in my post of 1/11/22 and saved the new database as 'Xmas Mailing Address List1.odb

JC
OpenOffice 3.1 on Windows XP
coppens1
Posts: 46
Joined: Fri Oct 22, 2010 4:26 pm

Re: Connect Base to a spreadsheet

Post by coppens1 »

Dear Villeroy,

OpenOfficeBaseSupport 120122 Solved, but why? Sheet 1 icon doesn't show

By trial and error I found that when I inserted a new column b prior to my original data, and saved the spreadsheet and database as V1Xmas Mailing Address List, the Sheet 1 icon doesn't show when you start the database. When I delete column b, the spreadsheet and database work.

Why would inserting a column make the spreadsheet unuseable as a database? I SAVED both the spreadsheet and the database AS V1Xmas Mailing Address List'. JC
OpenOffice 3.1 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect Base to a spreadsheet

Post by Villeroy »

I can't confirm the issue. I append a column, save, restart the office suite (AOO 4.1.11) and Base shows the column.
According to your signature you are using OOo 3.1 on WinXP. May be it was an issue 12 years ago? Who knows?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
coppens1
Posts: 46
Joined: Fri Oct 22, 2010 4:26 pm

Re: Connect Base to a spreadsheet

Post by coppens1 »

Actually I am using Open Office Version XXXXX.7 . I will have to look up the version, but it is recent. I try to keep all my computers updated to the latest version of Open Office.

I can email you the spreadsheets that work and don't work if you like. JC
OpenOffice 3.1 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect Base to a spreadsheet

Post by Villeroy »

So you are using this for mail merge.
Open the Writer document and hit Ctrl+F9 until the mail merge fields show something like "DatabaseName.TableName.ColumnName".
Call menu:Data Sources for the datasource window.
In the left pane right-click the database name and choose "Edit Database File...". This opens the actual database document which is used by the mail merge document.
If this is the wrong database document, call menu:Tools>Options>Base Databases, select the registered name and pick the right database document and open that corrected database document.
The status bar of the database document reads like "Spreadsheet | C:\Path\MySpreadsheet.ods" which is the other end of the bridge. If it points to the wrong spreadsheet document, call menu:Edit>Database>Properties... and browse to the correct spreadsheet.
[Spreadsheet] -------> [registered Base document name] --------> [Mail merge fields]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
coppens1
Posts: 46
Joined: Fri Oct 22, 2010 4:26 pm

Re: Connect Base to a spreadsheet

Post by coppens1 »

OpenOfficeBaseSupport Villeroy 130122 Using OpenOffice 4.1.7.

Dear Villeroy, I am Using OpenOffice 4.1.7. I usually store my spreadsheets as Excel Spreadsheets for portability.

I am not using the spreadsheet for mailmerge, but as part of a tutorial, “GS6008-2018 GettingStartedWithBase.pdf”.
 Edit: Perhaps this: Ch8 - Getting Started with Base?
If not, coppens1 is more likely to get help if they supply a link to the tutorial and to the specific page
-- MrProgrammer, forum moderator 
I will try what you suggested. Have a nice day. JC
OpenOffice 3.1 on Windows XP
coppens1
Posts: 46
Joined: Fri Oct 22, 2010 4:26 pm

Re: Connect Base to a spreadsheet

Post by coppens1 »

Dear Villeroy, I started to apply your solution, but the problem is Not Mailmerge, but a problem between Open Office Calc & Base programs.

Why would inserting a column make the spreadsheet unuseable as a database? I renamed both the revised spreadsheet and the new database when I made the changes. Its like base remembers the old spreadsheet, and is not handling this action as a new spreadsheet and database. JC
OpenOffice 3.1 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect Base to a spreadsheet

Post by Villeroy »

Background: Explorer
Foreground: Base
Connection spreadsheet document to database document
Connection spreadsheet document to database document
Background: Base
Foreground: Calc
Sheets as database tables
Sheets as database tables
Background: Explorer
Foreground: Dialog Tools>Options>Base>Databases
Database registration under Tools>Options...
Database registration under Tools>Options...
Last edited by Villeroy on Wed Jan 19, 2022 12:21 am, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Connect Base to a spreadsheet

Post by Villeroy »

Background: Writer with data source window
Foreground: Dialog Tools>Options>Base>Databases
Connection to mail merge (database fields)
Connection to mail merge (database fields)
Data source window
After adding a PHONE column to the spreadsheet
After adding a PHONE column to the spreadsheet

Background: Writer
Foreground: Dialog Edit>Exchange database...
After renaming Sheet1 to ADDRESSES and calling Edit>Exchange Datbase...  in Writer
After renaming Sheet1 to ADDRESSES and calling Edit>Exchange Datbase... in Writer
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply