Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


2017-03-26 17:50 GMT+02:00 Doug McNutt <dougopt@macnauchtan.com>:

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"


Do you mean Gedit? Gimp is not a text editor.


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.


Are those spaces between each column or are they TAB (↹) characters?



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?


I actually use a macro to automatically copy my clipboard to the first free
row of a certain spreadsheet. I go to my bank's web page, hit Ctrl+a
Ctrl+c, then I hit a fat button in my spreadsheet and it puts everything at
the right place. But the information on my bank's page has TAB separators,
so it was very easy. In your case it seems like there are spaces.

However, I experimented a bit with your data. This is what I did, I don't
know if it's relevant in your case:

1. I copied your three lines from this message into the clipboard (select
the text → Ctrl+c).
2. I then opened a new, empty spreadsheet in LibreOffice Calc. Actually I
did it before step 1, but that shouldn't matter…
3. Now I right clicked a cell, A1 in this case, and clicked Paste Special
(or similar – I use the Swedish user interface, I didn't bother to switch
to English…)
4. I can now choose between ”Unformatted text” and ”HTML”. I chose the
first one, ”Unformatted text” and hit OK.
5. Now the Import dialogue pops up.
6. Since your date column are a bit odd here, I selected English (USA), I'm
not sure that's necessary, I think it is in my case but not in yours
(default date format here is the ISO-8601 ”YYYY-MM-DD”).
7. I select Space as separator and I also select ”Merge delimiters” and
”Detect special numbers” .
8. After I hit OK, all the data end up in their different rows and columns,
like expected.

One odd thing is that even though I selected English (USA) in atep 6, the
currency column was formatted as my local currency, not USD. I don't know
if that's a bug or if I did something wrong, but it's easy to correct. Just
pre-format those columns or format them afterwards.


Kind regards

Johnny Rosenberg

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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.