Base: Importing data from a csv file

[Kametec]

Hello,
I have decided to use LibreOffice Base for my personal project. I have a couple of csv files holding data for a database I’ve created and I would like to import them. However, I’ve run into two issues:

1. I haven’t found a way to directly import a text file into a Base database. I found a workaround where I import data into Calc, select them, copy them to clipboard and then paste to Base, which opens a Copy table wizard, which lets me import copied data. It may work fine for some cases, but importing data to Calc is a problem for me. One of my files is fairly large and Calc can only hold about 1 milion rows in a sheet. One possible solution would be splitting the file into 1 milion row chunks and import them separately, but handling about 200 files sounds daunting and I am reluctant to get started. There surely is a better way, or is it? I mean, populating a database by data from a file sounds like a basic task, so I assume there is a easy way to do it. Am I right?

So, my first question is: How can I import data from a large csv file to a Base table?

2. When importing data to other and smaller tables I’ve encountered another issue I’d like to ask about. I don’t have an ID record for my data, I intended to use an auto-numbered column for primary key and have Base handle the numbering for me. When importing data from Calc by copying as mentioned above, I noticed that columns in the Copy table wizard don’t align as the ID column was missing in the Calc table. I then added an empty column to the Calc table, so that it makes the other column match well and removed its tick in the wizard so it doesn’t get imported. Idea was to let Base fill the ID column with unique numbers for me. But I get an error that I’m trying to paste null values into non-nullable column and nothing gets imported.

My second question is: How can I import data from a file to a Base table which has an auto-number as its primary key?

Thanks for replies, any feedback or assistance is much appreciated!

[Villeroy]

There is no such thing as a “Base database”. Base is not a database. It is a tool to work with databases. In most cases where users refer to a “Base database”, they mean an embedded HSQL database.

You can link csv files to HSQL tables.

If your Base document is connected to some popular database server such as MySQL or PostgreSQL. these databases also have the ability to link tables to csv files.

Embedded Firebird can not link to csv filles. But you can copy data into existing tables.

Finally, a Base document can treat a directory of similar csv files as a pseudo-database. This database will be read-only with limited capabilities but good enough for mail merge or easy data import into Calc.

Apache OpenOffice Community Forum - [Example] Loading CSV into preformatted spreadsheets - (View topic) includes an example database “CSV_Editor.odb” (embedded HSQL linking to csv) and a “CSV_Database” (direct link).
Embedded HSQL is documented here: Chapter 9. SQL Syntax
The ability to link text tables is documented here: Chapter 6. Text Tables

For any kind of copy and paste operation, you need a well prepared database table with the right column types as target and some source from where you copy the data. The source to copy from may be a table icon from the same Base document (a linked text table or view on such table) or from another Base document (e.g. a direct, read-only csv connection).

All the gory details of csv import depend on the csv itself. There is much more than choosing the right column delimiter.

[RobertG]

How to: Import Tables into Base

I downloaded a database and corresponding data dictionary. The Data Dictionary is in a separate csv file. The database downloaded as a zipped file and when unzipped, each table is a separate csv spreadsheet. Is there a way I can import this ‘easily’ into Base? Or do I need to set up each table separately and manually?

If you haven’t any primary key you have to define the fields for any table before you copy data. The import wizard is a little bit buggy since LO 7.2 (bug 147174) so you couldn’t choose “Create new field as primary key”.

[Kametec]

Thanks for your replies. I was able to bypass Calc by creating new database and linking a csv file to it. Then I can copy the data the same way I did from Calc.

My other issue is still the same:

This is a screenshot of the import wizard. Sorry for non English language but I didn’t have English available in interface options when I checked before taking it. My point should be clear nontheless - there are two less columns in the source table on the left side, because I don’t want to prepare data for the extra columns on the right side. One is an automatic number and the other one has a default value. If I confirm this, I get the “inserting null into non-nullable column” error. How can I append data to a table and have Base fill in automatic numbers in primary key column for me?

[RobertG]

@Kametec

One is an automatic number and the other one has a default value.

Automatic number must be defined at auto-value. You will find it in “Field Properties” at the bottom of the editor for tables.
But the error could be the second field. You have defined it as “default value”, but the default of Base GUI isn’t the default of the database. So no value is there for the database and if you have set this field to Entry required → 'Yes' it is a non-nullable column without any value.

ALTER TABLE "Tablename" ALTER COLUMN "Active" 
SET DEFAULT <Defaultvalue>;

A command like this will add a default value for the database to the field “Active”. Set “Tablename” to the name you are using. Set to the value. Could be like text: 'never'. Could be boolean: False …
Start this commend in Tools → SQL.

[Kametec]

You are right, the issue was with the other column.

@"RobertG

You have defined it as “default value”, but the default of Base GUI isn’t the default of the database.

This is the problem. I didn’t know about this and assumed that setting a default value when creating the table in GUI sets the default for the database. It doesn’t seem very intuitive, to say the least. I suspected the primary key column to be the issue because it seemed more likely.

I ran the Alter table command and it solved my issue, I was able to sucessfully import data on next try. Thanks a lot!