Importing CSV into GnuCash

So we have experimented scientifically and carefully and can confirm and replicate the steps below to import CSV files into GnuCash 2.6.4:

1. Open the CSV file (e.g., exported from GnuCash) in an editor (e.g., LibreOffice Calc):

    a. Edit the file to change date column format from MM/DD/YYYY to m-d-yy

    b. If there are split transactions
        i. Add the parent transaction date to each of the splits
        ii. Add the parent transaction account to each of the splits
        iii. Delete the parent summary line
        iv. Delete the parent split line
        v. Select the number columns and delete all minus signs
        vi. save and close.
        
2. In GnuCash:
    a. Import Transactions from CSV

    b. Keep the Data Type as Separated
        i. If it does appear as columns then examine the Separators
            to find the right one (Comma, Tab, Semicolon, etc.)

    c. Change the Date Format to m-d-y

    d. Change the Currency Format to Period

    e. In the None |None |None |None |... row:
        i. Rename the Date, Num, Account, Description, To Num, From Num
            columns to
                Date, Num, Account, Description, Deposit, Withdrawal

    f. If you see headers in the presented view then change 
        i. Start import on row
            to whatever causes the headers to be highlighted in pink,
            e.g., 2

    g. Change 
        i. stop row on
            to whatever clears any data from being highlighted in pink,
            e.g., 4

    h. Click Forward

    i. The Match Transactions screen appears:
        i. It has correctly read the Category column and creates 
            an Info column entry that states
            New, transfer $(xxx) to (auto)"*Category Name*"

    j. Click Apply. It reports success. Click Close.

    k. If it is a new transaction it appears. If it is a duplicate it simply overwrites the original

Discussion

I was on GnuCash 2.4.13 under Linux openSUSE 13.1. This is the default version offered by their repositories. However, in yast2 sw_software you can search for gnucash and open the Versions tab to select the current 2.6.4 offering:

2.6.4-63.1 x86_64

You have to do this also for gnucash-lang to avoid conflicts.

That works. So now we are on 2.6.4.

This offers File → Export → Transactions to CSV

That works just fine.

Importing, however, is a bit more daunting. 

1. First you clear out all the unwanted columns

It appears that the GnuCash CSV import routine requires exactly one line per transaction split, and NO HEADERS, NO TRANSACTION SUMMARY line, and a funky DATE FORMAT.

So you have to load the exported CSV file into an application, e.g., LibreOffice, and farkle with it a bit. ESPECIALLY if it was exported from GnuCash.

2. Regarding the date format: 

It appears that the routine requires m-d-y. But neither Windows 7 nor LibreOffice have that explicitly. You can enter m-d-y as a custom date format in LibreOffice, but 09/04/14  is rendered 9-4-y

So you must use m-d-yy in LibreOffice. 

3. Remove unwanted rows:

It appears that the GnuCash import routine expects one and only line for each transaction and really only requires 5 fields to work. 

But if the CSV was exported from GnuCash you will have at least three lines with fifteen fields:
    1. A summary line for the transaction with Account and fifteen other fields.
    2. Another line for each destination account but no date or account or description fields
    3. Another line for the parent account transaction split again without date or account or description fields.
    
    You must remove #1 and #3.

Otherwise, a piece of cake. 

Easy when you know how.

Solved: Chromium Aw Snap problem under Linux openSUSE 13.1

Just a quick post for google fodder:

I'm running under Linux openSUSE 13.1 with Chromium Version 37.0.2062.94 (290621) (64-bit)

I started getting "Aw Snap" errors in the middle of September 2014. Tried all the usual fixes (disabling, enabling extensions, deleting the profile, etc.)

The error message is 


[23335.702221] Media[1252]: segfault at 0 ip 00007f940df42700 sp 00007f940a73e3e8 error 4 in libffmpegsumo.so[7f940ddb3000+23a000]

Checking in yast2 sw_single I see there are two ffmeg modules:

     chromium-ffmpeg

     chromium-ffmpegsumo

The second only appears when you disable the first.

So I did, and the problem disappears. Now when I check yast2 sw_single the first has disappeared.

So it would appear that an upgrade to the ffmpeg package broke chromium, deleting the older version fixed it.

Easy when you know how.

A Work in Progress: OwnCloud and CALDAV

Very very cool.

OwnCloud and Caldav Sync Free Beta, that is.

I wanted to get away from big data. This link gave me the inspiration. So I've installed OwnCloud. 

Avishek Kumar does an excellent job of providing a straight-forward overview of the installation process:
     http://www.tecmint.com/install-owncloud-to-create-personal-storage-in-linux/

So now it works at http://localhost/owncloud/index.php/ but I want sync.

Well first we explored import/export of .vcf and .ics files. This is an interesting exercise, and I've learned a lot. In particular, 

     http://forum.owncloud.org/viewtopic.php?f=3&t=3067

is an excellent exposition on automating the download of .vcf and .ics files from OwnCloud. 

I worked these into a BASH script and quite happily now have the requisite files. And Evolution quite happily imports them.

But what about the 'droid?

Well, it turns out that there really is no happy way to import .ics files into the Android calendar.At least I couldn't find one.

So bite the bullet and start learning yet another something new: DAV.

Simply stated, in the beginning there was WebDAV, and then CardDAV and CalDAV. All are protocols for synchronizing data.

Well, it turns out that Konqueror (and a lot of other file managers) already have webdav support built in. So the first step (synchronizing, exchanging files) is a piece of cake. Enter the following (obviously changing the path to your OwnCloud's URL) in the address field of the file manager:

     webdav://localhost/owncloud/remote.php/webdav

This will prompt y     ou for your OwnCloud username and password, and then present the OwnCloud file tree.

Easy when you know how.

Next: Synchronize Evolution:

But this is problematic. All our attempts fail.

On the other hand, the Android is a delight:

     https://play.google.com/store/apps/details?id=org.gege.caldavsyncadapter

just works.

It's a little tricky, since it does not appear in the normal App Store. Go to 

     Settings →Accounts

and choose Add Account. Choose CalDav Sync Adapter and add the required information, e.g., 

     User:               andy
     Password:     **********
     URL:
               http://localhost/owncloud/remote.php/caldav/calendars/andy/defaultcalendar

For Calendar:
You get the URL from the OwnCloud screen. Click the gear icon in the upper right corner. There are a number of icons. The second one is the link. Click on it and read the URL in the popup dialog. 

(There are also a number of greyed-out sub-icons, including a download arrow. Click it. it will download the current calendar as an ics, that then happily imports into Evolution.)

For Contacts there is no such gear icon. The URL is in the form:

               http://localhost/owncloud/remote.php/carddav/addressbooks/andy/default

But the calendar works great, so that is enough for now.

Easy When You Know How: Deskewing text scans under Linux

http://galfar.vevb.net/wp/projects/deskew/

I've been scanning documents for ages, but more recently than before. Especially in large numbers. 

So I use the ADF (Automatic Document Feeder) on my HP-6400 scanner with XSane to direct the images to a Download directory.

But inevitably they are off by a fraction, no more than a fraction, like 0.5°, but my eye still catches it. And deems it unprofessional.

But if you have fifteen pages, opening each one to correct them is a royal PITA. And time consuming to boot.

So I cast about for ways to fix it: 

• Looked for GIMP plugins. There was one once upon a time called Deskew but it seems to have evanesced.
• Looked for options under Imagick. Its convert and mogrify scripts are very capable. There is a deskew option, but I did not find any simple description of how to use it.
• Looked for other options...

And found the cited program deskew.  It is current, and has versions for Linux (both 32- and 64-bit), Mac, and Windows. You can recompile it if you really want to, but it comes with a Bin subdirectory containing precompiled versions for all three OS.

And it is a positive dream to use. Most of the parameters are precompiled with defaults. I changed the binary RRGGBB background (-b) parameter to white (fffff) from its default black (000000), but frankly haven't had the time to examine whether that was really necessary.

So the command at its simplest for me was

     deskew -o output.jpg -b ffffff input.jpg

Yes, you have to know what you're doing: unzip the download, move the entire resulting Deskew directory to somewhere useful, e.g., /data/graphics You also need to 

     chmod +x deskew

to make it executable and adjust your .bashrc to include that path, or simply prepend it to the deskew command:

     /data/graphics/Deskew/Bin/deskew ...

So, generally, a breeze.

A very elegant piece of work.

Well done. 

Easy When You Know How: Garmin .tcx download and conversion under Linux

My quest to free myself from Windows continues, successfully.

There is very very little left for which I need Windows. One thing (until now) has been to download exercise data from the Garmin Forerunner 405 watch.

The quest began with Google, of course, which revealed Gant.

Gant is a program that has faded from sight. garmin-ant-downloader (GAD) is a fork.

Both purport to link to Garmin devices, e.g., the Forerunner 405 watch.

I tried both the 32- and 64-bit versions of GAD and could not get it to work. It threw segmentation faults.

http://www.jamesarbrown.com/?p=5 provides a link to a version of gant that works:

     https://github.com/jamesarbrown/Gant

You can download a zip of the source files that also contains the fully compiled gant program. It also has a copy of the binary auth405 file.

And it just works:

1. Unzip the zip file to a location you like, e.g., /usr/local/bin/holdings/fitness/gant.

2. Go there in a terminal as root, make gant executable, and change the permissions to user: 
      # cd /usr/local/bin/holdings/fitness/gant
     # chmod +x gant     # chown -R andy:users *

     a. You may need some additional files if it doesn't work initially, but it worked just fine under openSUSE 13.1.

3. Set the watch to pairing and listening mode as discussed at
http://community.linuxmint.com/tutorial/view/818:
Turn on pairing:
     Settings → ANT+ → Computer → Pairing: On
Tell it to retransmit recent data
     Settings → ANT+ → Computer → Force Send: Yes
And enable pairing
     Settings → ANT+ → Computer → Enabled: Yes

4. Plug the ANT+ USB dongle into a USB port on your computer. The ANT+ USB dongle will be recognized by the operating system and assigned to device ttyUSB0. (You can run dmesg to see this result.)

5. In a terminal session as root, change to the directory where you have installed gant, then pair the watch to the computer:
     # cd /usr/local/bin/holdings/fitness/gant
     # ./gant -f garmin -a auth405
     The -f switch assigns the name garmin to the pairing agreement. The auth405 file is the secret key used to pair with the 405. The program has to be run as root to be able to access the ttyUSB0 device.
     The watch will ask if you want it to pair. Press the upper right (Enter) button to say yes. This pairs the watch to gant, giving the watch the name garmin and writing the pairing key to auth405 in the current directory.

6. As root download the latest activities:
     # ./gant -nza auth405 >output.txt
     Note: output.txt is only the session.log. There will not be any other output unless you have actual training data on the watch.
     The routine will present each activity as YYYY-MM-DD-HHMMSS.TCX if you have actual activities on the watch. The routine always outputs output.txt as a session log.

7. Now, what to do with it?

https://sites.google.com/site/garmin405linux/ details how to upload the output.txt to Garmin Connect.

http://developer.garmin.com/schemas/tcx/v2/ discusses the TCX format.

https://forums.garmin.com/showthread.php?22222-Convert-Garmin-XML-(TCX)-to-CSV-and-analyze-with-Excel
     presents a routine to convert TCX to a comma-separated-variable (.CSV) format that can be read into a spreadsheet. But it is written in perl for Windows and throws an error at line 65. I've checked the perl dependencies and so forth, but nothing jumps out to resolve the issue, so too hard for now.

On the other hand,
     http://www.teambikeolympo.it/TCXConverter/TeamBikeOlympo_-_TCX_Converter/TCX_Converter_ENG.html 
(mind the line break) offers a very smooth app to read TCX files.

     It has a comprehensive manual at http://www.teambikeolympo.it/TCXConverter/TeamBikeOlympo_-_TCX_Converter/DOWNLOADS_files/TCX-Converter-UserManual_209.zip

     The only glitch I see is that it only recognizes .tcx files if the suffix is lower case (.tcx not .TCX). 

     But otherwise, quite robust. For example, it gives the following for each track:
     Time     Lat     Long     Altitude     Distance     Heart Rate (BPM)     Cadence

     It also gives a host of export formats, including CSV.

Yet another step away from Windows. I'll be golden if I can find a way to graph heart rate and show tracks on a map.

Easy When You Know How: Setting up SNI, Virtual Hosts, and Secure Socket Layer (SSL)

There must be a zillion links to someone discussing one or another of these topics. And pages and pages of Apache documentation. And Google fodder (stuff you can look up) galore.

So, what: yet another howto site?

Well no. 

I hate when people start out with "All you have to do is..." Because inevitably there is always a lot more to "do". That is because people are in a hurry, hurtling, and ASS-U-ME that others know what they know. Or they're out to make a quick buck and really don't care what the content of the blog is as long as people click on the ads.

The other thing I hate is Linux man pages (and many subsequent sites) because they never give an example.

Which is not the case here.

"All you have to do is":
1. Meet the Prerequisites
2. Set up Virtual Hosts
3. Set up the /etc/apache2/listen.conf
4. Configure the hosts
5. Import your organization's root certificate into your browser
6. Restart apache
7. Optional: Set up Local Hosts

Gosh, is that all there is to it? Piece of cake...

Not. 

My intent here is to pull it all together (with ample Google fodder) to let a relatively knowledgeable newbie get from a blank sheet to an operating SSL-protected web presence in a single shot.

("Relatively knowledgeable" is here defined as a computer geek who just hasn't met SSL before, but has done HTML and programming and understands Linux, LAMP, and BASH. Otherwise, stop and go home before you hurt yourself.)

I have a server in Tulsa, Oklahoma (I'm in Rhode Island) that I'm using as a sandbox to refine my geek skills. I've got Drupal and a host of other CMS in addition to ages of straight-stick HTML experience, but haven't (until now) actually built something with SSL. So reasonably, I thought I'd first set it up in a sandbox on the localhost machine. Easy, right?

Well, no. At least not until you know how. We now know how, but that may last for only a while...

(Stuff changes. Suck it up... :-(

I'll offer up Google fodder in that format for stuff beyond the scope of this article, but have fun) and point out some real gotchas along the way:

First: understand the difference between versions of Apache. 

• Sounds like a "duh", but when you go googling for stuff you are often served stuff based on version 2.2, while the current (May 2014) version is 2.4.x. 
• Necessarily, a lot of stuff has changed: default directories, default filenames, NamedVirtualHost directives, parsing of httpd.conf into subordinate directories with Include or IncludeOptional directives, and so forth.
• For example, many references talk about the "<VirtualHost> block" in httpd.conf. Well, on my system (openSUSE 13.1), there is no such thing. Instead there is /etc/apache2/vhost.d/ containing any number of desired .conf files, each constituting (and instantiating) a different VirtualHost.
• Additionally NamedVirtualHost directives have been deprecated in Apache 2.4 and can raise errors from listen.conf.

     So make sure you are using references to the current version of your server.

Second: in a similar vein, recognize the differences between operating system distributions. 

• openSUSE 13.1 puts stuff in the following tree:

          /etc/apache2/
              ssl.xxx/            # where xxx = key, csr, crt, crl, prm
              vhost.d/

     YMMV with other distributions, e.g., Ubuntu.

Third: Understand the difference between Server and ServerName:

• The former refers to the machine on which the website is mounted. In my case it is localhost. In other cases it may be 123.235.345.456 or some other IP address.
• The latter refers to the CommonName (CN) of the website, e.g., privustech.com.
  
  These conflicts are replete throughout the setup.

Fourth: Reeely understand certificates:

• ca.crt is the PEM-encoded certificate for the Certiicate Authority (CA) who certifies (signs) your server and website certificates. The CA for a self-signed certificate is yourself in whatever fashion you wish to represent yourself. Or the CA can be one of a zillion commercial certification activities, e.g., Thawte, etc. The point is that the CA certificate must be cited in your browser Root Certificate cache to prevent your browser raising an error when you try to go to one of your SSL-protected sites.
• <vhost>.key is the PEM-encoded encryption key to go with the associated certificate, whether for the CA, the server itself, or the various VirtualHosts.
• <vhost>.csr is the PEM-encoded X.509 Certificate Signing Request (CSR) for each of the client entities (server, <vhost>). There is an openssl command command that uses the CSR and the ca.crt to create a signed certificate for the client (server, <vhost>)
• <vhost>.crt is the resulting PEM-encoded X.509 certificate for the client.
• Most importantly, the CA CN cited in the client .crt must match the CA CN in the corresponding ca.crt and the keys must match as well.
• There are additional tricks, like Certificate Revocation Lists (CRL) and public DSA Parameter Files (PRM) that I haven't touched. Apparently, I do not need to, TBTG... :-)
• There is a very good and comprehensively specific protocol to follow for creating your own ca.crt and the consequential client (server or <vhost>) .key, .csr, and .crt files here.

Fifth: Tip your hat to SNI (Server Name Indication) . The details don't really matter since it's all automagic, at least in Apache 2.4, but you need to know the name of the horse you are riding:

• SNI (Server Name Indication) is an extension to SSL that allows multiple SSL-enabled Web sites to be served from a single IP address and port (443).
• The server relies on the client to report the hostname as part of the HTTP headers with name-based virtual hosting. Many different hosts can share the same IP address using this technique.

Sixth: Back into the "relatively knowledgeable newbie" domain: you do understand IP addresses, FQDN, ports, domains, and all that, don't you...

====================

So, with all that foregoing and foresaid, let's get started:

You must create a block (in httpd.conf or the Included /etc/apache2/vhost.d/<vhost>.conf) for the existing host if you are adding virtual hosts to an existing web server. Otherwise you will block access to the existing non-virtual host.

The DocumentRoot included in the .conf MUST  be the same as the global DocumentRoot. Note: the same:

So if you have loaded all your websites on localhost at /srv/www/htdocs then use

     DocumentRoot /srv/www/htdocs

List this virtual host first in the configuration file so that it will act as the default host.

These are defined in httpd.conf. Except that actually, the line in that file is:
IncludeOptional /etc/apache2/vhosts.d/*.conf

So if you create a conf file in /etc/apache2/vhosts.d/ it is automatically included when the server is restarted.

We'll set up two sites with SSL: privustech.com and privuscloud.com. Both sites will be served by the same IP address. We'll use separate SSL certificates for each site.

1. Meet the Prerequisites

     Enable SSL and Create Certificates.

• There is a very good and comprehensively specific protocol to follow for creating your own ca.crt and the consequential client (server or <vhost>) .key, .csr, and .crt files here.

2. Set up Virtual Hosts
     Reference:
            http://httpd.apache.org/docs/2.2/en/vhosts/name-based.html
     Create directories for the hosts (the basic server is implicitly at 
          /srv/wwwhtdocs/):
          # mkdir /srv/www/htdocs/privustech.com
          # mkdir /srv/www/htdocs/privuscloud.org

     Use your favorite HTML editor to create an index page, e.g., index.html

     Set their ownership and permissions
          # chown -R wwwrun:www /srv/www/htdocs/privustech.com
          # chown -R wwwrun:www /srv/www/htdocs/privuscloud.org

3. Set up the /etc/apache2/listen.conf:
     Change listen 80 to listen *:80
     Change listen 80 to listen *:80
     Reference:
          http://httpd.apache.org/docs/2.4/bind.html
               Apache may need to be told to listen on specific ports, or only on selected addresses, or a combination of both. This is often combined with the Virtual Host feature.
               The Listen directive tells the server to accept incoming requests only on the specified ports or address-and-port combinations.
               Multiple Listen directives may be used to specify a number of addresses and ports to listen on.
               The Listen directive does not implement Virtual Hosts - it only tells the main server what addresses and ports to listen on.

4. Configure the hosts
     a. The hosts are configured in
          /etc/apache2/vhosts.d

           which presently contains

               vhost-ssl.template vhost.template

     b. Copy vhost.template for a generic non-SSL host localhost.conf. 

          This is necessary because creating other virtual hosts will block normal http access to localhost. It will still work if you append a different port, e.g., 8080, 9000 (appending :80 does not help):
          # cp vhost.template ./localhost.conf

               Reference: 

                    http://httpd.apache.org/docs/2.4/vhosts/name-based.html

                         Main host goes away

                         Any request that doesn't match an existing  is handled by the global server configuration, regardless of the hostname or ServerName.

     c. Copy vhost-ssl.template for each of the intended SSL hosts <vhost-ssl>.conf, renaming them accordingly (these do not affect localhost:80)
     # cp vhost-ssl.template ./privustech-ssl.conf     # cp vhost-ssl.template ./privuscloud-ssl.conf
     The name does not need to match the CN as the CN is specified within the file.
     d. Edit each virtual host's <vhost-ssl>.conf. E.g.:
            ServerName privustech.com

            ServerAlias *.privustech.com

            DocumentRoot "/srv/www/htdocs/" 
                #This is the parent of the site, not the site itself
               #/srv/www/htdocs/privustech.com returns a 404

            ServerAdmin alavarre@lavarre.org

            ScriptAlias /cgi-bin/ /srv/www/cgi-bin/         

     Change the server key and certificate to those for the server, not the website:

            SSLEngine On     SSLCertificateFile /etc/apache2/ssl.crt/server.crt 
               #This is the server not the website

            SSLCertificateKeyFile /etc/apache2/ssl.key/server.key
              #This is the server not the website

            SSLCACertificatePath /etc/apache2/ssl.crt     
            SSLCARevocationPath /etc/apache2/ssl.crl 

5. Import your organization's root certificate into your browser
     The browser has a cache of "root certificates", certificates from Certificate Authorities that it has been taught to trust. It will not trust a self-signed certificate if the CN of the CA cited in the certificate is not included in its "Root Certificates" cache.

     Reference:
          http://stackoverflow.com/questions/7580508/getting-chrome-to-accept-self-signed-localhost-certificate

Use Chrome → Settings → Show advanced settings → HTTPS/SSL → Manage certificates → Authorities → Import →

This will establish lavarre.org (or whatever CN you used in the ca.crt) as a "trusted" root authority

6. Restart apache
     service apache2 restart

7. Optional: Set up Local Hosts
     Edit the /etc/hosts. 
          It is in the form:
               IP-Address Full-Qualified-Hostname Short-Hostname

          and already has the entries
               127.0.0.1 localhost

               192.168.5.2 tm2t tm2t

               plus a bunch of ip-v6 addresses.

     The discussion suggests adding an IP address for the various virtual hosts, but we leave this alone as we have already connected to
          /localhost/privustech.com

Hope this helps. Comments welcome, and we'll revisit to retweak as necessary.

But for now, it just works.

So, how long will all this take? About twice as long as it took you to read through this and understand it. 

But you had to have read every sentence and understood it. Nevermind the googling...

This has taken me a week to compose, and even after having composed and published it easily half a day more to debug and edit. And then, when published, easily a couple of hours to read through and reflect on the instructions, comments, and implied links.

So if you are a guru, maybe about three hours. If not, then tomorrow is another day.

There's no such thing as a free lunch. You can have it big, or you can have it quick, but you can't have it big and quick.

Except it's easier when you know how (and have kept a log...)

23 April 2014 City Council Meeting Resolution 14

The following is a clip from

http://www.thenewportrecord.com/CityCouncil/ncc.4.23.14.Ytx.htm

showing the discussion of Councilor Farley's proposed resolution. The clip is from approximately time 00:59:15 through 01:14:36 of the posting above: