Ask Your Question
1

How to remove automatic field separators in text import in Calc

asked 2019-10-03 11:20:29 +0200

pavion gravatar image

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 flag offensive close merge delete

Comments

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

Opaque gravatar imageOpaque ( 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.

pavion gravatar imagepavion ( 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)

Opaque gravatar imageOpaque ( 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.

pavion gravatar imagepavion ( 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.

Opaque gravatar imageOpaque ( 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)

pavion gravatar imagepavion ( 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.

Opaque gravatar imageOpaque ( 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...

LeroyG gravatar imageLeroyG ( 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.

Frants gravatar imageFrants ( 2020-10-15 12:29:09 +0200 )edit
LeroyG gravatar imageLeroyG ( 2020-10-15 13:56:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-10-15 00:21:23 +0200

LeroyG gravatar image

updated 2020-10-15 11:24:33 +0200

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.


Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

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

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

edit flag offensive delete link more

Comments

Excellent. This is exactly what I want and it is helping me save a lot of time. Thanks

pavion gravatar imagepavion ( 2020-10-15 14:03:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-03 11:20:29 +0200

Seen: 610 times

Last updated: Oct 15 '20