Double spaces stripped on import to Base

English
#1

Hi,

I’m importing data into Base via the following steps:

  1. Export CSV file from another application
  2. Open CSV file in Calc
  3. Select contents in Calc, and Copy
  4. Paste into a table in Base

It works fine, but I’ve just noticed that strings which originally had double spaces in them end up having only a single space when stored in Base. (This is more than cosmetic, as these strings represent file names - so file lookups subsequently fail.) The double spaces are still present when the data is viewed in Calc, so appear to be being lost on the import to Base.

Is this a known issue? Are there any settings that might be causing this behaviour?

I’m using LibreOffice 7.1.8.1 on Windows 10.

#2

Hello,

With Linux Ubuntu 20.04, have tried multiple versions including v7.3.0.3 and they all exhibit the problem you have. In fact, newer versions (7.2.5.x & 7.3.0.3 tested) have even further problems as you cannot create new tables in this fashion as the NEXT button is greyed out.
.
Should be reported as a bug here → Bugzilla
.
and after doing so, kindly post the reported link here.

#3

Many thanks for checking this out. I’ll go ahead and report it as a bug.

In the meantime, a workaround shouldn’t be too hard. I’ll get the application that generates the data to insert a short sequence of characters wherever there would be a double space (I’m guessing I could do that in Calc too), then after loading into Base use SQL UPDATE/REPLACE to change occurrences of that sequence back into two spaces.

#4

Bug reported:
https://bugs.documentfoundation.org/show_bug.cgi?id=147176

#5

You can import/link csv files directly in Base without using Calc.
Details depend on the exact type of csv and the type of database connected to your Base document (see status bar).

#6

If using a recent version of LO, creating a table from a csv file takes some manipulation now if the end result is to be in an actual database. This is because there are further issues the the dialog for copy and paste.
.
It can be done by defining the table first through Base for the database but you are still operating somewhat blind as Next there is also greyed out.
.
Copy/paste of data in tables using Base is currently very buggy. It appears that it doesn’t matter what database is being used.

#7

Thanks, Villeroy and Ratslinger.

Everything I’d seen in the documentation was about linking to CSVs rather than importing them, but I’ve now gone through the “New database” process to create one whose tables were based on my CSV files. I then opened one of these and manually copied then pasted the data into the relevant table in my main database. It seems to work ok and at a quick check appears to have preserved the double spaces.

What I really wanted to use was something like SQL Server’s BULK INSERT statement, but that doesn’t appear to be supported. (I’m currently using HSQLDB Embedded, but will probably move to a MySQL back-end in the medium term.)

#8

Using MySQL Workbench, there are tools there to import csv files to a new or existing table. Have also done this with PostgreSQL using pgAdmin4.

#9

Base is not a database. It is a tool to work with databases. If you don’t know which database you are using right now, it might be a HSQLDB or Thunderbird DB embedded in the document.
HSQL is documented here: Chapter 9. SQL Syntax
Handling text files in HSQL is documented here: Chapter 6. Text Tables
See also:
[Tutorial] Using csv/text files as editable data source. (View topic) • Apache OpenOffice Community Forum
[Example] Loading CSV into preformatted spreadsheets (View topic) • Apache OpenOffice Community Forum (example 5)

#10

As Firebird is back to experimental since 6.4.4 HSQLDB is much more likely.

#11

What am I missing? OP already noted currently using HSQLDB embedded with a possible move to MySQL.

#12

Nothing missed, and if Mike Kaganskis idea on the reason is right (see bugreport) it will affect every database.

#13

Paste Special does work for the multiple spaces. However I do not see reason this is going to affect every database unless you specifically are dealing with copy/paste. Then still not in total agreement. Can do other copy/paste in similar manner without Paste Special.

#14

Every database, as it is related to using html-mode, when using the clipboard, so this is a “decision” in base, not in the connector to a database or the database. So I expect to find this also when I copy to sqlite or dbase. (Will check over the next week.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.