Categories
Point-of-sale

How to import data from Microsoft Dynamics POS to Quickbook POS

by Amit Ramani/ @anterikinc

Import Data from Microsoft Dynamics POS to Quickbooks POS

My project for the day was migrating from Microsoft Dynamics to Quickbooks POS. This involves a procedure to import data (including Customers, Inventory, Vendors) from Microsoft Dynamics POS into Quickbooks POS. For those of you unfamiliar with this, POS stands for Point-Of-Sale. The simple POS’es are the ones at the grocery store cash registers where the clerks scan the merchandise and ring you up.

About Microsoft Dynamics

Microsoft Dynamics is an older POS software suite. It is quite popular within the small-business community. One of my clients has a POS that uses Microsoft Dynamics. As part of their expansion, the client purchased a new POS system for their new location, a mobile showroom.

About Quickbooks POS

Quickbooks is the relatively new entrant to this market. The big advantage with Quickbooks is that it can easily integrate with other Quickbooks software, including Quickbooks Accounting, Quickbooks Pro and sync up inventory with Quickbooks web connector for online ecommerce sites.

While I do not know what format Quickbooks POS uses for storing its data, it does have a very handy utility for importing the following:

  1. Inventory
  2. Customers
  3. Vendors

That said, it also includes some very useful templates with lots of instructions. The QB POS Template is an Excel spreadsheet containing one worksheet each for Inventory, Customers and Vendors.

Extracting Data from Microsoft Dynamics POS

There are two ways to extract data out of Dynamics POS

1. Using Inventory reports under Reports menu in the Management View/Console

2. SQL Queries to get raw data back from database.

With either step, you will need to Export the data to Excel xls spreadsheet or CSV files.  At first, I used Reports method for extracting the data. However, after a dry run with importing into Quickbooks POS, I discovered a problem.  Reports does not provide access to several internal fields, related to product/items in the inventory. In my case, I was not able to get the “Bar Code Number” for each product. That field is absolutely needed when importing into QB POS, because it  is used as the unique identifier.

Step-By-Step Guide for Exporting Data from Microsoft Dynamics POS

Step 1. Access the Query Tool via Menu->Tools->Database->Query Tool…

Dynamics POS Access Query Tool
Dynamics POS Access Query Tool

Step 2. Type the SQL Query in the Query Tool Window and click on the Run button

Dynamics POS SQL Query
Dynamics POS SQL Query

Step 3. Click on Results tab and Export to desired format (Excel, CSV etc)

Microsoft Dynamics POS SQL Query Results
Microsoft Dynamics POS SQL Query Results

SQL Queries to get raw data back from database

Running SQL Queries is not intended to be used for those not versed with atleast a cursory knowledge of database queries. The SQL syntax is straight-forward once you get used to it.

For example, for getting all vendors info, here is the query I used:

SELECT * FROM Supplier

This query will return all the info related to Suppliers, which can then be exported to Excel. This Excel spreadsheet can be used for importing into Quickbooks POS. See below for Step-By-Step instructions on importing “Vendors”.

Similarly, to get all  Departments, use the following query

SELECT * FROM Department

For obtaining all customers, simply type this query

SELECT * FROM Customer

For items, its not as straightforward. The trick here is to get verbose Category names and Supplier names for each item. The standard query only provides numeric Category ID’s and Supplier IDs. It does not provide the names of the Supplier or the name of Category that the item belongs to.

Here is the query I used and it worked quite nicely

SELECT <place all columns name that you need from each of tables here> FROM Item i LEFT OUTER JOIN Category c ON c.ID = i.CategoryID LEFT OUTER JOIN Supplier s ON s.ID = i.SupplierID

My specific query to retrieve all items with corresponding Supplier Names and Category Names :

SELECT Description, ItemLookupCode, Price, SalePrice, Cost, Quantity, ReorderPoint, SupplierID, ExtendedDescription, SubDescription1, SubDescription2,SubDescription3, BarCodeNumber, c.Name AS CategoryName, s.Name AS SupplierName FROM Item i LEFT OUTER JOIN Category c on i.CategoryID = c.ID LEFT OUTER JOIN Supplier s ON i.SupplierID = s.ID

This query will give you all the items along with their corresponding Supplier Names as well as Category/Department names.

From the Results window, choose File->Export. Name your exported file in a detailed manner consistent with the query. You can now take the CSV or XLS file and use it for import into QB POS.

 

Step-By-Step Guide for Importing Records into Quickbooks POS

1. First select the Data Import Wizard from “File->Utilities->Import” from the main menu.

Quickbooks POS Main Menu Import
Quickbooks POS Main Menu Import

2. The Data Import Wizard window appears. Click Next.

Quickbooks POS Data Import Wizard Windows
Quickbooks POS Data Import Wizard Windows

3. Select “Vendors” and click Next

Quickbooks POS Step 2 Import Vendors
Quickbooks POS Step 2 Import Vendors

4. Then select “Custom File” and click Next. Be sure to select “Custom File” and NOT “Default Template”.

Quickbooks POS Import Vendors Step 3
Quickbooks POS Import Vendors Step 3

5. Click on Browse. Then select your spreadsheet file. Select the correct worksheet under “Data is on Sheet:”.

Quickbooks POS Vendor Import Step 4
Quickbooks POS Vendor Import Step 4

6. Now, click on “Manage Mappings“. Here you will manually select the mappings between your spreadsheet columns and the Quickbooks Template. Pay special attention to “Required” fields, highlighted in Red. Give the Mapping a name so you can use it in the future.

Quickbooks POS Import Vendors Step 5
Quickbooks POS Import Vendors Step 5

7. Select “Full Name Only” and “Prompt me and let me decide”. This sets the action Quickbooks will take for determining duplicates and what to do in case it finds duplicates.

Quickbooks POS Import Vendors Step 6
Quickbooks POS Import Vendors Step 6

8. The next window will show you how many vendors are contained in the file, if there were any errors processing the file, number of vendors that will be added and number of vendors that will be updated.

Quickbooks POS Import Vendors Last Step
Quickbooks POS Import Vendors Last Step

Voila! You are done importing vendors.

You will follow the same general guidelines/steps for importing Customers and Inventory items. If you have any questions, feel free to ask below.

Happy Importing!

by Amit Ramani/ @anterikinc

Categories
code ecommerce

Importing oscommerce into WooCommerce

oscommerce is a very good platform that is very flexible and allows any kind of possible customization. However, as far as design layout and user interface go, those are not oscommerce’s forte.

That said, I have a few clients who have requested that they be upgraded to a new ecommerce platform. Having looked at Magento, Open Cart, I have finally decided on WooCommerce. For those of you that are not as familiar with WooCommerce, it is a full-scale ecommerce plugin that allows your WordPress-based website to become a fully featured ecommerce shop.

A logical step for migrating to a new ecommerce platform involves importing the data from the old platform (oscommerce in this case) into WooCommerce. I understand WooCommerce offers an importer through Tools->Importers->Wordpress. I ran their dummy_data.xml file through that importer.

However, I was specifically looking for an importer that would extract the information (Categories, Products, Orders and Customers) from oscommerce. Enter “WooCommerce  oscommerce Import” to the rescue. While it worked smoothly for the most port, easily importing all products, categories, orders and customers, I ran into one small glitch.

The Categories imported from osCommerce appeared twice in both the Admin Dashboard and the Front end of the site. I have documented a support post for this issue. While I was not able to completely root cause the issue, the workaround that worked for me (quite nicely, I might add), was to comment out one of the function calls inside the woocommerce_osc_run_cats function.

//add_woocommerce_term_meta($term[‘term_id’], ‘order’,$category[‘sort_order’]);

It seems like there is another piece of code somewhere else (outside of this plugin) that adds an ‘order’ entry to the wp_wocommerce_termmeta table for each category. So when I commented out the call from the plugin, there was only 1 entry for ‘order’.

If someone has run into this issue or have some other feedback, please respond in the Comments below.