One way to convert from your existing accounting software package to Bizuno is called the ‘Line in the Sand’ approach. This article describes the process that needs to be followed to implement this approach. This method is best used at end of fiscal year.  The basic process is:

  1. Verify your chart of accounts in Bizuno matches that from your current application.
  2. Import contacts (Tools -> Import/Export -> Contact Tab). This should be completed well before the conversion day. A template is available on the Contacts import tab to help format the input data and includes field names and descriptions.
  3. Import Inventory (Tools -> Import/Export -> Inventory Tab). This should also be completed well before the conversion day. A template is available on the Inventory import tab to help format the input data.
  4. Build Inventory, Accounts Payable and Accounts Receivable files (see examples below). These files are minimized so they may be generated by hand quickly (if necessary) are output directly from your current application.
  5. Print trial balance from your current application to a PDF file or paper for use old guys.
  6. Backup Bizuno (Tools -> Business Backup -> Business Backup section -> Go icon), just in case. This is just for precaution as your new values will have precedent.
  7. Purge the general ledger (Settings -> PhreeBooks -> Tools -> Purge Journal Entries).
  8. Enter Beginning Balances using the data from your Trial Balance printed from above. Save. This will take a while as the information needs to roll through each gl account for all months in your fiscal calendar.
  9. Print Trial Balance in Bizuno (Tools -> Reports/Forms -> General Ledger -> Reports -> GL Trial Balance -> Open (get a popup) -> PDF icon). At this point your balance sheet and income statement will not match that form your prior application. This is because these entries are BEFORE the current fiscal year. All accounts that are closed at the end of the fiscal year will be closed and the balances will be rolled into your retained earnings account. You will need this report for making adjustments later in this procedure.
  10. Import inventory valuation (Tools -> Import/Export -> PhreeBooks Tab -> Inventory, select file and press Import icon)
  11. Import purchase orders (Tools -> Import/Export -> PhreeBooks Tab -> Purchase Orders, select file and press Import icon)
  12. Import accounts payables (Tools -> Import/Export -> PhreeBooks Tab -> Accounts Payable, select file and press Import icon)
  13. Import sales orders (Tools -> Import/Export -> PhreeBooks Tab -> Sales Orders, select file and press Import icon)
  14. Import accounts receivables (Tools -> Import/Export -> PhreeBooks Tab -> Accounts Receivable, select file and press Import icon)
  15. Print another Trial Balance in Bizuno. This is where the paper part is best.
  16. Edit your beginning balances and subtract the AP total from your import file from the accounts payable account (to bring it back to your original beginning balance), subtract your AR total from your import file from your accounts receivable account, take the GL account you used as your holding account and add back the payables total and subtract the AR totals from your import file. This should restore all three accounts to the values you had BEFORE the import (step 7). Verify your inventory stock balances to make sure the import was successful.

This is a good time to check you AP/AR accounts as the value after step 8 should be equal to the total of your import for each type. If they are off (as they usually are) you will need to make a general journal entry to get them back to being even.

This process only works when converting at year end as the expenses, sales accounts are zeroed out at the start of each fiscal year. For this reason, you will see no expenses OR sales as they will be rolled into your retained earnings account. This is because beginning balances are the values ‘entering’ the fiscal year.

A mid year line in the sand conversion will require additional steps. Generally, you will need to post a GL entry restating your activity year to date that is posted to any account that is closed at year end, (sales, expenses, etc.). Create or use a holding account that you can balance against when adjusting your beginning balance to compensate for the duplicate values. After this process, your balance sheet and income statement from your prior accounting application should match exactly with the like Bizuno financial statements.

I think you should try a few dry runs before the big day. You can always purge the GL and start over. This way when the day comes, you’ll get it done in under an hour.

Sample inventory.txt file:

SKU,Qty,Total
Tire-Balloon,5,10.00
ANewSKU,2,13.00

Sample purchase_orders.txt file:

InvoiceNumber,PostDate,Total,BillContactID,SKU,Qty,LineTotal
PO345,12/12/2016,55.00,DIY Bike Co,Wheel-Black,11,55.00
PO364,12/14/2016,45.00,Tony’s Tires,Wheel-Black,4,20.00
PO364,12/14/2016,45.00,Tony’s Tires,Tire-Knobby,1,25.00

Sample accounts_payables.txt file:

InvoiceNumber,PONumber,PostDate,Total,BillContactID,HoldingGLAccount
GD20161222,PO-Dave,12/22/2016,66.00,goDaddy.com,3800

Sample sales_orders.txt file:

InvoiceNumber,PostDate,Shipping,Total,BillContactID,ShipContactID,SKU,Qty,LineTotal
3423,12/20/2016,10.00,110.00,Jim Taylor,Jim Taylor,Wheel-Black,2,100.00
3427,12/21/2016,25.00,275.00,Dave Jones,Dave Jones,Wheel-Black,4,200.00
3427,12/21/2016,25.00,275.00,Dave Jones,Dave Jones,Tire-Knobby,1,50.00

Sample accounts_receivables.txt file:

InvoiceNumber,PONumber,PostDate,Total,BillContactID,HoldingGLAccount
20322,,12/22/2016,175.00,New Guy,3800

Read More

contacts11-Dec-2013  / PhreeBooksR36RC3
Author – Charles
Draft

PhreeBooks: Importing ‘contacts.csv’ – Guidelines

Take care to make sure that your ‘contacts.csv’ file is prepared correctly:

1. remove any spaces in field name at top of columns e.g. ContactType, ShortName, Inactive, FirstName …
2. no null column headings – all columns to have a PB ‘field name’ (from an exported PB contacts file – test data)
3. only import columns that are required or have specific information. Delete columns containing no information.
4. fill required fields. e.g.

ContactType: c for Customers, v for vendors, i for CRM Contacts
ShortName: this is a required field. You can use zip codes, telephone numbers or random values
Inactive: set to 0
StoreID: set to 0
PrimaryName_1: this is a required field, I would use FirstName LastName. Can also be the company name, main billing name, etc.
AddressType_1: needs to be the ContactType and the letter m, i.e. cm for customer, main address

5. Remove any columns AddressType_2 and AddressType_3 as these are only used if more than one address is imported
6. Remove any CreationDate and LastUpdate columns
7. Remove any fields: RecordID, RecordID_x (address book record ID 1-5), ReferenceID_x (address_book link to contact table, 1-5). These are automatically generated by the system and should not be included.
8. Remove any rows without entries i.e. no nice-looking gap row beneath the column headings and before the data.
9.   CountryCode_1 – the 3-letter ISO Country Code is type char e.g. USA, GBR, CAN, ROI, LUX
10. RecordID – start at 2 and go up from there. Row 1 is reserved for column headers.

TOOLS > Import / Export > Contacts Module (green arrow on RHS) > Tables available to Import > select ‘csv’, browse to your newly created contacts.csv and ‘import contacts’.

 

 BEWARE – the ‘TSV’ problem & EOLs:

Quote Wikipedia:
… many so-called “CSV” files in fact use the tab character instead of comma (such files can be more precisely referred to as “TSV” for tab separated values)

Wikipedia talks about the lack of an agreed standard for .csv

PhreeBooks importer requires comma-delimited text file (.csv). The problem is that a tab-separated (as opposed to comma-separated) file ALSO has a .csv file type.

As to the TSV issue, if you use LibreOffice to edit a customer database, by default it opens the file with LibreOffice Calc instead of LibreOffice Write, and when you save the edited file, although you are saving it as .csv, it saves it with tabs instead of commas. Calc makes it easy to see if data is “misaligned”, and to correct it, but once that is done, you need to save it, close it, and open it with Writer so you can replace all the tabs. The final issue is to make sure that all the records end with ^M. If you are working in Linux/UNIX or Mac, you have to find a way to do that. For Linux users it’s easy:
“cat -v fname”.

NOTE: Dave has confirmed to me that EOL’s (line ends) are irrelevant to the PhreeBooks import script as the PHP script strips them away before import.

June 2014 / PhreeBooksR36RC3importicon
Author – Charles

Importing Inventory should be straightforward:

NOTES:

  1. Make absolutely sure that when you OPEN your spreadsheet programe to read the ‘Sample CSV’ and/or to create your .csv for Inventory Import, that you select ONLY the      ,delimiter. I have come across spreadsheet programs defaulting to 2 or 3 different delimiters – if a .csv is created with more than 1 delimiter defined, it will not import into PhreeBooks. Moreover, later ‘saving as a .csv text file’ and/or mucking around with it in Text Editors will not help you – you will have to re-create the .csv from scratch. See more details in SKU-Pricer    …   at the bottom of the page.
  2. If you re-run the Inventory Importer a second time with the same SKU’s present, you will get a second, double-entry of these SKU’s.
  3. Inventory Importer can be used to ADD SKU’s to an inventory already populated with other SKU’s.
  4. If you want to amend SKU details that are already entered into Inventory using a .csv bulk update, use SKU-Pricer.
  5. If you want to manually amend SKU details that are already entered into Inventory, either amend each SKU details page or:
    CUSTOMERS > Customer Price Sheets > Load Item Pricing (bottom left in blue letters)
  6. RecordID – start at 2 and go up from there. Row 1 is reserved for column headers.
  • Export a ‘Sample CSV‘ and open in a spreadsheet – make sure that the spreadsheet used to prepare your .csv for Importing Inventory uses the correct column-headings (no gaps in column-headings). You don’t need to use all column-headings, however, if not being used for .csv import, they should be removed.
    TOOLS > Import/Export
    Inventory Module, green arrow on RHSinventoryimport2
  • Make sure that you have no gap rows or gap columns in the block of data that you want to import into PhreeBooks. It should all be tight, up in the top left-hand-corner of your spreadsheet. Remember, no column or row gaps (you can have empty cells).
  • Make sure that the spreadsheet column Type (Inventory type) is populated correctly. Make an Inventory Export to get the correct ‘Type’, if necessary.
  • Save the file as a .csv (comma-separated and NOT tab-separated) text file. Only use double quotes (“) as a delimiter if a field has a comma within it.
  • You are now ready to import your Inventory.
    TOOLS > Import/Export
    Inventory Module, green arrow on RHS
  • Select ‘CSV‘ file-type to import, browse to your .csv file and then click on ‘import inventoryinventoryimport3
  • Clear browser cache

NOTE: there is no Import Report (a simple one line report, in green, on import success) in Importing Inventory, unlike SKU-Pricer.

Please post any comments or queries to the Forum.

priceJune 2014 / PhreeBooksR36RC3
Author – Charles

NOTE – There is a bug in ‘SKU Pricer’ in PhreeBooksR36RC3 which will be resolved in PhreeBooksR3.7. However, in the meantime, the latest (updated)  SKU-Pricer Module is available at GitHub.com and can be downloaded from there. It can found in the PhreeBooks-master folder,
/extensions/Modules/sku_pricer
GitHub latest: PhreeBooks-master 

What is SKU-Pricer?

SKU-Pricer is intended to update details held in your inventory tables in the PhreeBooks database in bulk. It is handy when a bunch of cost/price information has changed and it provides a common format (.csv) file structure to update PhreeBooks inventory fields.

skupricer

SKU-Pricer allows you to update the

  • item_cost
  • full_price, and
  • upc_code

of any / all SKU’s that already exist in Inventory, by bulk importing new pricing and upc-codes from a .csv file.

SKU-Pricer will not create new SKU entries in Inventory.

SKU-Pricer allows a PhreeBooks user to update their pricing (applying the  margin / markup in the spreadsheet beforehand) without having to  laboriously go through each SKU manually updating prices. This is a huge time saver.

NOTE: (Wikipedia)  The Universal Product Code (UPC) is a barcode symbology (i.e., a specific type of barcode) that is widely used in the United States, Canada, the United Kingdom, Australia, New Zealand and in other countries for tracking trade items in stores.

Additionally, many (most) other fields in your inventory file may also be updated using SKU-Pricer. Fields that are controlled by PhreeBooks will be ignored during the execution of the script, e.g., quantity_in_stock, id, quantity_on_sales_order, etc.

Fields from the core inventory database table available to update include:

description_short
description_sales
account_sales_income
account_inventory_wage
account_cost_of_sales
item_taxable
price_sheet
full_price
full_price_with_tax
item_weight
minimum_stock_level
reorder_quantity
lead_time
upc_code
description_purchase
price_sheet_v
purch_taxable
item_cost
vendor_id

NOTE: There is a possibility that you can mess up your books and prevent PhreeBooks from operating properly if the values imported are not within the valid range expected. It is recommended that if you are not sure of a field or the expected values, don’t update them using this tool.

Creating the .csv Import File :

If the SKU’s don’t already exist, you will need to create them before importing your .csv file.  The SKU must exist in the database prior to running an SKU-Pricer import, otherwise there will not be a record to update.

The SKU format used in the spreadsheet above was generated using the very quick PhreeBooks ‘Master Stock Item‘ method of generating SKU’s :
Stock Type – Attribute1Attribute2.
You may, however, use any SKU format that suits you.

A .csv file can be created using a spreadsheet. Please ensure the following:-

  • The column-titles are exactly as detailed above. Remove any spaces in the field name.
  • There are no nice looking gaps – either gap rows, or gap columns.
  • Ensure that either the sku or upc_code column is present. Other fields must fit within the expected format of the database field. The script will look at the sku field first, if present will set the upc_code field based on the SKU field.
  • When you OPEN or create your .csv file, make sure that ONLY ‘Comma separators’ are used.
  • Save the file as a .csv (comma-separated and NOT tab-separated) text file. Only use double quotes (“) as a delimiter if a field has a comma within it.

Gedit

The layout of your .csv file should look like this when viewed in a Text Editor.

This is the view in Gedit (Text Editor).

Importing Your .csv  File:

INVENTORY > SKU Pricer

  • Browse to and select the .csv file to import
  • Click on ‘Open’ the file
  • Click on the ‘Save’ icon in the top left-hand corner

Import Report:

You should see a simple, green-colored one line Import Report on the outcome of the .csv import, above the  ‘SKU Price Importer‘   page title.

 report

The SKU-Pricer Import Report will only display the number of rows that were changed during the execution of the script (‘Total affected rows‘). The SKU-Pricer import script will only count rows that had at least one field changed from what was originally in the database.

If you re-run the same .csv file through twice, the second run will result in 0 rows changed.

report1

And if you run the importer a third time, having changed just one field in one row (in this case just one upc_code), then ‘Total affected rows‘ will = 1.

report2

Finally, you can double-check that the new prices have updated successfully in any of three places:

  1. INVENTORY > Inventory Manager > double-click on any SKU
  2. CUSTOMERS > Customer Price Sheets > ‘Load Item Pricing’ (bottom LHS in light blue)
  3. In the PhreeBooks database

Potential Problem With Opening / Creating & Saving .csv File:

I recently came across an instance where my .csv file wouldn’t upload in SKU-Pricer. Nothing short of completely re-creating the .csv file from scratch overcame this problem for me. However, I now think that I know what I was doing wrong. I am going to detail it so that you can avoid this very easy-to-make mistake.

The mistake I think that I made was the way that I first OPENED the .csv file to manipulate the data prior to upload with SKU-Pricer  (this is a counter-intuitive problem for me).

Of course, I was very carefully making sure that I saved my .csv files as ‘comma separated‘ files, making sure that ‘tab’, ‘semi-colon’, ‘space’ and all other possible options were carefully avoided. However, I wasn’t as careful when I OPENED my .csv file.

When I first OPENED my .csv file (in LibreOffice), a number of the options under the heading ‘Separator options:’ were already selected – ‘Tab’, ‘Semicolon’ and ‘Comma’. Because the formatting looked correct in the small formatting window (see screenshot below) I assumed all was OK – expecting to set my .csv file as ‘text .csv‘ when saving it later – which I did.

What I wasn’t expecting was that I had somehow defined my .csv file in the process of OPENING it – I hadn’t noticed that the spreadsheet programme had by default selected three seperator types. I believe that this left me with a corrupted .csv file. This possible corruption was not remedied by my carefully selecting ‘text .csv‘ upon saving, nor was it later identifiable when double-checking in the LibreOffice and the Text Editor. This corrupted .csv file would not load into PhreeBooks.

When I later re-created the .csv file and selected just ‘Comma“, carefully UN-checking ‘Tab’ and ‘Semicolon’, I created a .csv file that loaded in SKU-Pricer without any problem.

So I suggest that you are careful both when you open / create and save your .csv file.

seperators

NOTE: Dave has confirmed to me that EOL’s (line ends) are irrelevant to the SKU-Pricer import script as the PHP script strips them away before import.

Please post any comments or feed-back to the Forum.