Search

Index

Revindex Storefront

QuickBooks export sales order (Powershell)

Last updated on 2015-04-17 3 mins. to read

You can use the following Powershell script to export your completed sales orders information to an IIF file suitable for importing into your QuickBooks software.

# QuickBooksSalesOrderExport.ps1
#
# This script will export all completed sales order information 
# to a QuickBooks IIF file.
######################################################################

# Configuration
######################################################################

param
(
    [parameter(Mandatory = $false)][string]$APIKey = 'xxxxxxxxx',
    [parameter(Mandatory = $false)][string]$APIUrl = 'http://my.com/.../Revindex.Dnn.RevindexStorefront/Api/Rest/V1/ServiceHandler.ashx?portalid=0',
    [parameter(Mandatory = $false)][string]$APIUsername = 'host',
    [parameter(Mandatory = $false)][string]$OutFile = 'C:\SalesOrders.iif',
    [parameter(Mandatory = $false)][DateTime]$StartDate = '2001-01-01',
    [parameter(Mandatory = $false)][DateTime]$StopDate = [DateTime]::Now,
    [string]$QBBankAccount = 'Bank account',
    [string]$QBIncomeAccount = 'Income account',
    [int]$NetworkTimeout = 30000
)


# Functions
######################################################################


# Function to help post HTTP request to web service
Function PostWebRequest([String] $url, [String] $data, [int] $timeout)
{    
    $buffer = [System.Text.Encoding]::UTF8.GetBytes($data)
    [System.Net.HttpWebRequest] $webRequest = [System.Net.WebRequest]::Create($url)
    $webRequest.Timeout = $timeout
    $webRequest.Method = "POST"
    $webRequest.ContentType = "application/x-www-form-urlencoded"
    $webRequest.ContentLength = $buffer.Length;

    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($buffer, 0, $buffer.Length)
    $requestStream.Flush()
    $requestStream.Close()

    [System.Net.HttpWebResponse] $webResponse = $webRequest.GetResponse()
    $streamReader = New-Object System.IO.StreamReader($webResponse.GetResponseStream())
    $result = $streamReader.ReadToEnd()
    return $result
}


# Start program
######################################################################

Try
{    
    # Create IIF file
    # http://support.quickbooks.intuit.com/support/Articles/HOW12778
    # http://www.qblittlesquare.com/2011/07/import-lists-into-quickbooks-with-iif/
    # Write headers
    ('"!TRNS","DATE","ACCNT","NAME","CLASS","AMOUNT","MEMO"') >> $OutFile
    ('"!SPL","DATE","ACCNT","NAME","AMOUNT","MEMO"') >> $OutFile
    ('"!ENDTRNS"') >> $OutFile

    # We need to construct our XML request using the parameter list
    $strRequest = "<?xml version='1.0' encoding='utf-8'?>
                    <request>
                      <version>1.0</version>
                      <credential>
                        <username>$APIUsername</username>
                        <apiKey>$APIKey</apiKey>
                      </credential>
                      <service>GetSalesOrdersByDateRange</service>
                      <parameters>
                        <startDate>$StartDate</startDate>
                        <stopDate>$StopDate</stopDate>
                      </parameters>
                    </request>"
                    
    # Execute the API call
    $xRequest = [Xml] $strRequest
    [Xml]$xResponse = PostWebRequest $APIUrl $xRequest.InnerXml $NetworkTimeout
    if ($xResponse.response.code -ne '2000')
    {
        Write-Host "Error executing GetSalesOrders. Response: " + $xResponse.response.code + ' ' + $xResponse.response.message
        return    
    }

    [System.Xml.XmlElement]$salesOrders = $xResponse.SelectSingleNode('/response/return/salesOrders')


    $qbIIF = @()
    foreach ($salesOrder in $salesOrders.SelectNodes('salesOrder')) 
    {                 
        # Export only completed orders
        if ($salesOrder.status -ne '4')
        {
            continue
        }
    
        ('"TRNS", "' + ([DateTime]$salesOrder.orderDate).ToString("yyyy-MM-dd") + '","' + $QBBankAccount.Replace('"', '""') + '","' + $salesOrder.billingFirstName.Replace('"', '""') + ' ' + $salesOrder.billingLastName.Replace('"', '""') + '","' + $salesOrder.totalAmount + '","SalesOrder",""') >> $OutFile
        
        ('"SPL", "' + ([DateTime]$salesOrder.orderDate).ToString("yyyy-MM-dd") + '","' + $QBIncomeAccount.Replace('"', '""') + '","' + $salesOrder.billingFirstName.Replace('"', '""') + ' ' + $salesOrder.billingLastName.Replace('"', '""') + '","' + (-([Decimal]$salesOrder.totalAmount)) + '",""') >> $OutFile
        
        ('"ENDTRNS"') >> $OutFile
    }
}
Catch
{
    Write-Output $_.Exception.Message
}

Comments


Powered by Revindex Wiki