![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I need to write a program that will read a CSV file and extract some, but not
all, of the information to populate a database. I don't know where to
start. What language do I need to use in order to interface with the
LibreOffice database program? Is there an SDK or a library of subroutines
that I can use to interact with the database?
Can someone please point me in the right direction to get started with this
project?
Thank you,
Mark Mickelsen
Since Base is not a database program, the first thing to know would be the
actual database to be used. An embedded HSQLDB is only one option, good
enough for simple demos and drafts but you don't really want to develop
anything serious for that.
Every "common database" (MySQL, Postgre,... and HSQLDB too) provides access
to external text tables by means of database structures pulling their
content from plain text files.
You can create a view which selects the wanted records from the text table
in the right order of columns.
In Base you can drag or copy the view icon over the target table's icon and
hit "Create".
A set of example files dealing with csv, Base and Calc:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=23727
[Example] Loading CSV into preformatted spreadsheets
Files #1 and #2 resemble a text file linked to a (embedded) HSQLDB, editable
through an input form on a (embedded) Writer document.
Using some "common database", any "common scripting language" can update the
database with very few lines of code. No 350MB office suite required for
that.
I'm very surprised to learn that Base is not a database program. I
guess it is just some sort of front end to "real" database programs,
is that right?
I guess this means that I'll have to get a real database program that
I can write programs for in order to accomplish this project. Any
suggestions as to what would be inexpensive and easy to work with?
Thanks,
Mark Mickelsen
I do a bit of this, extracting data from accounting/stock reports and pumping it to a web page. I use 2 methods (again, not sure it matches your need), either perl scripts which are great for text when you want it automated or apache+php+mysql which is great when you want general user interaction.
steve
Hi 
Base is a front-end and yes you can easily choose which back-end you use. There
are a lot of powerful back-ends out there. Base does have a default one but
apparently it is better to choose something else. It is not in great shape
right now but hopefully more people will help those that recently started rather
than everyone building up their own front-ends that they then have problems
maintaining and supporting adequately. Just my 2 cents/euros/pence.
Regards from
Tom
I am surprised that Base is not a database program. I thought it was meant
to be something like MS Access? Why is it included then in Libre Office?
I am also trying to create a database in Base (as I did before in Access),
however I also run into problems I never had when using Access...
Raymond
You are right. It is a mere frontend. Mainly it is a bridge between tabular
data and office document. Tabular data include address books from various
mail clients, csv, dBase, spreadsheets and all kinds of databases via ODBC,
JDBC or some built-in driver (SDBC). When the mail merge wizard creates a
serial letter for you it creates a Base document even when you specify a
spreadsheet list as address source.
In OOo 2.0 they invented the infamous "integrated database document" which
includes a http://hsqldb.org of version 1.8 zipped into the Base document,
installed every time you "open the database document" and repackaged every
time you "close the database document". No need to say that this database is
a pain in the ass, particularly when it trashes your precious data.
Base connected with some mature external database (and hsqldb is not a bad
choice) is a simple database frontend if you are aware that "simple" may be
the opposite of "easy". The main data flow goes from databases to office
documents.
You can attach simplistic input forms to office documents in order to send
data the other way from the office to the database.
Normally, I do not need any macros at all.
Your requirement for updating a databse from csv has not so much to do with
a database frontend. Of course you can do this programmatically with some
database frontend. Writing such frontend animal will be a horror trip for
sure. MS Access is much better prepared for such things and Steve Edmonds
already confirmed the backend story from a professional point of view. Some
database, some data, some scripting language, no office monster. When work
is done load the results into pretty reports, spreadsheet models, even
presentation tables can receive database data.
In my previous answer I outlined a non-programmatic way how to do such
things in the Base user interface without a single line of code but with a
little bit of setup and a few more clicks for each import.
To be honest, I have written a database program for a mainframe. I really like the backend/frontend type. You can choose what database you want to use that is best for your needs. MySQL seems to be the most popular for the non-MS ones. Also is you do any Browser/server/web based access to these databases, you should choose MySQL.
The real part about databases is the way you access the data in the system. Entry forms and Reports are the main stay for database programmers. So it would be better to use the well-established backend database systems and dedicate your office suite programming to access packages for those popular systems.
To be honest, would you want to use a database system that only one package can access, or one that you can use for your office package related documents and web-based access methods at the same time? Use LO to do the entry and reports, and use HTML/browser based viewing for those will low computer skills. It seems most offices now have HTML based access to databases or other customer information. I think if you have network based database access, that is the way to go. I would use LO to do the initial work for setting up the database and the initial data entry work, then use HTML/Browser based access for my users. Some fields/records would require the admin/LO forms to do any edits to, as a security feature though.
SO it would make sense for LibreOffice to use the popular database formats, like MySQL, and concentrate on the access forms/reports for those bases. That is what I did for my Mainframe programming work, let other define the database and I create the data entry and reports.
Hi
To be honest, I have written a database program for a mainframe. I
really like the backend/frontend type. You can choose what database you
want to use that is best for your needs. MySQL seems to be the most
popular for the non-MS ones. Also is you do any Browser/server/web
based access to these databases, you should choose MySQL.The real part about databases is the way you access the data in the
system. Entry forms and Reports are the main stay for database
programmers. So it would be better to use the well-established backend
database systems and dedicate your office suite programming to access
packages for those popular systems.To be honest, would you want to use a database system that only one
package can access, or one that you can use for your office package
related documents and web-based access methods at the same time? Use LO
to do the entry and reports, and use HTML/browser based viewing for
those will low computer skills. It seems most offices now have HTML
based access to databases or other customer information. I think if you
have network based database access, that is the way to go. I would use
LO to do the initial work for setting up the database and the initial
data entry work, then use HTML/Browser based access for my users. Some
fields/records would require the admin/LO forms to do any edits to, as a
security feature though.SO it would make sense for LibreOffice to use the popular database
formats, like MySQL, and concentrate on the access forms/reports for
those bases. That is what I did for my Mainframe programming work, let
other define the database and I create the data entry and reports.
Ubuntu provides connections for generic ODBC and specific connectors for
MySQL, PostgreSQL and other databases to LO and other front-ends. I
assume this is done in other Linux distros. I like using Base with
MySQL/MariaDB because its connection is straightforward.
Am 25.08.2011 20:27, L O wrote:
I am surprised that Base is not a database program. I thought it was meant
to be something like MS Access? Why is it included then in Libre Office?I am also trying to create a database in Base (as I did before in Access),
however I also run into problems I never had when using Access...Raymond
Access is named Access because it lets you access databases. MS Access is one of the best frontend tools for databases. In this respect you can use it very much in the same way as Base. Unfortunately, 90% of its users try to use the built-in JET database engine instead of a professional and portable one.
The main difference between Access and Base amounts to a decade of development and several millions of Dollars.
Raymond,
I am surprised that Base is not a database program. I thought it was meant
to be something like MS Access? Why is it included then in Libre Office?I am also trying to create a database in Base (as I did before in Access),
however I also run into problems I never had when using Access...Raymond
I recommend MySQL/MariaDB for a back-end. You connect to the back-end
not the default back-end (HSQL). MySQL/MariaDB are mature databases.
There are others which are also mature and will make an excellent
back-end, I have not tried some of them yet.
The advantage of this method is that you can use the Base front-end to
set up, manage the database with users having access to the same
database.
Andreas
You are right. It is a mere frontend. Mainly it is a bridge between tabular
data and office document. Tabular data include address books from various
mail clients, csv, dBase, spreadsheets and all kinds of databases via ODBC,
JDBC or some built-in driver (SDBC). When the mail merge wizard creates a
serial letter for you it creates a Base document even when you specify a
spreadsheet list as address source.
In OOo 2.0 they invented the infamous "integrated database document" which
includes a http://hsqldb.org of version 1.8 zipped into the Base document,
installed every time you "open the database document" and repackaged every
time you "close the database document". No need to say that this database is
a pain in the ass, particularly when it trashes your precious data.
Base connected with some mature external database (and hsqldb is not a bad
choice) is a simple database frontend if you are aware that "simple" may be
the opposite of "easy". The main data flow goes from databases to office
documents.
+1 on simple and easy
Hi
MariaDb forked off from MySql and is a drop-in replacement for it. There are a
lot of parallels with LibreOffice including an active and aggressively fast
development schedule.
http://mariadb.org/
http://en.wikipedia.org/wiki/MariaDB
Regards from
Tom
The one packaged database I really liked was Lotus Approach. It also
used a fairly standard database file (Dbase I think). I now prefer the
separate database and front ends, but that is easy for me. Where these
split systems fall down is when you have a database application that you
want to develop and send to someone you know is incapable of installing
the backend and whose capabilities extend only to double clicking an icon.
May be IBM will donate the Approach code to Apache and save everyone a
lot of work.
steve
Mark,
Don't believe everything you read. Base is just like MS Access. It has a
built-in database engine (like Access), you can connect to and
administer external databases, create forms, report and glue these
together using one of LO's script languages. All this is pretty much
like Access.
It is true the built-in database engine is almost unusable right now due
to it's slowness (apparently caused by some java issues).
Also some databases backends do not allow all things to be administered
(like modifying an existing table in sqlite, and some driver issues in
Base with Mysql and Postgresql). So it might be good to select an
external database that will run on the platform you need (mysql will run
on windows as well as linux) and has a good tool for administering (PG
Admin is great if you select postgresql, like we did without regrets).
Having selected the backend, and set up the tables you can then go about
creating your forms in Base. You might then want to use the Basic script
to handle certain events in your forms.
Ferry