Calc Major Bug

Andrew_Foss · June 19, 2011, 7:35pm

I need to open a tab delimited text file in Calc. I have done this many times
in Excel where you have to Open the File from inside Excel. However, trying to
open it with Calc launches Writer (ugh!!!). It seems the programmers have gone
to great lengths to stop one doing what you wants and needs to do. Obviously,
if one is inside Calc, any file one tries to open should open inside Calc.
That's a no brainer.

How can I work around this problem? I tried tyo merge in the file but that also
failed. I tried changing the file extension but LO ignored that. I tried
caopy/paste into Calc but Calc failed to put each tab delimited field into a
separate cell so that failed. Hopeless!

Help!!!!

planas · June 19, 2011, 7:47pm

Andrew,

I need to open a tab delimited text file in Calc. I have done this many times
in Excel where you have to Open the File from inside Excel. However, trying to
open it with Calc launches Writer (ugh!!!). It seems the programmers have gone
to great lengths to stop one doing what you wants and needs to do. Obviously,
if one is inside Calc, any file one tries to open should open inside Calc.
That's a no brainer.

How can I work around this problem? I tried tyo merge in the file but that also
failed. I tried changing the file extension but LO ignored that. I tried
caopy/paste into Calc but Calc failed to put each tab delimited field into a
separate cell so that failed. Hopeless!

Help!!!!

The default behavior can be irritating, it sometimes gets in the way.

One option is to right click on the document, select open with and the
select Calc. It should show an import wizard asking you what the
delimiter is. Make the appropriate choices and click OK. It may take a
couple of minutes for it open but it should be in Calc.

Hope this helps.

Regina · June 19, 2011, 7:59pm

Hi Andrew,

Andrew Foss schrieb:

I need to open a tab delimited text file in Calc. I have done this many times
in Excel where you have to Open the File from inside Excel. However, trying to
open it with Calc launches Writer (ugh!!!). It seems the programmers have gone
to great lengths to stop one doing what you wants and needs to do. Obviously,
if one is inside Calc, any file one tries to open should open inside Calc.
That's a no brainer.

That is not so obvious. Word and Excel are different programs, Word can only handle text documents and Excel can only handle spreadsheets. But LibreOffice is not a collection of 6 programs (Writer, Calc, Draw, Impress, Math, Base), but is is one program, that offers different UIs for the different tasks.
Therefore a file, that looks like a text file will be opened in Writer as long as you don't determine explicitly what part to use. And a tab delimited text file contains nothing, which indicates that it is not a text file.

How can I work around this problem? I tried tyo merge in the file but that also
failed. I tried changing the file extension but LO ignored that. I tried
caopy/paste into Calc but Calc failed to put each tab delimited field into a
separate cell so that failed. Hopeless!

(1) Inside LibreOffice use File > Open. It does not matter where you are. It might be Writer or Impress or wherever. You get a file picker dialog.
(2) Select the file you want to open.
(3) The dialog has a drop-down list 'file type'. Open it and scroll down to the section where are the spreadsheet formats. Choose type 'Text CSV (*.csv,*.txt)'. Open.
(4) You get the import dialog. Set the needed parameters. Be careful about language, date and number formats.

Kind regards
Regina

Dennis_E_Hamilton · June 19, 2011, 8:20pm

I just checked the file-open dialog from within Calc in LibreOffice 3.3.2.

The .txt extension is listed under both Spreadsheets and Text document.

If it doesn't work, it definitely seems to be a bug.

I am surprised there is no File | Import ... Calc menu item for this and similar cases.

It is good there is a workaround. Seems to be a bug too.

- Dennis

jfn · June 19, 2011, 8:26pm

There is. It's Sheet / From file (the exact wording might be different as I'm translating from a FR environment).

jfn · June 19, 2011, 8:28pm

Oooops... too fast... The menu entry actually reads Insert / New sheet from file.

Peter_Langfelder · June 19, 2011, 8:40pm

I need to open a tab delimited text file in Calc. I have done this many times
in Excel where you have to Open the File from inside Excel. However, trying to
open it with Calc launches Writer (ugh!!!). It seems the programmers have gone
to great lengths to stop one doing what you wants and needs to do. Obviously,
if one is inside Calc, any file one tries to open should open inside Calc.
That's a no brainer.

Well, it sounds logical but Open/Libre Office seems to use a single
file open dialog across all applications (or at least Writer, Calc and
Impress).

How can I work around this problem? I tried tyo merge in the file but that also
failed. I tried changing the file extension but LO ignored that. I tried
caopy/paste into Calc but Calc failed to put each tab delimited field into a
separate cell so that failed. Hopeless!

You need to be careful specifying the input type. Start Calc, go to
File->Open, then select the file and under the file type selector be
careful to scroll down to spreadsheet types. You will recognize the
section by ODF Spreadsheet, MS Excel and similar file types. Among
them is "Text csv". Select that one, and you will be presented with a
csv/tab delimited loading wizard that should correctly split your file
into columns. I do this all the time and I agree that Excel is easier
to use in this regard.

HTH,

Peter

Tom_Davies · June 19, 2011, 8:43pm

Hi
In Calc it might be more appropriate for that ".txt" option to read ".csv"?
There are other ways a spreadsheet's sheet can be saved as a txt file, for
example by using tabs or spaces instead of comma's to separate values.

Csv is the one most commonly used method, used by the widest range of programs
and devices so it would be good if that was the default option for saving a
sheet as a text file.
Regards from
Tom

Tom_Davies · June 19, 2011, 8:49pm

Hi
Brilliant workaround (as always). I was going to suggest allowing it to
open in Writer then select a tab space and use "Find&replace" to swap them to
commas, then save the file as .csv rather than .txt. Double clicking on a csv
file should open it in Calc/Excel. Much more messy but easier for many people
to understand. Regina's elegant answer is much better imo but it can be good to
have choices
Good luck and regards from
Tom

nunojsilva · June 19, 2011, 8:52pm

I might be mistaken, but in LibO/OOo importing (opening from non-ODF) is
done with File->Open, so the lack of that (Import) menu entry is not
such a big issue.

Tom_Davies · June 19, 2011, 9:07pm

Hi

Where do you get the tab delimited file from? Does it offer a Csv (comma
separated values) option as well? If so then Csv is the more normal method of
transferring data.

Proprietary systems do try to throw a curved ball into the mix to force people
to go over to their systems for everything but Calc devs are probably able to
adapt fairly easily to this particular challenge. It might help to find a
bug-report or write one
http://wiki.documentfoundation.org/BugReport
Good luck and regards from
Tom

Dennis_E_Hamilton · June 19, 2011, 9:08pm

That's it. What a wonderful place for it :).

Yes, it is the Insert | Sheet from file ... menu dialog in the en-Calc too.

- Dennis

Dennis_E_Hamilton · June 19, 2011, 9:14pm

The problem is that both Writer and Calc accept .txt, according to the selections in the File | Open menu, but there is apparently no way to prevent the .txt from going to Writer from File | Open even if the Spreadsheets list of extensions has been selected.

That does seem like a bug. Not major if the Insert | Sheet from file ... dialog solves the problem, but most people would not think to look there.

- Dennis

PS: Andrew, did you choose the Spreadsheets case in File | Open ... before picking a .txt file?

planas · June 19, 2011, 9:19pm

Tom

Hi

Where do you get the tab delimited file from? Does it offer a Csv (comma
separated values) option as well? If so then Csv is the more normal method of
transferring data.

Proprietary systems do try to throw a curved ball into the mix to force people
to go over to their systems for everything but Calc devs are probably able to
adapt fairly easily to this particular challenge. It might help to find a
bug-report or write one
http://wiki.documentfoundation.org/BugReport
Good luck and regards from
Tom

________________________________
From: Andrew Foss <shrisource@yahoo.ca>
To: users@global.libreoffice.org
Sent: Sun, 19 June, 2011 20:35:06
Subject: [libreoffice-users] Calc Major Bug

I need to open a tab delimited text file in Calc. I have done this many times
in Excel where you have to Open the File from inside Excel. However, trying to
open it with Calc launches Writer (ugh!!!). It seems the programmers have gone
to great lengths to stop one doing what you wants and needs to do. Obviously,
if one is inside Calc, any file one tries to open should open inside Calc.
That's a no brainer.

How can I work around this problem? I tried tyo merge in the file but that also

failed. I tried changing the file extension but LO ignored that. I tried
caopy/paste into Calc but Calc failed to put each tab delimited field into a
separate cell so that failed. Hopeless!

Help!!!!

--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Tab delimited files are often originally from a Word or Writer file
saved for importing into a spreadsheet. You are correct about CSV files
and spreadsheets. CSV files will open in Calc correctly and are the more
standard way of saving a file for a generic spreadsheet program.

nunojsilva · June 19, 2011, 9:26pm

There *is* a way, at least here with 3.3.3 (version details in
signature). If you pick the right file type (in the File -> Open file
picker), "Text CSV (*.csv;*.txt)" it opens in calc, no matter if the
name ends with .txt or with .csv.

The CSV option is there and works. But there *is* an unexpected
behavior, when you pick the "Spreadsheets" filter -- it opens in Writer.

jorge1 · June 19, 2011, 9:26pm

Hi:

I used to have the same problem and I solved like this:

1) Open Spreadsheet in Calc.
2) Open .txt file with Writer or Gedit for example.
3) Select all the text that you need to carry to Spreadsheet and copy.
4) Paste in the Spreadsheet. Here could be occur two things:

a) Come the dialog box to format the text that you paste, or

b) Paste the text in the spreadsheet directly. In this case you can use
"Text to Column" from "Data Menu" to make the format that you need.
Remember that you have to select only the first column to do that.

Regards,

Jorge Rodríguez

Andrew_Foss · June 19, 2011, 9:56pm

Choosing Spreadsheets does not work but CSV does if you can find it, it took
some time and I doubtless would not have found it if I had not known it was
there. The app should be able to tell that the Calc UI has been selected by the
user which means they are trying to open a file inside a spreadsheet. If you
think it could ever be otherwise, why not offer a dialog in case it looks like a
Writer file?

Dennis_E_Hamilton · June 19, 2011, 10:13pm

I see the "Text CSV (*.csv;*.txt)" now. When I checked the File | Open ... dialog before, the scroll bar was off the right of my display [;<). That is *way* down there, huh?

- Dennis

Villeroy · June 19, 2011, 8:26pm

Am 19.06.2011 21:35, Andrew Foss wrote:

I need to open a tab delimited text file in Calc. I have done this many times
in Excel where you have to Open the File from inside Excel. However, trying to
open it with Calc launches Writer (ugh!!!). It seems the programmers have gone
to great lengths to stop one doing what you wants and needs to do. Obviously,
if one is inside Calc, any file one tries to open should open inside Calc.
That's a no brainer.

How can I work around this problem? I tried tyo merge in the file but that also
failed. I tried changing the file extension but LO ignored that. I tried
caopy/paste into Calc but Calc failed to put each tab delimited field into a
separate cell so that failed. Hopeless!

Help!!!!

Hello,

-- Use the right filter in the open dialog as advised by planas.

-- Use the Windows context menu of the file as advised by Regina.

-- Import into a sheet of a regular spreadsheet document. Menu:Insert>Sheet From File...(with the link option). This will also store the import settings for this particular file.

-- Text tables are a database exchange format. OOo Base provides a configuration storage for all similar text tables so you can access all similar text tables from all office documents without repeating the import procedure.

-- Open the plain text file in a plain text editor (Notepad), copy, paste-special (Ctrl+Shift+V) in Calc.

OK, 5 methods should be enough for now.
Andreas S.

nunojsilva · June 19, 2011, 10:29pm

It is. Well, it means LibO supports many formats.

But the list is really long, I wonder if someone is thinking about how
to make it easier to browse (if that's possible).