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-xxxxCA","Download from usbank.com. OOMAINC xxx-xxx-xxxxCA","-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/YYYY 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/YYYY 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/YYYY 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

Reply via email to