{"id":5133,"date":"2019-01-23T12:12:20","date_gmt":"2019-01-23T12:12:20","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5133"},"modified":"2023-09-26T15:04:48","modified_gmt":"2023-09-26T15:04:48","slug":"read-data-from-quickbooks-online-to-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-data-from-quickbooks-online-to-sql-server\/","title":{"rendered":"Read data from QuickBooks Online into SQL Server via ODBC Driver"},"content":{"rendered":"

Introduction<\/h2>\n

\"QuickBooks<\/a>QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement\u00a0QuickBooks Online API<\/a>\u00a0Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using\u00a0ODBC<\/strong>\u00a0in few clicks. We will use\u00a0ODBC JSON \/ REST API Driver<\/a>\u00a0to\u00a0read data from QuickBooks Online\u00a0and Load into SQL Server \/ other targets (Using OAuth Connection). We will discuss on How to Create an Intuit Developer Account, How to Create QuickBooks Online App for OAuth.<\/p>\n

In nutshell,\u00a0this post will focus on how to call QuickBooks Online API using ODBC.
\nSo let\u2019s get started.<\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\n

Before we look into Step-By-Step section to extract and read data from\u00a0QuickBooks Online to SQL Server<\/strong>\u00a0let\u2019s make sure you met the\u00a0following requirements.<\/p>\n

    \n
  1. A first requirement will be to SQL Server Database Engine Installed<\/li>\n
  2. The second requirement will be SSMS installed<\/li>\n
  3. Finally, make sure to have ZappySys ODBC PowerPack<\/a> installed.<\/li>\n<\/ol>\n

    What is QuickBooks Online<\/h2>\r\nQuickBooks<\/b>\u00a0is an accounting software package developed and marketed by Intuit.\u00a0QuickBooks products are geared mainly toward small and medium-sized businesses and offer on-premises accounting applications as well as cloud-based versions that accept business payments, manage and pay bills, and payroll functions.\r\n

    Getting Started<\/h2>\r\nIn order to start, we will show several examples. ZappySys includes an\u00a0ODBC JSON \/ REST API Driver<\/a>\u00a0that will help you to call QuickBooks Online API, read various data from QuickBooks Online with ODBC. To learn more about QuickBooks Online API check this help file<\/a>.\r\n\r\nTo access QuickBooks Online data using REST API call, you need to create an Intuit Developer Account and then create QuickBooks Online App.\r\n

    Create an Intuit Developer Account<\/h3>\r\nFollow the mentioned steps below to create an Intuit developer account:\r\n
      \r\n \t
    1. you can visit this site\u00a0developer.intuit.com<\/a>, and click on Sign up from the menu bar.<\/li>\r\n \t
    2. A registration form will be opened in a Popup. you can fill up and then click on Create Account.\r\n
      \r\n\r\n\"Signup<\/a>\r\n

      Signup Form: Intuit Developer Account<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    3. Complete the validation check and click\u00a0Continue<\/strong>.<\/li>\r\n \t
    4. On the confirmation dialog, provide a few details to fill out your profile and click\u00a0All set<\/strong>.<\/li>\r\n<\/ol>\r\n
      \r\n\r\nYour account is created! You'll automatically be redirected to the\u00a0app creation page<\/a>\u00a0to get coding on your app.\r\n
      \r\n\r\nNote:<\/strong>\r\n\r\nChoose the user ID\u00a0of your account carefully. Once an app is created in a given account\u00a0it cannot be moved to another account or deleted. If you are building apps for a\u00a0client, we recommend you create a dedicated account for that client that stays with them, leaving them full access.\r\n\r\n<\/div>\r\n

      Create a QuickBooks Online App<\/h3>\r\nFollow the steps below to start creating your QuickBooks Online app.\r\n
        \r\n \t
      1. From the\u00a0developer<\/a>\u00a0portal, click on\u00a0My Apps<\/strong>.<\/li>\r\n \t
      2. after that, Click\u00a0Create a new app.\r\n<\/strong>\r\n
        \r\n\r\n\"Create<\/a>\r\n

        Create a new App: Select APIS<\/p>\r\n\r\n<\/div><\/li>\r\n \t

      3. Click the\u00a0Select APIs<\/strong>\u00a0button under Just start coding.<\/li>\r\n \t
      4. Select the APIs you wish to use. This selection can always be changed later via your\u00a0app's settings<\/a>.<\/li>\r\n \t
      5. Your new app opens to its Dashboard tab.<\/li>\r\n \t
      6. Click the\u00a0Keys<\/strong>\u00a0tab. This is where you can find all keys related to your app.\r\n
        \r\n\r\n\"Intuit<\/a>\r\n

        Intuit App: Client Id, Secret Keys for OAuth Connection<\/p>\r\n\r\n<\/div><\/li>\r\n \t

      7. To copy a key or token, click its\u00a0Copy<\/strong>\u00a0button.<\/li>\r\n<\/ol>\r\nFinally, we are ready with an App. Let\u00b4s start with an example. In this example, we will show how to retrieve all Invoices data from QuickBooks Online with ODBC.\r\n\r\n<\/div><\/div>\n

        Create ODBC \u2013 Native Zappysys JSON Driver Data Source<\/span><\/span><\/h3>\n
          \n
        1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:\n
        2. Add the Native – ZappySys JSON Driver Data source.\n
          \"Add

          Add Gateway Data Source (Native JSON Driver)<\/p><\/div><\/li>\n

        3. Now it’s time to connect with QuickBooks. Let’s use JSON Driver Data source with the\u00a0following URL:\n
          https:\/\/{RequestURL}\/v3\/company\/{realmID}\/query?query={selectStatement}<\/pre>\n
          here,\u00a0We have used 3 placeholders.
          \n* {RequestURL}<\/strong>is either a SandBox base URL or Production base URL.<\/p>\n
          Sandbox Base URL: https:\/\/sandbox-quickbooks.api.intuit.com\r\nProduction Base URL: https:\/\/quickbooks.api.intuit.com<\/pre>\n
          * {realmID}\u00a0<\/strong>is\u00a0your QuickBooks Online sandbox\/production\u00a0company ID<\/strong>.
          \n* {selectStatement} <\/strong>can be any query something like Select * From Invoice (*Note: select statement must be URL encoded first)
          \nFor more information on Query, an Invoice Request API\u00a0          see this<\/a>
          \n<\/strong>I am using my sandbox URL like this:<\/p>\n
          https:\/\/sandbox-quickbooks.api.intuit.com\/v3\/company\/193514799372394\/query?query=SELECT * FROM Invoice STARTPOSITION <%startposition%> MAXRESULTS 1000<\/pre>\n
          \"\"<\/a>
          Setup QuickBooks ODBC-based data source configuration<\/p><\/div><\/li>\n
        4. Configure the new OAuth Connection for QuickBooks Online<\/strong> as below.
          \nBasically, you need to configure the configurations of App on the General tab (i.e. OAuth Provider, Client ID, Client Secret, Scopes, etc.)<\/p>\n
        5. Most importantly, you also need to Enter your Callback URL in Advanced Tab.\n
        6. Similarly, you need to check “Do not include credentials in header<\/strong>”\n
        7. Click on Generate Token button to Generate token and then click on Test Connection button and click on OK to save the connection manager configurations settings.\n
        8. Let’s continue on, and enable pagination by configuring it like in the image below in the Pagination<\/em> tab. In the\u00a0Page Num Indicator<\/em> field input this value:\u00a0<%startposition%><\/code>\u00a0Then set Increment By<\/em>\u00a0value to a maximum number of results possible:\u00a01000<\/code>
          \n<\/a><\/p>\n
        9. Finally, now using Code Generator we will generate the query.\n
          \"odbc-json-driver-generate-quickbooks-query\"<\/a>
          Generate Query<\/p><\/div><\/li>\n<\/ol>\n
          Load QuickBooks Invoices data in MS SQL Server<\/h3>\n
          Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.\r\n
            \r\n \t
          1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here:\u00a0Get SQL Server Express<\/a>\u00a0and\u00a0\u00a0Get SSMS<\/a><\/li>\r\n \t
          2. Open SSMS and connect to SQL Server.<\/li>\r\n \t
          3. Go to Root > Server Objects > Linked Servers node. Right click and click\u00a0New Linked Server...<\/strong>\r\n
            \r\n\r\n\"Add<\/a>\r\n
            Add Linked Server in SQL Server<\/p>\r\n\r\n<\/div><\/li>\r\n \t
          4. \u00a0Now enter the linked server name, select Provider as SQL Native Client<\/li>\r\n \t
          5. Enter data source as\u00a0GatewayServerName, PORT_NUMBER<\/span><\/strong>\u00a0where server name is where ZappySys Gateway is running (Can be same as SQL Server machine or remote machine). Default PORT_NUMBER is 5000 but confirm on Data gateway > General tab in case its different.<\/li>\r\n \t
          6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column\r\n
            \r\n\"Configure\r\n<\/a>\r\n
            Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection<\/p>\r\n<\/div>\r\n
            \r\nINFO:<\/strong>\r\n