[GNC] GNU generated report in HTML to XL

[Adrien Monteleone]

I just tested the following using LibreOffice 6.1.0.3 and GnuCash 3.4:

1. ctrl+a (select-all) and ctrl+c (copy) from the report within the GnuCash report tab (not a saved file), then ctrl+v (paste) on a blank Calc spreadsheet.

I was given a dialog to choose import coding, select my delimiters, and otherwise fine-tune the import (paste) with a preview of my actual data.

The result is the data was imported without formatting into their respective cells, except that ’Totals’ somehow had a newline character before the figures which would necessitate some additional copy/paste to align them correctly.

2. Save the report to an .html file, open that file with Firefox and then repeat the above select/copy/paste commands.

This gave me a less useful dialog box with little control over the end result. I was able to only select the import language and asked if I wanted to detect ’special numbers’ like dates.

I ended up with a formatted version of the report as it appeared in Firefox but with each respective title or figure in a proper cell

3. File > Open the .html file from within Calc as suggested by Chris Lam.

Without a dialog box, this resulted in a similar situation to #2, but with no grid lines within the report. (like a sheet of paper overlaying the grid)

4. Sheets > Insert Sheet from File (or Insert Sheet and then choose ‘From File’ radio button)

I chose to ‘insert’ the .html file. This gave me the same dialog box as #2, but the result was the same as #3. I could choose to insert before or after the current sheet.


Note, options 1-2 place the data on the current Calc notebook sheet.

Option 3 opens an entirely new workbook with only 1 sheet as your report. (unless you’ve set default # of sheets higher)

Option 4 is *adding* a sheet to the notebook, so you can only place it before or after the current one.

In every case, the data came through just fine, though I’d probably find 3 or 4 most useful as everything is already lined up. If you wanted no empty columns between your account titles and the values, 1 or 2 would work best, but as noted, you’ll have to do some adjusting if you included parent account total lines.

Though I used the keyboard shortcuts, Edit > Paste was always available. Perhaps there is an issue with the workbook or the sheet itself that is disabling it? Try a websearch on ‘Edit > Paste not available, just Paste Only’ for LibreOffice.

A cursory search turns up possibilities of a protected sheet, a read-only workbook, and a long-standing bug on the Windows platform that doesn’t seem to be easily reproducible. However, for those first two possibilities, I would think none of the paste functions would be available.

I also see a suggestion that 3rd party clipboard managers sometimes don’t play nice with LO.


Regards,
Adrien


> On Jan 3, 2019, at 6:09 AM, Finbar Mahon <mahon.finbar at neuf.fr> wrote:
> 
> Thanks both, but I still seem to be having a problem.
> 
> I have a file -
> 
> .... /gnu p&l2018.html
> 
> If I try to cut and paste the data to a new libreoffice calc file I get only 'paste only' as an option under >edit, and nothing else recognisable from >insert.
> 
> I have tried 'insert sheet' under >insert but only get the option to insert before or after and then cannot copy the 'new' sheet into an existing one, if I add 'after'
> 
> There doesn't seem to be a method of cut and paste directly, afaics.
> 
> Thanks, Barry
> 
> On 02/01/2019 17:51, David Carlson wrote:
>> Finbar,
>> 
>> You may need to first open a new blank spreadsheet then cut and paste from the html document into the spreadsheet rather than trying to directly open HTML with Calc.
>> 
>> My experience with LibreOffice Calc is that it will import several different kinds of files.  I use it regularly to convert transaction detail exports from a certain department store website to a form from which I can manually extract data to enter into GnuCash, although I have not figured out how to restructure it to use the CSV importer for that case.
>> 
>> David C
>> 
>> On Wed, Jan 2, 2019 at 9:27 AM Finbar Mahon <mahon.finbar at neuf.fr <mailto:mahon.finbar at neuf.fr>> wrote:
>> 
>>    Hello,
>> 
>>    I exported a GNUCash report to HTMl, but I don't seem to be able to
>>    import it to LibreOffice Calc, the LibreOffice xl equivalent.
>> 
>>    Does anybody know if I must use 'real' MS XL to receive the HTML
>>    file?
>>    In LibreOffice you can save calc files in MS format. Are there any
>>    special tricks for importing?
>> 
>>    I am using 3.3 on Windows 10.
>> 
>>    Thanks, Barry
>> 
>>    _______________________________________________
>>    gnucash-user mailing list
>>    gnucash-user at gnucash.org <mailto:gnucash-user at gnucash.org>
>>    To update your subscription preferences or to unsubscribe:
>>    https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>    If you are using Nabble or Gmane, please see
>>    https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>    -----
>>    Please remember to CC this list on all your replies.
>>    You can do this by using Reply-To-List or Reply-All.
>> 
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.