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

At 11:50 26/03/2017 -0400, Doug McNutt wrote:
> 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

And what exactly are you trying to do with them? It would help to 
start by explaining that!

> 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 [blah, blah]. The third column is [blah, blah]. The 
> fourth column is [blah, blah]. The fifth column is [blah, blah], the 
> sixth column F is [blah, blah]. the rest is two more [blah, blah].
>
> 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

Hold on: how is that any different from your first copy above?

> Now I'm going to copy the information from the Ubuntu text editor 
> "gimp" and paste them back into the spreadsheet.

You say you copied the information from a spreadsheet. (But I'm 
beginning to think that's not what you are trying to do.) You are now 
trying to paste it into a spreadsheet. You can copy information from 
a spreadsheet cell range from one place to another, from one sheet to 
another, or even from one document to another directly.

> 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.

Good: that's what you need.

> It suggests separator options fixed width and tab. I don't 
> understand the "fixed width".

If you are importing plain text, you may have information in 
fixed-width columns. In that case, you will want to choose "Fixed 
width" and move the column boundaries around appropriately in the 
Fields view at the bottom.

> But it does show the data with little arrows separating the columns.

Are those tab characters? It's worth saying that no-one can really 
see what your data really looks like from what appears in your mail 
message - which has only multiple blank characters.

> I will spare you seeing the same thing as above.

Phew!

> 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)
> [etc.]

So you've got your import options wrong? If your data is really 
separated by tabs, you would want to select "Separated by" and "Tab". 
You can experiment with the options, watching what happens in the 
Fields display until you see what you 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 equals sign indicates a formula. By itself it does not make the 
change you suggest.

> ... the Value() function ...
> Perl5 can handle the modifications ...
> ... I still have to use =right(8) ...

Are these more red herrings? Why not simply paste the data?

> Does anybody have some experience in getting bank information into 
> text that LibreOffice Calc will accept?

Aha! - at last, some indication of what you are trying to do. Is the 
original information - your first table - a plain text file? Or is 
the information in a *single* spreadsheet column, not the multiple 
columns you suggest?
o Copy the information from your text editor (or wherever it first appears).
o In your spreadsheet, use Edit | Paste Special... (or right-click | 
Paste Special..., or Ctrl+Shift+V).
o Select "Unformatted text".
o In the Text Import window, tinker with the options until you see 
what you need in the Fields display. You will probably want Space (or 
Tab?) and "Merge delimiters" selected.
o It is sometimes necessary to select the headers of various columns 
in the Fields display and to use the "Column type" drop-down to 
modify the data type. But this shouldn't be necessary in your case.

> Is the whole problem a bug in the spreadsheet code?

Nope.

See also Chapter 1 of the Calc Guide.

I trust this helps.

Brian Barker


--
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