1

I want to import some data from a csv/text file to Calc.

In the first column I'd like to have the 'Dummy text # i', and in the second column the numerical value.

In the text file, 'Dummy text # i' and the numerical value are separated by several blank spaces. Thus, I would probably achieve this by using ' ' (two blank spaces) as delimiter, then ticking the 'merge delimiters option.

However, I don't now how to do this. I tried using 'other' as custom separator but it seems to interpret any number of blank spaces as just one.

This is the initial data that I copy and paste to the spreadsheet.

Initial text data

And this is what I can get. Note that unmarking the 'space' option, only accomplishes accounting for the right-trailing spaces.

As you can see, none of the following serve. The 'Dummy text # i' is spread in various columns, as Libreoffice seems to be taking one single space as separator.

enter image description here

The second one (merge delimiters)comes closer, but still doesn't get the 'Dummy text # i' in the first column.

enter image description here

As @Zina tested it in another version, I checked my Libreoffice version (4.3.3.2), but I'm sceptical about it being the root cause, as it seems a rather glaring flaw.

What am I doing wrong? Might it be a version flaw? Thanks in advance.

  • I suspect Calc can only delimit using one character, similar to tr. Why not just use a format that it can handle like CSV, it means comma separated values? Either that, or do some search & replace with a better separator, maybe with sed or awk or find & replace in a text editor. Having spaces in your values can mess up using spaces as a delimiter. Or just import it messed up, then cut & paste & move around to fix it in calc afterwards – Xen2050 Feb 13 '16 at 8:48
  • The workaround I'm using is replacing ' ' (two blank spaces) with a string (e.g. '???'), then use that as custom separator, tick 'merge separators', and it works. However I was hoping for a more direct way. I'm not sure I understand the 1 character limit you mention. – Calculus Knight Feb 13 '16 at 8:59
  • If your first field has quotes around it (not sure if it would work with single quotes or backticks, or only double quotes) then try the "Quoted field as text" checkbox under other options. And why not just replace your current two spaces with a single comma, instead of ??? ? – Xen2050 Feb 13 '16 at 11:18
  • The first field generally doesn't have quotes of any kind. My choice of '???' was to avoid trouble when importing it. If I were to use ',' as a separator, and my data contains ',', everything would go awry. The input data is quite general, but doesn't contain the string '???', thus my choice. – Calculus Knight Feb 13 '16 at 11:23
  • Ok. There are other single characters that might not be in your input data too, like maybe one of !@#$%^)&*(_-[]{}\|>.<? – Xen2050 Feb 13 '16 at 11:29
1

I just tried on a CentOS 7 with LibreCalc 4.3.7.2 and it works. You just have to select "Space" and "Merge delimiters". I created a text file with random data (the rows even did not have the same number of columns) some data divided with 1 space some with 2 or even more. I was able to see in the preview pane how the data was changing when enabling/disabling "Merge delimiters". Are you sure there are spaces? And not some special characters not shown?

Okay. Now I got what you explained. I think you will be able to get the wanted behaviour only if you put the "Dummy text#??" under quotation marks and deselect all except "Other" where you have to put your double spaces in and set the text delimiter to the used quotation marks. Or you will have to replace all double spaces with eg a semi-colon as others already suggested.

| improve this answer | |
1

In a (scriptable) two-step process, you could use sed to convert some spaces to a tab, and then import. A sample script:

#!/bin/bash
tmpcsv=$(mktemp)
inputcsv=$1
mv $inputcsv $tmpcsv
sed 's/  /\t/g' $tmpcsv > $inputcsv
scalc $inputcsv
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.