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
}