Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Brian Barker]

At 10:17 17/10/2016 -0400, Charles Marcus wrote:

On 10/15/2016 10:02 PM, Brian Barker wrote:

At 18:10 11/10/2016 +0100, Tom Davies wrote:
Can find get rid of the ' marks to make the values revert 
to 'numbers/dates' rather than being forced into being text?


No.


This claims otherwise:
http://www.ryananddebi.com/2009/11/29/remove-apostrophes-before-numbers-in-openoffice-calc/


Er, yes and no. I was replying, of course, to the suggestion that the 
apostrophes themselves could be searched for and replaced. They 
cannot, since they simply do not exist in the cell data. It's 
important that readers are not confused by the suggestion that they can.


But the web site offers a neat trick, if over-complicated by the 
presence of the circumflex, which unnecessarily locks the search to 
the beginning of the cell text. All that seems to be necessary is to 
search for "." and replace with "&". The process presumably relies on 
Calc's reinterpreting the values as it replaces them - now seeing 
them as numbers, just as it would if they were typed afresh.


In any case, Jean-Francois Nifenecker (who also answered "Nope" to 
the above suggestion) offered a technique using Data | Text to 
Columns... which was superior to my contribution.


Brian Barker  



--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Tanstaafl]

On 10/15/2016 10:02 PM, Brian Barker  wrote:
> At 18:10 11/10/2016 +0100, Tom Davies wrote:
>> Can find get rid of the ' marks to make the values revert to 
>> 'numbers/dates' rather than being forced into being text?

> No.

This claims otherwise:

http://www.ryananddebi.com/2009/11/29/remove-apostrophes-before-numbers-in-openoffice-calc/

Also, if I recall I dealt with this a long time ago and there is an
Addon/Extension that can accomplish this... let me go look...

Yes, the CT2N extension...

http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Jean-Francois Nifenecker]

Hi Tom,

Le 11/10/2016 19:10, Tom Davies a écrit :


Can find get rid of the ' marks to make the values revert to
'numbers/dates' rather than being forced into being text?


Nope.

But you may fix that after the import process has run without having to 
relaunch it.


-- Just select the offending column
-- Go to  Data > Text to columns.
You'll get into a dialog similar to the text import one.
-- Go to the lower panel and select the column by clicking its heading.
-- In the Column type list, select the appropriate format for the data. 
Here, it would be Date (YMD).

-- Click OK.

You're done.

HTH,
--
Jean-Francois Nifenecker, Bordeaux


--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Brian Barker]

At 18:10 11/10/2016 +0100, Tom Davies wrote:
Can find get rid of the ' marks to make the values revert to 
'numbers/dates' rather than being forced into being text?


No.

Brian Barker 



--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[libreoffice-ml . mbourne]

Doug McNutt wrote:

What I have learned:

Most important: Brian is very helpful. Thank you.

Dates are really floating point numbers and the unit is one day.
Somewhere there must be a way of setting the zero point. 1900, 1904,
1970 are common.


Tools > Options > LibreOffice Calc > Calculate > Date; the options are:
  30/12/1899 (default)
  01/01/1900 (StarCalc 1.0)
  01/01/1904

Although it appears under the application's options, from a quick 
experiment it appears that setting is actually saved in individual files 
(at least when saving as ODS). Changing the setting seems to change the 
default used for new documents, but opening an existing file will use 
whatever epoch was saved in that file (again, at least with ODS files) - 
which makes sense as different files might use different epochs. I guess 
with non-ODS formats, LibreOffice will use the default if the file 
format doesn't specify the epoch.


--
Mark.


--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Doug McNutt]

What I have learned:

Most important: Brian is very helpful. Thank you.

Dates are really floating point numbers and the unit is one day. 
Somewhere there must be a way of setting the zero point. 1900, 1904, 
1970 are common. My startup worksheet came from an old, 2001, Apple 
version of Excel and, though I have seen some time values in cy 2020, 
almost all are based on 1904 and show 2016 as the year. Time values 
appear to be rounded so that differences of less than a second get lost 
while sorting. They might be using a 32 bit float.


The format used by US Bank for it's delivery of data as *.csv files 
offers lines like this:


"8/30/2016","DEBIT","DEBIT PURCHASE -VISA OOMAINC 
xxx-xxx-CA","Download from usbank.com. OOMAINC xxx-xxx-CA","-4.2300"


All entries include quote marks and I can't tell if they ever escape 
quotes that are required inside of regions of text. The DEBIT entry is 
often a CREDIT instead. The "-" minus sign is redundant and just means a 
debit.


Getting the download requires use of a browser. I don't think 
Libre_Office has the required security and I don't have time to search 
that way but Firefox will save the bytes in a Linux file where there are 
no provisions for formatting. The calc module will open the file and 
senses the .CSV well enough to read into a spreadsheet format. It 
recognizes the MM/DD/ format and converts the times to the internal 
format used by calc. It also handles the extra zeros in the US$ format. 
There is some white space in front of the xxx items and the xxx's are my 
replacements of decimal digits. I think calc is ignoring them - fine.


I find it convenient to introduce the downloaded information using a 
sheet in the workbook that handles everything else. with the downloaded 
spreadsheet open and the destination open in another screen I can copy a 
bunch of data into columns starting with J.


There are a bunch of details such as identifying column K to see if it 
is a check number and changing a format for that. No big deal and the 
Excel page is adequate except


Copying those MM/DD/ dates into column A and B, where they are 
needed when the new data is entered into the big spreadsheet which 
handles a year of data, can be a pain. The format data for columns A and 
B must be set BEFORE the data is moved over and they don't seem to 
remember that. What works is to fill the destination columns with the 
formula =VALUE(Jx). Somehow that causes calc to recognize the MM/DD/ 
format and convert the integers to an appropriate floating point 
date/time value. If you just use =Jx you get an apostrophe in front of 
the MM/DD item. That's an Excel concept that tells the software to leave 
the data alone. It's useful for specifying long numbers of more that 14 
digits that are found for the likes of part numbers or encoding keys.


The "Format / Number Format / Date" option under the Apple-like menu bar 
is interesting and will let you specify a date format for empty cells 
but it varies between showing a floating point number like 41146 and the 
equivalent 08/26/16. If you don't see what you want try it again.  You 
can add fractional parts to the first format like 41147.25 which moves 
it to 08/27/16 which is limited to the day part. Formatting it as time 
results in "AM".


On this Linux box I like to work with a shell which leads me to a simple 
command to open my spreadsheet. I also noticed the availability of an 
error file. This is a small part of the result. It's mostly warnings but 
those items 34, 36, and 38 are repeated well over 25000(10) times before 
I trimmed them down. I also have zero experience with Java. The machine 
uses Ubuntu and it's current.


Wed Oct 12 14:28:18 EDT 2016
Opening Finance_16.ods with libreoffice --calc
javaldx: Could not find a Java Runtime Environment!
Warning: failed to read path from javaldx
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
** (soffice:27204): WARNING **: Unknown event notification 38
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
** (soffice:27204): WARNING **: Unknown event notification 36
** (soffice:27204): WARNING **: Unknown event notification 34



--
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Tom Davies]

Hi :)
Can find get rid of the ' marks to make the values revert to
'numbers/dates' rather than being forced into being text?
Regards from
Tom :)


On 9 October 2016 at 18:00, Brian Barker  wrote:

> At 18:01 06/10/2016 -0400, Doug McNutt wrote:
>
>> Brian had some questions about how I read, with curl and perl5, the csv
>> files which seemed to be applying an apostrophe to dates formatted as
>> MM/DD/.
>>
>
> I'm not sure why you think you need to preprocess these documents before
> using them. It would be instructive to use them exactly as you get them -
> from your bank - and see what happens. If there are problems, you can see
> what those problem are and decide how to circumvent them - which could
> involve preprocessing but may instead and more easily be modification of
> techniques or further processing in the spreadsheet itself.
>
> Managed to open *.csv into new window using the suggested procedures. Copy
>> and paste into BANK worksheet ...
>>
>
> Whoa! Hold your horses. The question is about how to input the CSV file
> data. Before you do anything else with it, let's survey the situation: if
> the data has been imported correctly, any problems must be being introduced
> by you later; if not, the way forward is to deal with the problem now.
>
> When you import that date data, does it come in as dates or as text?
>
> o Dates will be right-aligned by default. Their cells will have been
> automatically formatted as Date. If you click View | Value Highlighting,
> dates will show in blue text.
>
> o Text will be left-aligned by default. Its cells will remain formatted as
> Number | General. If you click View | Value Highlighting, dates will show
> in black text.
>
> You can examine formatting by selecting a single cell and going to Format
> | Cells | Numbers. For the test to work, it is important that you have
> *not* formatted any cells or cell ranges in advance, so import the material
> into a fresh sheet, not somewhere you have already been using. Best of all,
> right-click the CSV file and use Open With... (or whatever similar facility
> your operating system provides) to open it in LibreOffice.
>
> I selected the csv data and performed a copy followed by a paste into my
>> worksheet but moved over to the right starting at column K.
>> Some samples of the data from the comma separated file:
>>K   L M NO
>> 08/17/201608/19/2016GH BASS & CO #4385 JEFFERSONVILLOH
>> [...]
>>
>
> All this will work, but how to do whatever you need depends on getting the
> data into LibreOffice correctly in the first place.
>
> _But_ Copy and paste-special adds a ' at the start of the MM/DD/ date.
>>
>
> That happens if you paste text into cells previously formatted as number,
> date, and so on. Note also that Paste Special... (as that ellipsis
> forewarns) is not a single process but gives you a range of choices of what
> is pasted and what is not. So you have not clarified what you did here by
> referring only to "paste special".
>
> Changing the format for columns A and B to date doesn't help.
>>
>
> Changing the format of cells never changes the data already in them. Since
> you now have text in these cells, you cannot magically convert that to
> dates (numbers) by changing the format of the cells. (But you can do so
> easily using the VALUE() function.)
>
> I trust this helps.
>
> Brian Barker
>
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-uns
> ubscribe/
> 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
>
>

-- 
To unsubscribe e-mail to: users+unsubscr...@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

Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

[Brian Barker]

At 18:01 06/10/2016 -0400, Doug McNutt wrote:
Brian had some questions about how I read, with curl and perl5, the 
csv files which seemed to be applying an apostrophe to dates 
formatted as MM/DD/.


I'm not sure why you think you need to preprocess these documents 
before using them. It would be instructive to use them exactly as you 
get them - from your bank - and see what happens. If there are 
problems, you can see what those problem are and decide how to 
circumvent them - which could involve preprocessing but may instead 
and more easily be modification of techniques or further processing 
in the spreadsheet itself.


Managed to open *.csv into new window using the suggested 
procedures. Copy and paste into BANK worksheet ...


Whoa! Hold your horses. The question is about how to input the CSV 
file data. Before you do anything else with it, let's survey the 
situation: if the data has been imported correctly, any problems must 
be being introduced by you later; if not, the way forward is to deal 
with the problem now.


When you import that date data, does it come in as dates or as text?

o Dates will be right-aligned by default. Their cells will have been 
automatically formatted as Date. If you click View | Value 
Highlighting, dates will show in blue text.


o Text will be left-aligned by default. Its cells will remain 
formatted as Number | General. If you click View | Value 
Highlighting, dates will show in black text.


You can examine formatting by selecting a single cell and going to 
Format | Cells | Numbers. For the test to work, it is important that 
you have *not* formatted any cells or cell ranges in advance, so 
import the material into a fresh sheet, not somewhere you have 
already been using. Best of all, right-click the CSV file and use 
Open With... (or whatever similar facility your operating system 
provides) to open it in LibreOffice.


I selected the csv data and performed a copy followed by a paste 
into my worksheet but moved over to the right starting at column K.

Some samples of the data from the comma separated file:
   K   L M NO
08/17/201608/19/2016GH BASS & CO #4385 JEFFERSONVILLOH
[...]


All this will work, but how to do whatever you need depends on 
getting the data into LibreOffice correctly in the first place.



_But_ Copy and paste-special adds a ' at the start of the MM/DD/ date.


That happens if you paste text into cells previously formatted as 
number, date, and so on. Note also that Paste Special... (as that 
ellipsis forewarns) is not a single process but gives you a range of 
choices of what is pasted and what is not. So you have not clarified 
what you did here by referring only to "paste special".



Changing the format for columns A and B to date doesn't help.


Changing the format of cells never changes the data already in them. 
Since you now have text in these cells, you cannot magically convert 
that to dates (numbers) by changing the format of the cells. (But you 
can do so easily using the VALUE() function.)


I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscr...@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