Re: [libreoffice-users] Mixing LibreOffice Calc formats with ASCII text

Hi,

I tried your example with the Fedora text editor and I was able to
reproduce the behaviour you are describing. The way out is to tell Calc
what type of data you have in the columns before you hit the "OK"
button of the import panel. To do so, make sure the arrows are placed
at the right place (you can move them with the mouse or click on them
to make them disappear) and then click on where it says "Standard" in
the column headings and select the type of data you have from the
pulldown just above (by default it should say "Standard"). My only
issue was with the numbers (I use a "," as a decimal separator) so I
had to perform a search and replace to change the decimal points to a
comma (you can do this in the text editor or in Calc) - you must do
this if you are not using the locale-defined separator in your text
because otherwise Calc will see the "numbers" as pure text.

To answer your other question (fixed-width vs. Tab), "fixed-width" is a
way for you to define where the fields are in your text (this is why
you have the little arrows); tab-delimited (you can also select other
delimiters) implies the fields on a line are separated by a tab (\t)
character (or comma, semi-colon, space, etc from the selection).

I hope this helps.

Rémy Gauthier.

Le dimanche 26 mars 2017 à 11:50 -0400, Doug McNutt a écrit :
> Here are three lines copied from a LibreOffice Calc page
> > 03/24/17    03/24/17        EPMT    p    $16.96    COMM Consumer
Cellular
> > 03/24/17    03/24/17        EPMT    p    $159.64    HOUS    Waste
Management
> > 03/24/17    03/29/17        EPMT    p    $330.65    COMM    Pair
Networks
> The first two columns are dates formatted using numbers/Date/Format 
> 01/01/04 Default to English (USA) Format code MM/DD/YY.  They are 
> > formatted that way for the entire length of possible columns. It's
two 
> columns to allow for sent and deposited dates.
>
> > The third column is empty. The fourth column is a type of payment,
here 
> > Electronic Payment. The fifth column is one letter, here Paid, the
sixth 
> > column F is formatted as number-currency. the rest is two more
comment 
> columns formatted as text.
>
> > I'm going to copy those three rows and install the contents right
here:
> > 03/24/17    03/24/17        EPMT    p    $16.96    COMM Consumer
Cellular
> > 03/24/17    03/24/17        EPMT    p    $159.64    HOUS    Waste
Management
> > 03/24/17    03/29/17        EPMT    p    $330.65    COMM    Pair
Networks
> > Now I'm going to copy the information from the Ubuntu text editor
"gimp" 
> > and paste them back into the spreadsheet. I select the cell at the
upper 
> > left just below where I started. I copy from the text editor and ask
the 
> spreadsheet to re-enter the data.
>
> > I get an "Import" window.  It suggests separator options fixed width
and 
> tab. I don't understand the "fixed width". But it does show the data 
> with little arrows separating the columns. If I copy from the "Text 
> Import" window I get something that copies back in the text editor 
> > exactly as I would have expected. I will spare you seeing the same
thing 
> as above.
>
> I click the button that seems to be correct for closing the Import 
> > window. The spreadsheet seems to have placed the entire block data
into 
> > the three cells in the A column. Selecting the three A cells and
doing a 
> copy and replace into the text editor I see this:
>
> "03/24/17    03/24/17        EPMT    p    $16.96    COMM Consumer 
> Cellular"    (All in column A)
> "03/24/17    03/24/17        EPMT    p    $159.64    HOUS    Waste 
> Management"
> > "03/24/17    03/29/17        EPMT    p    $330.65    COMM    Pair
Networks"
> > I get the same result if I first select a columns A through H before
I 
> do the paste.
>
> Note that the entire lines have been honored with quote marks. The $ 
> > signs are part of the text but it matters not much because it's in
the 
> > wrong column anyway. Note that the spaces are still the tab
characters 
> that they have been all along.
>
> > I have been trying a bunch of schemes that involve copying lines of
data 
> > from a bank into cells over on the right side and then attempting to
use 
> > formulas that move the data to the columns I need. I see one of those
= 
> signs in the front of the dates that just makes the date into text 
> > instead of the coded date it was. The $ sign gets left in the
currency 
> > formatted column which declares it's just text and will fail to add
with 
> other data already present.
>
> Using a multiple step procedure involving the Value() function and 
> removing the $ signs can be made to work but I have to move the data 
> into the spreadsheet one column at a time. Perl5 can handle the 
> > modifications to remove the $ and = signs when the actual source is
a 
> bank but I still have to use =right(8) on the dates to persuade the 
> > spreadsheet to accept what it put out in the first place. The likes
of 
> > 03/24/17 as text with nothing at the ends always gets something
added 
> that makes it into text. (Right now I'm safe from some bank offering 
> 3/24/17 without the leading zero. But. . .)
>
> > I'm pretty good with perl5. Does anybody have some experience in
getting 
> > bank information into text that LibreOffice Calc will accept? It
just 
> takes too long to do everything one column at a time. Is the whole 
> problem a bug in the spreadsheet code?

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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