Importing data and queries

[janiehee]

Hello

I was wondering if someone could give me some advice on whether Base is going to be a suitable package for me to use. I recently started a business selling homemade fudge and chocolate truffles online. My website just has a simple Paypal basket and I get all my order information from Paypal. I can download the order details as a csv file. However, this means that I have multiple rows for each order - there will be one row about the order and customer, and then additional rows for each product type ordered.

I used to have a similar issue with a previous ordering system I used and I built an Access database to deal with it. I essentially had a simple macro that imported the data into a table, I then used append queries to split the data up into two individual tables - one with the general order data and one with the specific products ordered. However, I'm not a programmer, I only used built in queries and recorded macros.

I'm wondering if I'm going to be able to do something similar in OpenOffice. Since there doesn't even seem to be an import function I'm a bit confused. This data will need to be imported regularly so I cant' have anything too laborious, and I'm happy to have a go writing SQL if that's what's needed, but concerned it may be too tricky for me.

Any suggestions welcome!

[rudolfo]

I might be a bit late and it is even not a complete answer, but if your csv files look like the following:

Code: Select all

2014-02-23;30034;Glasgow;Peter;23 Main Road;postalcode;Dallas;Texas;total-price;other;data
prod_265;dark brown;4 pieces;"mail delivery";other
prod_010;white chocolate;7 pieces;"picked up by taxi";other
2014-02-25;30038;Washington;George;Park Avenue 1;postal-code;Washington D.C.;;$28;other;data
prod_265;dark brown;1 piece;"mail delivery";other
prod_220;White Santa Claus;2 pieces;"mail delivery";other

You won't be able to directly import it, because CSV files have by definition the same number of fields. This file has different field depending on the context: customer level or order details.
At this point you have no other chance then to process this csv file with some pre-definitions:

• You read the file until you reach the first customer line
• Generate a unique label for this customer and store this label (a serial number or something similar) in memory
• Go through the next lines as long as it is not another customer line. These lines are all orders. Mark them (store them together) with the last customer unique label from above.
• You find the next customer line (this means that the processing for the previous customer an his orders is completed. Continue with the same steps for this new customer.

To accomplish this process it is not enough that a database engine is able to import csv files. This kind of process needs more intelligence, typically in form of a procedural language or a macro. Some databases come with their internal procedural language (PL/SQL of Oracle, PL/pgSQL by PostgreSQL, Transact-SQL by Sybase and Microsoft, ...) but in most cases they are not able to access plain csv files on the file system (database procedural languages are meant to work with database table content).
Perl, Python, awk and PHP are the classical scripting languages that are meant for text (file) processing.You might be able to do this in OpenOffice Basic with its file I/O functions. But Basic is an old language, it doesn't know about Unicode. If you have names like "Müller" or "Linné" trouble is waiting to happen. So better use one of the P languages: Python, Perl or PHP. Python has the advantage that it is also one of the mature scripting languages for OpenOffice.

The other choice that you have to make is the choice what database backend engine you would like to use. The above mentioned scripting languages have all generic interfaces to several kinds of database backends. (DBI for Perl, PDO for PHP and Python and its Database API 2.0. These interfaces are good for connecting to standalone database backends. Unfortunately this is not the case for the default backend of Base, the embedded HSQLDB engine. This database backend engine runs inside the OpenOffice database document and can only be reached through the OpenOffice UNO API. This would limit the choice of scripting languages to Python, because Python has no problems to speak to OpenOffice's UNO API.

If you are wondering why I am talking about all this stuff and why not just using Base as a database ...
This is the typical misconception: Base is not a Database, it is the counter part of MS Access in OpenOffice, that's right. But this is rather a marketing statement and not a real world statement. The fact is that Base is only a frontend to several (external/third party) database backends and it only happens to have an embedded backend that pretends that Base is a database. If you regulary read this forum you quickly
learn to know that the embedded database tends to show severe errors. Do yourself a favour and do not use the embedded database for real business.

So these are just a few thoughts for the beginning. You can always come back if you have further questions.