# How to remove automatic field separators in text import in Calc

In Libreoffice 6.2.7 open Calc and from File | Open menu select some text file with long rows. Text Import dialog appears. In "Separator Options" select "Fixed width" and now in bottom of dialog there is ruler where red dots are displayed. Every red dot is indicator where field separator will appear. When opening long rows text file there are e.g. hundred dots. Because this red dots are set to who know what logic in my case are totally wrong. Now I have to delete dot (by double click on it) and set the right dots. Now deleting dots occupies 90% of my work defining field separators. Is there a way I can with e.g. one click remove all automatically set red dots?

edit retag close merge delete

Does your question actually mean: "I want to insert each input text line, into a single cell of a row"?

( 2019-10-03 11:50:50 +0200 )edit

No. I want to import text file with few hundred of rows. But each row is pretty long. Now Libreoffice Calc automatically adds red dots (field separators). I am asking if I can remove automatic field separators with one command (this would significantly preserve my time) and then next task as normal I manually set the field separators.

( 2019-10-03 12:25:35 +0200 )edit

Sorry this wasn't an answer to my question and it is still unclear why you use "Fixed width" option instead of "Separated by" and deselecting all separators (btw: if starting with this option and reselecting "Fixed width" will remove all your red dots)

( 2019-10-03 12:30:12 +0200 )edit

I can't use "Separated by", because there is no character separator in my input text data. I get this data from our business partner. I use "Fixed width", because data are stored like that, e.g. from first to 10 character is first field, from 11 to 25 is second field etc. But LibreOffice Calc by default "thinks" it is smart and adds separators (red dots) by itself, I don't even understand the logic behind it. This automatically adding separators is completely useless, because I have to manually add positions where field separators should appear.

( 2019-10-03 15:02:51 +0200 )edit

Are you telling me, that in fact you don't have a single line feed in your input file? Have you even tried to select "Separated by" and switched back or are you dealing with a nonsense task? Please provide a clear picture of your input and how you want the result to look like.

( 2019-10-03 15:16:04 +0200 )edit

I have ordinary file with few hundreds of rows. Each "column" in file is separated by position e.g. first column is from position 1 to 10, second column from position 11 to 25 etc. At those positions I want to have separate columns in Calc. Now when I choose File | Open, Text Import window appears and at the ruler is automatically filled with a lots of red dots (field separators), it looks like totally random. Because I have pretty long rows there are about 100 red dots, that I have to manually remove before I can set positions manually for each of the column I have in my file.

Yes I tried selecting "Separated by" and switched back to "Fixed width" and nothing changes all of the red points (field separators) stay at the same position. You can test this out too, just open any text file you want ...(more)

( 2019-10-04 06:49:09 +0200 )edit

you can test this out too, just open any text file you want and you will notice switching doesn't do anythig.

I test before I post, that's why I made the suggestion.

( 2019-10-04 15:45:05 +0200 )edit

Testing on it will show that Fixed width remembers the separators (aka: column widths, or split points) previously selected. So, they are not set automatically. Clicking on each red dot will delete it (not so easy if there are many).

Text Import dialog will appear on Calc when:

• choosing menu File - Open…, on a plain text file;
• pasting text copied from a plain text file.

If plain text are already in cells, choosing menu Data - Text to Columns… will open the Text to Columns dialog (the only difference with the other Text Import dialog is that it has a Skip empty cells check box).

Related reports in Bugzilla:

https://bugs.documentfoundation.org/s...

https://bugs.documentfoundation.org/s...

( 2020-08-20 21:10:12 +0200 )edit

Tried testing. I am creating a text document New1.txt. I fill it with text:

123456789abcdefghij########

123456789abcdefghij########

123456789abcdefghij########

I save, close. I open Calc, File-> Open, select the New1.txt document.

In the "Text import" window: I select only the "Fixed width" option, set two red points at positions 10 and 20, OK.

RESULT: the document is imported as three columns of data. I make SaveAs New2.ods. It's all. Works properly.

But there is one wish for the developers - this is the ability to save the import specification in the template.

( 2020-10-15 12:29:09 +0200 )edit
( 2020-10-15 13:56:30 +0200 )edit

Sorry, I don't quite understand you. What should I do there?

( 2020-10-15 14:33:48 +0200 )edit
the ability to save the import specification in the template.


If do you want an enhancement to Calc, you can post an enhancement request there.

But you could see that the last used splits (field separators) remain in the memory of Calc.

( 2020-10-15 14:40:45 +0200 )edit
1

Yes, I noticed that the positions are remembered. It's good when you work at one PC (home). But what to do when you also need to use someone else's PC (in the office, for example)? What if text files each require their own markup?

( 2020-10-15 15:30:31 +0200 )edit

Sort by » oldest newest most voted

Not a one click solution, but a two key one (I feel a little bit like Archimedes):

Shift+Delete: delete all splits (field separators).

LibreOffice Help on Controlling the Text Import dialog (CSV file import) is a must read.

Tested with LibreOffice 6.3.6.2 (x86); OS: Windows 6.1.

Check the mark () to the left of the answer that solves your question.

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).

more