You can use the following Powershell script to export your customers information to an IIF file suitable for importing into your QuickBooks software.
# QuickBooksCustomerExport.ps1
#
# This script will export all customer information
# to a QuickBooks IIF file.
######################################################################
# Configuration
######################################################################
param
(
[parameter(Mandatory = $false)][string]$APIKey = 'xxxxx-xxxxx',
[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:\Customers.iif',
[parameter(Mandatory = $false)][DateTime]$StartDate = '2001-01-01',
[parameter(Mandatory = $false)][DateTime]$StopDate = [DateTime]::Now,
[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
{
# 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')
$userIDs = @()
$qbIIF = @()
foreach ($salesOrder in $salesOrders.SelectNodes('salesOrder'))
{
# Only export unique users
if ($userIDs -contains $salesOrder.userID)
{
continue
}
else
{
$userIDs += $salesOrder.userID
}
# Append data to CSV (IIF Format)
# http://support.quickbooks.intuit.com/support/Articles/HOW12778
# http://www.qblittlesquare.com/2011/07/import-lists-into-quickbooks-with-iif/
$qbIIF += New-Object -TypeName PSObject -Property @{
"!CUST" = "CUST"
"NAME" = $salesOrder.billingLastName +", " + $salesOrder.billingFirstName
"BADDR1" = $salesOrder.billingStreet.Replace("`r", "").Replace("`n", ", ")
"BADDR2" = $salesOrder.billingCity
"BADDR3" = $salesOrder.billingSubdivisionCode
"BADDR4" = $salesOrder.billingCountryCode
"BADDR5" = $salesOrder.billingPostalCode
"SADDR1" = $salesOrder.shippingStreet.Replace("`r", "").Replace("`n", ", ")
"SADDR2" = $salesOrder.shippingCity
"SADDR3" = $salesOrder.shippingSubdivisionCode
"SADDR4" = $salesOrder.shippingCountryCode
"SADDR5" = $salesOrder.shippingPostalCode
"PHONE1" = $salesOrder.billingPhone
"PHONE2" = ''
"FAXNUM" = ''
"EMAIL" = $salesOrder.billingEmail
"CONT1" = ''
"CONT2" = ''
"CTYPE" = 'Residential'
"TERMS" = ''
"TAXABLE" = 'Y'
"LIMIT" = ''
"RESALENUM" = ''
"REP" = ''
"TAXITEM" = ''
"NOTEPAD" = ''
"SALUTATION" = ''
"COMPANYNAME" = $salesOrder.billingCompany
"FIRSTNAME" = $salesOrder.billingFirstName
"MIDINIT" = ''
"LASTNAME" = $salesOrder.billingLastName
"CUSTFLD1" = ''
"CUSTFLD2" = ''
"CUSTFLD3" = ''
"CUSTFLD4" = ''
"CUSTFLD5" = ''
"CUSTFLD6" = ''
"CUSTFLD7" = ''
"CUSTFLD8" = ''
"CUSTFLD9" = ''
"CUSTFLD10" = ''
"CUSTFLD11" = ''
"CUSTFLD12" = ''
"CUSTFLD13" = ''
"CUSTFLD14" = ''
"CUSTFLD15" = ''
}
}
# Persist fulfillment to file
# Create CSV with headers and append data
$qbIIF | Select-Object "!CUST",
"NAME",
"BADDR1",
"BADDR2",
"BADDR3",
"BADDR4",
"BADDR5",
"SADDR1",
"SADDR2",
"SADDR3",
"SADDR4",
"SADDR5",
"PHONE1",
"PHONE2",
"FAXNUM",
"EMAIL",
"CONT1",
"CONT2",
"CTYPE",
"TERMS",
"TAXABLE",
"LIMIT",
"RESALENUM",
"REP",
"TAXITEM",
"NOTEPAD",
"SALUTATION",
"COMPANYNAME",
"FIRSTNAME",
"MIDINIT",
"LASTNAME",
"CUSTFLD1",
"CUSTFLD2",
"CUSTFLD3",
"CUSTFLD4",
"CUSTFLD5",
"CUSTFLD6",
"CUSTFLD7",
"CUSTFLD8",
"CUSTFLD9",
"CUSTFLD10",
"CUSTFLD11",
"CUSTFLD12",
"CUSTFLD13",
"CUSTFLD14",
"CUSTFLD15" | Export-Csv -NoTypeInformation $OutFile
}
Catch
{
Write-Output $_.Exception.Message
}