Issue 34078

Summary: Enhancement of CSV import, i.e. with fixed format files (no field delimitter) leading and trailing spaces should be automatically eliminated like in Excel. Also opening a .TXT file in Calc should not automatically bring up Writer.
Product: Calc Reporter: mmellinger66 <mmellinger66>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: chris, issues, openoffice
Version: 680m51Keywords: ms_interoperability, oooqa, rfe_eval_ok, usability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
tab delimited text file that is openned by oocalc in writer none

Description mmellinger66 2004-09-10 21:33:45 UTC
When importing text in fixed format(probably same in separated files too),
leading and trailing spaces are not automatically eliminated.  MS Excel, for
example will remove the extra space on either end.  This is quite useful, IMHO.

Also, OO is too smart.  If I open a *.txt file from with the spreadsheet, I want
to bring it into the spreadsheet to parse and not open the Writer application.
Comment 1 frank 2004-09-13 09:58:44 UTC
Hi Bettina,

one for you.

Frank
Comment 2 erwin.tenhumberg 2004-10-28 11:39:09 UTC
enhanced summary, set keywords and reassigned issue according to RFE process
Comment 3 chrishk 2005-05-20 14:24:37 UTC
Hi all,

  I have been an OpenOffice user for a while, and started with version 1.1.4. I
have downloaded the latest beta build (OpenOffice.org 1.9.100) and must say that
I am very impressed with the product. In my line of work, I continually deal
with files received from different sources which needs to be imported into our
system. The data comes in various formats and have to be "re-worked" to get it
into a format suitable to import into our system. The files we receive come in a
variety of formats including Fixed-width or a various other delimited type
files. Calc is absolutely perfect for this. However, I am unable to import any
text file properly into Calc anymore since I started using the new version.

  I searched all issues I could find and decided to post to this issue. I tried
everything to open the files but nothing works - I tried using the OpenOffice
dialogs, default system dialogs and even various options - nothing seems to
work... I will attempt to list all my methods:

1. File -> "Open"
   Select file and File Type as "Text CSV (*.csv; *.txt)"
   Select the "Open" button
   RESULT: This imports the file into Writer

2. Insert -> "Sheet..."
   Select "From File" option
   Select "Browse" button
   Select file and File Type as "Text CSV (*.csv; *.txt)"
   Select the "Insert" button
   1.1.4 RESULT: Sheet 1 appears in the box next to the "Browse" button and
                 the import dialog pops up. With this, I could still import
                 the file.
   1.9.100 RESULT: System seems to think/freeze for a few seconds and then
                   returns to "Insert -> Sheet..." dialog. Nothing happens
                   after this. You are free to close the dialog - the system
                   did not freeze, but no import dialog popped up either.

3. Insert -> "Sheet From File..."
   Select file and File Type as "Text CSV (*.csv; *.txt)"
   Select the "Insert" button
   RESULT: System seems to think/freeze for a few seconds and then
           returns to "Insert -> Sheet..." dialog. Nothing happens
           after this. You are free to close the dialog - the system
           did not freeze, but no import dialog popped up either. (Same as 2.)

4. Insert -> "Link to External Data..."
   Select the "..." button
   Select file and File Type as "Text CSV (*.csv; *.txt)"
   Select the "Insert" button
   RESULT: System seems to think/freeze for a few seconds and then returns to  
         
           Insert -> "Link to External Data..." dialog. It did not carry the
           value of the file that was chosen, but this is a seperate issue...
   Type the fully pathed name of the file into the dialog and press the Enter key
   RESULT: System seems to think/freeze for a few seconds and then remains on  
         
           the Insert -> "Link to External Data..." dialog.

  This is unfortunately a real problem as this is much needed functionality that
used to worked. In my humble opinion, I believe that this warrants a priorty
larger than a P3. Could this PLEASE be sorted out for the latest stable version
of OpenOffice (v2) as I am already a fan of the new look-and-feel OpenOffice and
would not want to be left behind on OpenOffice.org. 1.1.4. By the way, before I
forget, I tested the said version on a Windows XP Home Edition platform.

  Thanks to all the contributors for a great product!
Comment 4 Matthias Basler 2005-06-19 11:15:16 UTC
See my comments to issue 49828 on that last matter.

You write:
> If I open a *.txt file from with the spreadsheet, I want
> to bring it into the spreadsheet to parse and not open the Writer application.

My test showed that if you open a .txt file from within OOo Calc it DOES open as
a spreadsheed UNLESS it contains null characters. If you still do opposing
observations, please attach a sample file to reproduce.

IMHO the only real problem in all this that OOo does not accept CSV files with
null characters. All other problems seem to be either indirectly caused by this
or result from chrishk not giving .txt or .csv extensions to his files. ;-)

Comment 5 stuartprescott 2005-06-21 06:08:55 UTC
I think this issue is less about whether OOo *can* import a TXT file or not than
it is a design issue that OOo ignores the current state of the application in
deciding *how* to import it.

For example (under OOo 1.1.3 (Debian Sarge)):

The simple attachment will open in OOo Calc if and only if I select "Tab
Delimited" as the import filter, even though I am already editing OOo Calc. If
that import option is not selected, it opens it in Writer.

But if I have to import 100 tab-delimited files like this, I don't want to have
to scroll all the way down to the "tab delimited" entry (which is helpfully
called "Text CSV"... hmmmm?) for *every* file. It would be much nicer for OOo to
say "I'm currently running Calc, so I'll open this new file in Calc if possible". 

Even just a persistent checkbox in the open dialogue box to enable this feature
would be wonderful... (you could be more clever than that and use a regexp
against the file to see if it looks like CSV or TSV, but allowing *easy*
override of these default behaviours is important)

This is a big barrier to me doing anything serious with Calc (well, that and the
graphing component is hopeless, see
http://www.openoffice.org/issues/show_bug.cgi?id=3997 for more on that!). Until
I can easily import lots of data files from scientific instruments (and then
graph them!) Calc will only be a toy for me.

Comment 6 stuartprescott 2005-06-21 06:09:14 UTC
I think this issue is less about whether OOo *can* import a TXT file or not than
it is a design issue that OOo ignores the current state of the application in
deciding *how* to import it.

For example (under OOo 1.1.3 (Debian Sarge)):

The simple attachment will open in OOo Calc if and only if I select "Tab
Delimited" as the import filter, even though I am already editing OOo Calc. If
that import option is not selected, it opens it in Writer.

But if I have to import 100 tab-delimited files like this, I don't want to have
to scroll all the way down to the "tab delimited" entry (which is helpfully
called "Text CSV"... hmmmm?) for *every* file. It would be much nicer for OOo to
say "I'm currently running Calc, so I'll open this new file in Calc if possible". 

Even just a persistent checkbox in the open dialogue box to enable this feature
would be wonderful... (you could be more clever than that and use a regexp
against the file to see if it looks like CSV or TSV, but allowing *easy*
override of these default behaviours is important)

This is a big barrier to me doing anything serious with Calc (well, that and the
graphing component is hopeless, see
http://www.openoffice.org/issues/show_bug.cgi?id=3997 for more on that!). Until
I can easily import lots of data files from scientific instruments (and then
graph them!) Calc will only be a toy for me.

(This is perhaps an associated issue... if I run "oocalc testcase.txt" where the
file is tab delimited, it opens in Writer not Calc...)
Comment 7 stuartprescott 2005-06-21 06:10:44 UTC
Created attachment 27360 [details]
tab delimited text file that is openned by oocalc in writer
Comment 8 abeelias 2005-07-27 04:06:29 UTC
I urge you all to vote on bug 4929.  This is a BUG that has had no attention in
3 years.
Comment 9 abeelias 2005-07-27 04:07:37 UTC
 bug 4925 oops :)
Comment 10 louis1st 2005-12-04 10:31:20 UTC
is there a solution to this problem yet?
Comment 11 stuartprescott 2006-03-21 18:29:49 UTC
I think there's a problem with the way this bug has been written up... there are
two things involved: 1/ how spaces are handled and 2/ opening a .txt file in
oocalc should suddenly start oowriter for that file.

I've just spent a *very* frustrating afternoon analysing data. It wasn't
frustrating because of the quality of the data but the way that I had to go File
-> Open then select the file I wanted then scroll down a *long* list of formats
to find csv/txt *every* time I opened a file. Guess what... the novelty of that
sure wore off pretty quickly. 

As much as I hate to admit it... it would have been easier and faster with
M$Excel where I could have just done a drag+drop onto excel and it would have
opened the file in excel and *not* in word just because it was a .txt file.

So... ooo devs: how do we go about convincing you that this "feature" is a huge
PITA?
Comment 12 ali_b 2009-12-11 11:28:05 UTC
Please see Issue 85946.
I think this is a more general approach.