Customer-Vendor Reports via SQLite

[Robert Heller]

At Mon, 9 May 2016 06:47:43 -0500 Lewis Balentine <lewis at keywild.com> wrote:

> 
> Last week a user inquired how to run a report of the Customers and Vendors.
> I had asked the same question a few days prior.
> 
> My solution was simple because I use the SQLite3 data format and I am 
> familiar with the SQL syntax.
> I have tried to document the process I used and how it can be used with 
> the other data formats.
> There are three basic steps:
> 
> 1) Get the data into a SQLite3 data file (even if it is only a temporary 
> copy)
> 2) Extract the relative data to a CSV file
> 3) Open the CSV file in a spreadsheet and massage as needed
> 
> Note:
> This document and the associated files are the exclusive work product of 
> the author without any connection,
> obligations or legal encumbrances to any other entity. This document and 
> the associated files are placed into
> the PUBLIC DOMAIN by the author Lewis Balentine on 9 May 2016.
> 
> The PDF Document 'GNUCashSQLiteReports.pdf' give ful instructions on how 
> to copy the db from GNUCash to SQLite under either Windows or Linux. It 
> also contains a mini introduction to extracting data from the data base 
> via SQLite3. The ZIP archive contains a sample database as well as all 
> the OS and SQL scripts to run the reports.
> 
> I have uploaded the files to the following URLs:
> 
> http://www.keywild.com/DL/GNUCashSQLiteReports.pdf  (948 KBytes)

Two points about your PDF file:

1) For CentOS / Fedora the extra things needed to be installed for SQLite are:

sudo yum install libdbi-dbd-sqlite    (this is the driver required by GNUCash)
sudo yum install sqlite               (this is the command line utility)

(*I* had no interest in a GUI based SQLite database browser, so I made no 
attempt to figure out the CentOS / Fedora package names for sqliteman or 
sqlitebrowser.)

2) And yes, one *never* uses a word processor as a code editor -- that will
just about always cause trouble :-). *I* use JASSPA MicroEMacs (from
http://www.jasspa.com/). Other options include GNU Emacs or vi (at least in 
the UNIX/Linux world).


> http://www.keywild.com/DL/GNUCashSqliteReports.zip   (902 KBytes)
> 
> The ZIP archive file should contain:
> A sample GNUCash db in XLM data format with errors (because that is the 
> default format).
> A sample GNUCash db in SQLite data format without errors.
> Windows command script files: RptCustomers*.cmd
> Linux shell script files: RptCustomers*.sh
> SQLite script files: RptCustomers*.sql
> CSV data files: RptCustomers*.csv
> A spread sheet with the imported data in XLS file. There are several 
> tabs for various reports.
> A set of RptVendor script files
> A set of CleanData script files
> A 'test.sh' Linux bash script file (has example of pause function 
> definition)
> 
> Is there a more appropriate location where they should be uploaded ?
> 
> Regards,
> 
> Lewis Balentine
> Huston, Texas
> 
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
> 
>                                                                                                    

-- 
Robert Heller             -- 978-544-6933
Deepwoods Software        -- Custom Software Services
http://www.deepsoft.com/  -- Linux Administration Services
heller at deepsoft.com       -- Webhosting Services