Frequently asked questions - Base
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
There is more than one way to import data from a .csv file to a table in Base, and they are not all documented in the built-in help. Sensibly the ones left out of that documentation are the ones presenting the steeper learning curves.
The easy strategies involve using a helper application and then using copy/paste from the helper to the destination database. The helper application could be Calc (method 1 below), Base (method 2 below), or some other program from outside LibreOffice (not yet documented here yet, but appropriate additions to this page would be welcome!).
In overview the methods are
- Use Calc as a helper application, and copy/paste the data into the destination database
- Use Base twice in separate windows (as the helper application and to open the destination database respectively). Use copy/paste to transfer the data from a whole table to the tables pane of the destination.
- Use Base twice, as above, but write suitable LibreOffice Macro code in the destination, and trigger it from a form whenever you want to transfer the data. (More work for the programmer; easier for the end-user!)
- Use Base once only and open text files from within the destination database. Contrary to the LibreOffice Help this is possible but involves using a non-standard SQL command, and is the only method which allows the database engine to actually change the data in the .csv files directly.
Each of the above has different merits and disadvantages. They are listed in order of easiest first for a typical newcomer to LibreOffice.
In more detail
- Calc as a helper. It is important that the first row contains names for each column and that the data starts in row 2. It is equally important to look at the options Calc gives you whenever you open a .csv file. Are the separators appropriate? The process usually works better if you have only one kind of separator, so only leave the one your file actually uses selected (If you get this wrong then it will work most of the time but give you occasional glitches that can be hard to understand. In addition, if there are dates in the input then two more things matter: select "Detect Special Numbers" (because that is how Calc stores dates) and above each column containing dates click on "Standard" at the top of the column and change it to Date. Then in the drop down menu select either D/M/Y or M/D/Y depending which best suits. When you paste into the tables pane in Base my suggestion s that you paste into a table without a Primary Key unless you are sure one of your fields will work. You will get the option to create a new table empty (Definition only), a new table with the pasted data (Data and Definition) or to append the data to an existing table.
- Base as helper for copy/paste. Again it is important that your file or files have column names in the first row. This method allows you to create a read-only database where the tables are actually the csv file/s. If there is more than one they must all be in the same folder, so create one especially for this role. You will need to tell Base where that folder is, NOT the actual file/s itself/themselves. New files can even be added later on and will magically appear in the helper database every time it is accessed. There is less scope to customise the .csv formats here -- basically if the Locales you have set up in your operating system are a close enough match to the ones in the software that produced the file then it should work. If not, and in particular if you find dates are muddled up between US and European patterns (7/4 being seventh of April for me but fourth of July in the US) or if some dates appear as NULL (or some rows are missing completely) then you can experiment with settings in the helper database: go to Tools in the menu bar then Options -> Languages -> Language Settings. If you can't make that work try using Calc as the import helper instead.
- Base as helper but Macro to do the transfer. This starts off exactly as for option 2, and the same comments apply about customising the .csv formats. However the transfer is not done by copy/paste. Write a Macro that is triggered by a suitable button on a form in the destination database, and whenever the user clicks that the data is transferred. This is much more user-friendly as whatever process you used to obtain the .csv file can be told to write it directly into the relevant folder, and the user only has to click something rather than copy/paste. Equally, of course, this is more programmer-challenging and the exact details are beyond the scope of this page.
- Using the destination database to read the input csv files directly. This is by far the hardest method to code, and the easiest for the end user once it is working. It requires use of a non-standard extension to HSQLDB in the Tools->SQL window; the command starts CREATE TEXT TABLE ... and again the exact details are beyond the scope of this page, but will have to be enetered separately for each file that you wish to connect to. It has worked for a number of years with the embedded HSQLDB engine. It should work with connections to an external HSQLDB engine; but for other databases the syntax and your luck may vary. Notably (and despite what the LibreOffice Help says) this method once you get it working will allow the database to directly write to the text files.