Exporting data from Calc to Base

Discuss the spreadsheet application

Exporting data from Calc to Base

Postby HollyK » Wed Mar 06, 2019 5:28 am

I have a spreadsheet with information on alum. members. Is it possible to export this data to a database file that can printed out as a directory as opposed to a spread sheet?

Thanks!
Last edited by robleyd on Wed Mar 06, 2019 7:53 am, edited 1 time in total.
Reason: Remove green tick
Open Office on 4.1.5 Windows 7
HollyK
 
Posts: 2
Joined: Wed Mar 06, 2019 5:21 am

Re: Exporting data from Calc to Base

Postby Zizi64 » Wed Mar 06, 2019 7:49 am

What did you meat "a database file that can print out as a directory "?
How do you want to print it "as directory"?

You can format the spreadsheets the print, but not the databases. You must write a Report (in a database manager software) based on the database to make a formatted print...
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8354
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Exporting data from Calc to Base

Postby HollyK » Wed Mar 06, 2019 9:54 am

What I am trying to do is print a directory (list) of the people listed on the speadsheet. The fields are separate as to first name, address, phone, etc. Thanks!
Open Office on 4.1.5 Windows 7
HollyK
 
Posts: 2
Joined: Wed Mar 06, 2019 5:21 am

Re: Exporting data from Calc to Base

Postby Zizi64 » Wed Mar 06, 2019 10:02 am

Without seeing the structure of the present content and the format of the spreadsheet and the desired result of the printing, I can guessing only, why not appropriate the spreadsheet file itself for you...
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8354
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Exporting data from Calc to Base

Postby MrProgrammer » Wed Mar 06, 2019 3:59 pm

Hi, and welcome to the forum.

HollyK wrote:What I am trying to do is print a directory (list) of the people listed on the speadsheet. The fields are separate as to first name, address, phone, etc.
Now I want to pick specific pieces out of the rows and display them in a very small type of report.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3843
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Exporting data from Calc to Base

Postby Villeroy » Wed Mar 06, 2019 4:25 pm

Download https://extensions.openoffice.org/en/pr ... rt-builder and open it with Openoffice or install it via Tools>Extensions...
Restart the office suite.
File>New>Database...
Type: Spreadsheet
Specify the spreadsheet document containing your list.
After saving the database document, you hava a database connection to the spreadsheet. Nothing has been exported, copied or converted. Your data still reside in the spreadsheet and editable through Calc rather than Base.
Now create a new report using the report wizard.
A report is similar to a Writer document filled with database data. It is embedded in its database document.
Create another one if you don't like the first.
Play with the report builder until you get close to the report you like.
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: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Exporting data from Calc to Base

Postby jrkrideau » Wed Mar 06, 2019 4:41 pm

HollyK wrote:I have a spreadsheet with information on alum. members. Is it possible to export this data to a database file that can printed out as a directory as opposed to a spread sheet?

Thanks!


I am not much of a data base user but in theory, you could export to .csv and import the .csv file to a database. Given very clean data in the Calc file, it is possible. In practice, you might drive yourself crazy trying to clean the data for import.

Villeroy's approach seems the way to go.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3698
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Exporting data from Calc to Base

Postby Villeroy » Wed Mar 06, 2019 5:07 pm

jrkrideau wrote:
HollyK wrote:I have a spreadsheet with information on alum. members. Is it possible to export this data to a database file that can printed out as a directory as opposed to a spread sheet?

Thanks!


I am not much of a data base user but in theory, you could export to .csv and import the .csv file to a database. Given very clean data in the Calc file, it is possible. In practice, you might drive yourself crazy trying to clean the data for import.

The csv import would work with any other database program if you can't connect a Base document to the database.
There is also a copy&paste approach where you connect a true database to a Base document, copy a clean cell range, select a table icon without opening the table and paste to the icon. A wizard pops up where you map incoming columns to the existing columns of the database table. A clean cell range has numbers, dates, times and text strictly separated from each other. That is what I would call "exporting data from Calc to Base" i.e. some real database that is connected to a Base document.

Villeroy's approach seems the way to go.

The database connection to a spreadsheet treats a spreadsheet list as if it were a database table. Any ambitious column types (mixed numbers and text) will be treated as text.

All this import/export/copy/paste is obsolete if you have a true database which is connectable to a Base document, MySQL for instance or just a directory of dBase files. Then you can easily create reports right from the database without any spreadsheet, csv files, copy/paste from one program to another.

 Edit: Oh, I forgot my macro which can execute SQL commands on a selected spreadsheet range: Base, Calc, Python: INSERT, UPDATE, DELETE from spreadsheet. This code provides a very simple method to export data from Calc to a database once you set up the right column headers and SQL statement. 
Last edited by Villeroy on Wed Mar 06, 2019 7:08 pm, edited 3 times 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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Exporting data from Calc to Base

Postby jrkrideau » Wed Mar 06, 2019 5:38 pm

All this import/export/copy/paste is obsolete if you have a true database which is connectable to a Base document, MySQL for instance or just a directory of dBase files. Then you can easily create reports right from the database without any spreadsheet, csv files, copy/paste from one program to another.


But this assumes that HollyK (the OP) inherited the data from someone who knew what a database is.

People use spreadsheets for the most inappropriate things. We have examples, here, of people with 100+ columns and 100K rows of data.

Heck, I have, a long time ago, seen C.V.s written in Excel and Lotus 123 and the Lotus was working under DOS.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3698
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Exporting data from Calc to Base

Postby Villeroy » Wed Mar 06, 2019 5:48 pm

May be someone administrates that database and can provide read-only access with a connection URL and a query string. On this forum I've seen people exporting data from their own database to csv, import the csv into a spreadsheet, connect a Base document to the spreadsheet in order to get a report or serial letter.
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: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Exporting data from Calc to Base

Postby jrkrideau » Wed Mar 06, 2019 8:29 pm

Villeroy wrote:n this forum I've seen people exporting data from their own database to csv, import the csv into a spreadsheet, connect a Base document to the spreadsheet in order to get a report or serial letter.


But, but, isn't that the correct way to do it? :crazy:

I just read, yesterday, about a govt stats group, I assume a long time ago, were using LotUS 123 and did not know that it could do calculations. They would enter the raw numbers, calculate things on their calculators and enter the results.

More ON topic, I was just reading about the sqlite manager extension for Chrome and Firefox that will read in a spreadsheet and output an sqlite database. If the OP really wants a db this might be worth investigating.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3698
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Exporting data from Calc to Base

Postby Villeroy » Thu Mar 07, 2019 12:35 am

jrkrideau wrote:But, but, isn't that the correct way to do it? :crazy:

Well, you could also download the forum topics as a bunch of csv files and upload your postings in plain text to some ftp service from where some deamon writes it into the database so others can see your posting in their downloaded csv files. Fortunately, we get reports and input forms through our web browsers.

Dumping database data into the layout of documents had been the whole point of database connectivity in OpenOffice even before the Base component started in v2.0. The concept of "the Base document" and the idealistic idea of office users creating new databases from scratch is a huge misunderstanding. Hardly anybody wants to do that. And those who want struggle with underdeveloped components.
However, some admin of some company can connect some existing database to our beloved office suite and when a user runs some report or serial letter, even the data that have been added a second ago by someone else will be included in the report without any extra effort. Once the database is connected via ODBC or JDBC, you have its data in the data source window, in linked cell ranges, in pivot tables, serial letters, label sheets. You may even write data back into the database through forms. If you do it right, you share read and write access on the same row sets with other database applications or multiple office instances. The server application takes care that only one user writes to some same row at a time while another user may edit/insert/delete another row. The users don‘t need to handle any files nor file formats.

Back on topic:
HollyK, You have data in a spreadsheet that are organized as a flat, simple list [*], so the spreadsheet serves as poor man's database. Connect a Base document to this pseudo-database and play with the report builder in order to get the list content into a nice layout with formatting.
This type of pseudo-database has a draw back. Before the report reflects modified spreadsheet data, you have to edit, save and close the spreadsheet document in Calc, restart the whole office suite, open the database document and its contained report. However, you do not have to edit anything else but your sheet data.

[*] spreadsheets do not enforce any data structure. For instance, there may be contingency tables (aka cross tables) in a spreadsheet or chaotic lumps of data cluttered across the sheet. Not all contents of a spreadsheet document are suitable to be used as a database table.
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: 27222
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests