Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
# 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. …
<?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);
*/
<?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>
<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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.