2018 Graphic Tool To Export Customers, Vendor and Employee data from GNUCASH

Author: Jeffrey Paesch
Last updated: January 02, 2020

This tool is created to export Customer, Vendor and employee within gnucash windows (dialogs)
No need to create SQL commands.
No need to create python script
The software will not collect personal data

To download the latest version click here

License:
    Proprietary.   The tool is free to use. The tool is delivered as it is. The tool may not be recompiled,
    modified and rebuild without authorization of the author

Limitations:
    While exporting data you can not touch or use keyboard and mouse!
    The tool does not capture drop down menu values.
    The tool exports 9-10 customer per minute.
    The program is not digital signed. Therefore, some Antivirus may report this file as virus.
    The program will expire at the end of every year. Please be kind to come back to this 
    blog to download the latest version. 

 Instructions:

• Download the file from the above mentioned url
• Open gnucash file
• Run the downloaded tool
• While running you can not interrupt the program
• The exported data is stored in a csv file in the same folder location as the program
• Also a second csv file is created that is ready to import in gnucash

Support
    For support or to send a bug, please send a email at support@ppsolution.net

Change log:

• Version 20.1.2.9
• January 2, 2020
• Lincese renewed for 1 year
• Version 2019
• February 1, 2019
• Lincese updated
• Version 2018.13
•  January 3, 2018
• Critical: detect if file handler exists for gnucash file
• Critical: error when trying to create excel workbook object 
• Critical: issue closing Gnucash program before complete export
• Input box and button added to manage output directory
•  Label added to gui for support and bugs 
• Improved program notifications
• Version 2018.12
•  January 2, 2018
•  Expiration date updated
• Version 2017.124 
•  January 24, 2017
• Graphic interface added with:
• Option to select gnucash file
• Option to select which type of data you want to export
• Performance increased
• Output file naming modified
• Other minor updates
• Version 2017.123
• January 23, 2017 
• Disable debug Mode

• Version 2017.120. 
• January 20, 2017
• Fixes to activate gnucash
• Vendor export is added
• Employee export is added

• Version 2017.14.
• Initial version. Export Customer Only.

Recommended Page:
Export GnuCash Data for WIndows
GNUCASH CLOSE BOOKING YEAR

Please leave a comment to improve this article.

GNUCASH CLOSE BOOKING YEAR

I kindly like to thank you for reading my blog. Before we continue I like to inform you that I'm not an accountant, but an experienced IT in deploying accounting software and have a junior degree in business administration.

What really happen during closing of a booking period is that you will move total expenses and income balance to your Equity balance. The result of both balance could be a profit or loss at the end of the booking period. This means that your expenses and income accounts for the new booking year will open with zero (0.00).

In Gnucash, the above is an easy task by running a running a tools from the menu Tools - Close book.
This tool will prompt you for a closing date, in most cases the last day of the year. The total Income and expenses account must be created in the Equity account tree. You may use the following:

•  Equity
• 20xx    ( the closing year)
• Expenses
• Income

Last but not least a description, like EOY 20xx  (End of Year 20xx).

Although some people or companies like to close their booking period with the above mentioned tool, but some may prefer to keep separate files for each booking year. They may reason that each closed booking year should be a read only file. They are right! But this same reason is also a disadvantage in case of corrections. For this situation you will have to correct the old year file and also the new year file.

For those who want to keep each booking year separate, here follows some steps and recommendations to help you with closing and opening new booking period in separate file in Gnucash.
Create booking period in a separate file

1. At the end of the period, make sure you book all your income, expenses, interest received, etc
2. Make sure you match your accounting with  Bank statements
• Note: Some atm withdrawals or expenses may reflect in the next periode.
3. Run all schedule task and disable them, if you have, if neccessary
4. Save and Close Gnucash 
5. Backup the gnucash file in case something goes wrong. (duplicate the gnucash file and rename including something like "BEFORE End of Period" or else)
6. Open gnucash file again
7. Run the close book year: Tools - Close book.
8. Save Gnucash
9. Create the new file year by exporting the account structure: File - Export- Export Account
• At prompt type a name and browse for the desire location to save the new file structure
10. Export the customer, vendor, employee data using one of my method discuss on this blog
• https://ppsolutionnet.blogspot.com/2017/01/export-gnucash-data-for-windows.html 
11. You may make print screens of your File - Proprieties and Edit - Preference screen
12. Generate a Balance sheet Report: Reports - Assets and Liabilities - Balance sheet
• You must include the transaction from the beginning till the end of the period
• Also modified the report level to show level 3 or 4 (recommended)
13. Close Gnucash
14. Open the new file created
15. import the customer, vendor, employee data you have exported previously
16. Amend the business properties
17. Amend Gnucash preference if necessary
18. Using your balance sheet create the open balance entry (or Balance brought forward) for each Assets and Liabilities account. Note: Invoices can not be imported in this turial, but the opening balance. Payment for invoice created in the previous booking year should be post manually.
19. Save your file

To make you gnucash file read only

1. Go to the file location
2. Right click the file and slect properties
3. In the file roperties windows, check the option Read Only.
4. Click ok to save and exit the Properties windows

For this point you are unable to save or overwrite the previos booking period file. Nevertheless you may save it to another location and has access back to write, but the one locked will remain locked

To unlock the file, just repeat above, but uncheck the Read Only option

In case of error
In case of error, do not panic! You have create the backup as suggested at step 5, right?
If not, post me your email immediately.
If you did then restore the backup you created at step 5 and start over at step 6.

If you have difficulties following my tutorial, you may contact us or comment your request .

Export GnuCash Data for WIndows

THIS ARTICLE IS DRAFT and some section may be not completed.

At the end of the year 2016, I was looking for a best option to close my books in Gnu cash.
I google many website and found out that the best way to achieve my goal is to create separate gnucash file for each booking year. (I manage to do this way, click here).

But how to export the customer, vendor and employee data to the new file?
This subject seems to be a long discussion on internet. Some of this discussion are so complicated for a average user computer. Therefore I though it is better to bring a solution for this controversial issue.

There are several method! But on this page i will discuss 3 way!

1. Export GNUCASH data through Mysql workbench
2. Export GNUCASH data through Mysql command
3. Export GNUCASH data through Graphic tools 
4. Export GNUCASH data from the xml file (gnucash file itself)

1.Export GNUCASH data through Mysql workbench

If you familiar with some basic sql command it will be easy to extract the data in a minute. However the installation of Mysql server will take hour to set it up. The following steps are  written for Windows OS, but you may follow these steps on other OS.

Install Mysql

1. Download full package of MySQL Community Server from http://dev.mysql.com/downloads/mysql/. (You need to have an oracle account to download the files)
2. Run the installer and choose sever only setup type.
• Make sure you do not loose your root password. Write it down. 
• If you are able, please create an gnucash account
• Do not forget to  install as the Mysql workbench!!! 

                For more information, please read the documentations at
               https://dev.mysql.com/doc/refman/5.7/en/installing.htm.

Covert Gnucash xml file to Mysql database

1. Open gnucash and open the desire gnucash file to convert
2. Save as that file with the MYSQL format, with the mysql account you created earlier in the Mysql setup with it correspondent password.
3. Close Gnucash

 Export Table with Mysql Workbench

1. Open the Mysql workbench and follow the instruction https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html to export the data from customer, vendor , employee or any table in the data base.

2.Export GNUCASH data through Mysql command

1. Folow the steps from Install Mysql and Covert Gnucash xml file to Mysql database from "1.Export GNUCASH data through Mysql workbench"
2. Start command prompt and type the following command (I assume you install Mysql in C:\Mysql)
1. C:
2. Cd C:\Mysql\bin
3. mysql SELECT * FROM customers INTO OUTFILE 'c:/var/lib/mysql-files/customers.csv'FIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n'
4. mysql SELECT * FROM vendors INTO OUTFILE 'c:/var/lib/mysql-files/vendor.csv'FIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n'
5. mysql SELECT * FROM  employee INTO OUTFILE 'c:/var/lib/mysql-files/ employee.csv'FIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n' 

3.Export GNUCASH data through Graphic tools 

A tool is develop by me in order to extract the customer, vendor and employee data from the graphic user Interface (GUI) of Gnucash.This tool have some limitations, but work like a champ.

Read more at https://ppsolutionnet.blogspot.com/2017/01/gnu-cash-graphic-custumer-export-tool.html 

4.Export GNUCASH data from the xml file (gnucash file itself)

Under construction.

A tool is being created by me to export data from the gnucash file directly. This tool is beta and will be released soon. You may consider to donate if you wan to support the cost of this project.

Requirements:
7-zip. To download 7-zip, please visit their download page at http://www.7-zip.org/download.html


Please leave a comment to improve this article.