# Convert a QuickBooks QBXML Report to HTML/CSV File # I created this little script to help me read through the raw XML responses from QuickBooks. I originally built it to export to a CSV file, but found that a simple HTML table was better suited for my usage. However, I've included the CSV code below for anyone who may need that functionality. …

convert.php

<?php

/**
 * Convert a QuickBooks QBXML Report to HTML/CSV File
 *
 * I created this little script to help me read through the raw XML responses
 * from QuickBooks. I originally built it to export to a CSV file, but found
 * that a simple HTML table was better suited for my usage. However, I've
 * included the CSV code below for anyone who may need that functionality.
 *
 * Hopefully it helps others dealing with QuickBook's excruciatingly long and
 * verbose XML reports. (Why couldn't they just support JSON?!?!)
 *
 * @link https://gist.github.com/4448576
 * @author Jay Williams <http://myd3.com/>
 * @copyright Copyright (c) 2013, Jay Williams
 * @license http://www.opensource.org/licenses/mit-license.php MIT License
 */

$file = 'example.xml';

// Load XML file
$report = new SimpleXMLElement($file, 0, true);

// Get Column Descriptions
$ColDesc = $report->xpath('//ColDesc');

// Save the results in an array
$columns = array();
foreach ($ColDesc as $col) {
    $columns[(int) $col->attributes()->colID] = (string) $col->ColType;
}

// To make sure each row has the name number of columns
// we have to get the first and last column ids and create
// a blank array to use as a starting point for each row.

// Get Column Starting Number
reset($columns);
$column_start = key($columns);

// Get Column Ending Number
end($columns);
$column_end = key($columns);

$default_row = array_fill($column_start, $column_end, null);

// Get Report Data
$ReportData = $report->xpath('//ReportData/*');

// Save the results in an array
$rows = array();
foreach ($ReportData as $row) {
    $rowNumber = (int) $row->attributes()->rowNumber;
    $rows[$rowNumber] = $default_row;

    if ($row->count()) {
        foreach ($row->children() as $col) {
            $colID = (int) $col->attributes()->colID;
            if($colID < 1)
                $rows[$rowNumber][1] = (string) $col->attributes()->value;
            else
                $rows[$rowNumber][$colID] = (string) $col->attributes()->value;
        }
    } else {
        $rows[$rowNumber][1] = (string) $row->attributes()->value;
    }
}

// Output arrays
//var_dump($columns, $rows);

// Output XML object
//var_dump($report);

// Output HTML Table
echo "<table>\n";

    echo "<tr>\n";
    foreach ($columns as $column) {
    echo "<th>$column</th>\n";
}
echo "</tr>\n";

foreach ($rows as $row) {
    echo "<tr>\n";
    foreach ($row as $colID => $column) {
        if($colID == 1)
            echo "<th>$column</th>\n";
        else
            echo "<td>$column</td>\n";
    }
    echo "</tr>\n";
}

echo "</table>";


/*
// Output CSV File
$fp = fopen('output.csv', 'w');

fputcsv($fp, $columns);

foreach ($rows as $row) {
    fputcsv($fp, $row);
}

fclose($fp);
*/

example.xml

<?xml version="1.0"?>
<QBXML>
	<QBXMLMsgsRs>
		<GeneralDetailReportQueryRs requestID="2388" statusCode="0" statusSeverity="Info" statusMessage="Status OK">
			<ReportRet>
				<ReportTitle>Sample Data</ReportTitle>
				<ReportSubtitle>Some random data used as an example</ReportSubtitle>
				<ReportBasis>Accrual</ReportBasis>
				<NumRows>14</NumRows>
				<NumColumns>7</NumColumns>
				<NumColTitleRows>1</NumColTitleRows>
				<ColDesc colID="1" dataType="STRTYPE">
					<ColTitle titleRow="1"/>
					<ColType>Blank</ColType>
				</ColDesc>
				<ColDesc colID="2" dataType="STRTYPE">
					<ColTitle titleRow="1" value="Num"/>
					<ColType>RefNumber</ColType>
				</ColDesc>
				<ColDesc colID="3" dataType="STRTYPE">
					<ColTitle titleRow="1" value="State"/>
					<ColType>LatestOrPriorState</ColType>
				</ColDesc>
				<ColDesc colID="4" dataType="STRTYPE">
					<ColTitle titleRow="1" value="Memo"/>
					<ColType>Memo</ColType>
				</ColDesc>
				<ColDesc colID="5" dataType="STRTYPE">
					<ColTitle titleRow="1" value="Item"/>
					<ColType>Item</ColType>
				</ColDesc>
				<ColDesc colID="6" dataType="STRTYPE">
					<ColTitle titleRow="1" value="Type"/>
					<ColType>TxnType</ColType>
				</ColDesc>
				<ColDesc colID="7" dataType="INTTYPE">
					<ColTitle titleRow="1" value="Trans #"/>
					<ColType>TxnNumber</ColType>
				</ColDesc>
				<ReportData>
					<TextRow rowNumber="1" value="Transactions entered or modified by User"/>
					<TextRow rowNumber="2" value="Bill"/>
					<DataRow rowNumber="3">
						<ColData colID="3" value="Latest"/>
						<ColData colID="6" value="Bill"/>
						<ColData colID="7" value="123456"/>
					</DataRow>
					<DataRow rowNumber="4">
						<ColData colID="4" value="Some Crazy Product"/>
						<ColData colID="5" value="XX-1234"/>
					</DataRow>
					<DataRow rowNumber="5">
						<ColData colID="4" value="Some Other Crazy Product"/>
						<ColData colID="5" value="XX-1235"/>
					</DataRow>
					<TextRow rowNumber="6"/>
					<TextRow rowNumber="7" value="Transactions entered or modified by SomeOtherUser"/>
					<TextRow rowNumber="8" value="Build Assembly 123"/>
					<DataRow rowNumber="9">
						<ColData colID="2" value="123"/>
						<ColData colID="3" value="Latest"/>
						<ColData colID="5" value="XX-456"/>
						<ColData colID="6" value="Build Assembly"/>
						<ColData colID="7" value="123456"/>
					</DataRow>
					<DataRow rowNumber="10">
						<ColData colID="4" value="Random Product"/>
						<ColData colID="5" value="AB-1234"/>
					</DataRow>
					<DataRow rowNumber="11">
						<ColData colID="4" value="Lots of Cool Things"/>
						<ColData colID="5" value="AB-1246"/>
					</DataRow>
					<DataRow rowNumber="12">
						<ColData colID="4" value="More Random Stuff"/>
						<ColData colID="5" value="XC-1234"/>
					</DataRow>
					<TextRow rowNumber="13"/>
					<TotalRow rowNumber="14"/>
				</ReportData>
			</ReportRet>
		</GeneralDetailReportQueryRs>
	</QBXMLMsgsRs>
</QBXML>

output.html

<table>
<tr>
<th>Blank</th>
<th>RefNumber</th>
<th>LatestOrPriorState</th>
<th>Memo</th>
<th>Item</th>
<th>TxnType</th>
<th>TxnNumber</th>
</tr>
<tr>
<th>Transactions entered or modified by User</th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th>Bill</th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td>Latest</td>
<td></td>
<td></td>
<td>Bill</td>
<td>123456</td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td>Some Crazy Product</td>
<td>XX-1234</td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td>Some Other Crazy Product</td>
<td>XX-1235</td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th>Transactions entered or modified by SomeOtherUser</th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th>Build Assembly 123</th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td>123</td>
<td>Latest</td>
<td></td>
<td>XX-456</td>
<td>Build Assembly</td>
<td>123456</td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td>Random Product</td>
<td>AB-1234</td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td>Lots of Cool Things</td>
<td>AB-1246</td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td>More Random Stuff</td>
<td>XC-1234</td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<th></th>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>