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


SMX 2013 Initial Feedback

by Amit Ramani/ @anterikinc

SMX Initial Feedback

This was my first SMX conference. In fact, it was my first technical conference of any kind. While $2400 is quite a steep price to pay for the full conference (including the workshop and all sessions), the idea is that you get upto speed on the latest happenings in the SEO, PPC, Social Marketing world. That I think is the perceived goal of SMX.

Towards that goal, I think SMX fared reasonably well. The sessions span a wide variety of different topics from SEO, PPC, Social Media, Semantic, Entity Search. However, the depth of these sessions is questionable, as is the quality of some of the speakers.

The Good

I found the following sessions and workshops the most useful.

  • Bruce Clay SEO Workshop
  • Learn with Google Sessions
  • What are the most important Page Ranking Factors?

Bruce Clay SEO Workshop

The best part about the conference for me was the full-day SEO Workshop by Bruce Clay Inc. In all honesty, the workshop delivers on its promise. Bruce Clay spoke about SEO like second nature, he has the smarts, he keeps up with the industry and does not hesitate when faced with difficult questions. I thought the entire audience of the Bruce Clay Session engaged very actively throughout the workshop. That is always a sign that the audience is grasping at what the speaker has to offer.

SEO All In One For Dummies is included in the class as is the SEO Training binder. Bruce goes over the important aspects of SEO, answers even the most simplest of questions. Add to that, his staff is very pleasant. (shout out to @ChelseAdams)

Learn with Google Sessions

This was kind of unexpected as it was not on the official agenda. In my opinion, these sessions were a hit. Not only was their presentations more clear, it was coming straight from the source. No ambiguity and guesswork whether or not to follow their suggestions and advice. I can not say the same about some of the other speakers in the smaller sessions.

The Bidding Strategies for PPC session by Google was especially useful in that it focused on how to get the most of your campaign if you are squeezed by budget limitations.

Another very useful presentation was how to get your site working smoothly across multiple devices. The speaker presented clear and specific tips instead of generic advice., like “make sure it works on iPads”.

I will post more specifically about each of these sessions with the key takeaways for me. In the meantime, cheers to the organizers of SMX and all the contributors. It has been a very enjoyable experience and I will return in 2014!

Did you attend SMX 2013? What are your thoughts?

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.

backhand tennis

Stan (the man) stuns Murray at US Open!

That Stanislas Wawrinka has all the goods to beat any ATP tennis player, we all knew. What we also knew is that mentally he was sometimes not prepared to overcome that hurdle.

He delighted all his fans (including me) today by beating Andy Murray, the defending US Open Champ, in straight sets. While I have not watched (yet) the full match, I did catch the last few games of the 3rd set. As usual, Stan’s game was on top. Murray did look out of sorts, but that was likely due to what was happening on Arthur Ashe stadium court.

A big congratulations to Stan. By the way, if you have not seen yet, please be sure to check out some videos of his beautiful one-handed backhand. Stan’s one-handed backhand is a formidable weapon.

Here is to hoping Wawrinka is going all the way to the final. I hope Richard Gasquet achieves a similar result against Nadal. My dream final would be Gasquet v Wawrinka, the battle of the one-handed backhands.

ecommerce Wordpress

Evaluation of JigoShop as WordPress e-Commerce platform

So far, I have had some time to look at JigoShop for evaluation as an ecommerce plugin for WordPress. Coming from a setup where we use dedicated CMS’s for e-commerce, the idea of a plugin seems a little too thin, relatively speaking. i.e. maybe the plugin does not have all the features that a full scaled CMS that is geared towards ecommerce use.

I have found that last part to be mostly untrue, since JigoShop supports a lot of features, right out of the box. It also has a variety of secondary plug-ins for SEO, email etc that make it powerful.

Looking closer at the JigoShop code, I realize that internally it does not use separate tables dedicated to the ecommerce functionality. Instead, it embeds and extends the core WordPress database tables for tailoring it to WordPress. At this point, I am not sure what are the advantages and disadvantages of this structure.

I will continue writing about whatever I discover about the different eCommerce plugins like WordPress.

If you have any feedback, feel free to comment below.