Trial of LibreOffice

users
#21

I'm not sure this thread is about my problems but the title sure fits.

You've chosen to bury your enquiry in another thread, in which many people may have no interest. Why would you hide your question in this way?

I open my Excel spreadsheet in a Linux shell using the command "librecalc Finance_16.xls". OpenCalc reads it fine ...

Er, what's OpenCalc, please? Does it have anything to do with LibreOffice Calc (or OpenOffice Calc, for that matter)?

One thing I like to do is to log in to my bank and download their idea of changes for a month or less. [...] The bank likes comma separated ASCII text but in Excel2001 I have to have tab separations. A bit of perl5 fixes that easily but neither seems to work for OpenCalc.

LibreOffice Calc will happily import comma-separated values.

The dates from Discover or the bank have date values as MM/DD/YYYY. When I place them over in the right ...

Er, what does "placing them over in the right" mean, please?

... and allow spreadsheet formulas to move them to the right place ...

Are these formulae to be kept secret from anyone trying to help you?

... they come out `MM/DD/YYYY with the starting apostrophe that tells Calc to leave them alone and never convert to date ...

I think it is very difficult to get Calc to convert date inputs to text! Perhaps it is those formulae that are at fault? The only obvious problem is that your bank's data may perhaps not be in MM/DD/YYYY format but instead be quoted as "MM/DD/YYYY". In that case, there are two possible solutions:
o Either remove the tick from "Quoted field as text" under "Other options" on the Text Import window,
o Or use =VALUE() to convert the text values to dates after import. (Note that the VALUE() function will not affect any genuine dates, so can handle mixed sets of data.)

The problem is the same for directly reading a CSV file.

Not for most users, I think ...

When I add the bank items into a point in the ledger near the current time there seems to be no way to ask for a sort that works only for selected rows.

You define which items you want to add in formulae, not by selecting rows. And you certainly can sort selected rows.

OpenCalc needs to have a way to accept dates, when explicitly told, from the MM/DD/YYYY stuff that banks use.

Ditch OpenCalc. Use LibreOffice Calc - or even OpenOffice Calc - both of which do this easily.

I trust this helps.

Brian Barker

#22

"Trial of Libre Office" was not the correct place. Please excuse me.
Brian Barker had some things to say about it and he did offer some help which I appreciate. I did use the prefix "Open". openSCAD is what I was working with earlier and I mistakenly started typing "opencalc". Everything I'm talking about is LibreOffice_calc here and I'm quite sure there is no "openOffice" hiding. In fact I thought that was killed by Oracle years ago.

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/YYYY. I have done some searching and it appears that I just don't understand date formats in LibreOffice_calc. Some while-trying comments:

Managed to open *.csv into new window using the suggested procedures.
Copy and paste into BANK worksheet worked OK in the far right area starting at column K

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 N O
08/17/2016 08/19/2016 GH BASS & CO #4385 JEFFERSONVILLOH 52.27 Merchandise
08/17/2016 08/19/2016 PEPPERIDGE FARM - 328 JEFFERSONVILLOH 16.06 Restaurants
08/17/2016 08/19/2016 WS OUTLET 0463 JEFFERSNVILLEOH0001000161651608171518 56.28 Merchandise

to match into the ledger page I use these formulas and then convert the results to values so I can add the lines to the ledger and sort by one of the date columns.
A =K172, B =L172, (C) 9/27, (D) DISC, (E) B, F =N172, (G) DISC, H =M172
These values are loaded into the left side columns with a fill down.
Row 172 is one of 166 to 197 this month but it varies. Other downloaded data, banks, use the space above row 172.

Formulas =K167, =L167 in columns A and B seem ok as copies

____But _____
Copy and paste-special adds a ' at the start of the MM/DD/YYYY date.
I can't sort by date anymore!

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

Format-NumberFormat-date selection applied to A and B columns.
now do the copy and paste special selecting just date/time
   columns A and B become blank on row 169

Selecting date/time and numbers does the same thing

>>Selecting date/time, numbers, text generates the leading apostrophe.<<

Copy starting at columns K and L
and paste special with date/time, numbers, text does the same thing.

Can someone point me to the book I should be reading? It isn't Excel! This is only one of the problems I was having in the badly addressed earlier message.

#23

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

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 N O
08/17/2016 08/19/2016 GH 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/YYYY 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

#24

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

#25

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

#26

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.

#27

No.

Brian Barker

#28

Hi Tom,

Can find&replace 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,

#29

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

#30

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

#31

I have managed to move my finance workbook from a CY 2001 version of Excel to libre-office-calc.

I had a lot to learn and I'm still frustrated when I want to insert a few lines of entries from the bank. It's a pain having to do a separate entry of the number of lines and then paste in contents. But..

Every time I start up the workbook I get a complaint that I'm using macros and references to another file which is probably where I stored macro-like commands that I wrote for Excel on the Mac. They used the Macintosh Programmer's Workshop, MPW, development tools.

I'd like to get rid of the mouse and keystrokes required to startup. I just say "no" to both but there must be a better way. For those references there is probably a reference in the 20 worksheets but Mr. Calc isn't telling me where they are. As for the macros I'd like to discard them and rewrite those I really need using your command structure. But where are they so I can killem? I have no intentions to return to my old Mac. Ubuntu Linux is closer to what I learned in the 1970's.

#32

I have managed to move my finance workbook from a CY 2001 version of Excel to libre-office-calc.

I had a lot to learn and I'm still frustrated when I want to insert a few lines of entries from the bank. It's a pain having to do a separate entry of the number of lines and then paste in contents. But..

Every time I start up the workbook I get a complaint that I'm using macros and references to another file which is probably where I stored macro-like commands that I wrote for Excel on the Mac. They used the Macintosh Programmer's Workshop, MPW, development tools.

I'd like to get rid of the mouse and keystrokes required to startup. I just say "no" to both but there must be a better way. For those references there is probably a reference in the 20 worksheets but Mr. Calc isn't telling me where they are. As for the macros I'd like to discard them and rewrite those I really need using your command structure. But where are they so I can killem? I have no intentions to return to my old Mac. Ubuntu Linux is closer to what I learned in the 1970's.

#33

Doug McNutt wrote:

I have managed to move my finance workbook from a CY 2001 version of
Excel to libre-office-calc.

I had a lot to learn and I'm still frustrated when I want to insert a
few lines of entries from the bank. It's a pain having to do a separate
entry of the number of lines and then paste in contents. But..

Every time I start up the workbook I get a complaint that I'm using
macros and references to another file which is probably where I stored
macro-like commands that I wrote for Excel on the Mac. They used the
Macintosh Programmer's Workshop, MPW, development tools.

I'd like to get rid of the mouse and keystrokes required to startup. I
just say "no" to both but there must be a better way. For those
references there is probably a reference in the 20 worksheets but Mr.
Calc isn't telling me where they are.

Depending how the data is linked, Edit > Links... might help.

As for the macros I'd like to
discard them and rewrite those I really need using your command
structure. But where are they so I can killem?

Is the document saved as .ods or .xls? Saving as .ods might get rid of the Excel macros. If that doesn't help, try Tools > Macros > Organise Macros > LibreOffice Basic > Organiser...; see of there's anything under the spreadsheet in any of the tabs there.