Xero - beautiful accounting software

Xero Developer Help Center

Xero Developer Community

Community > API Endpoints >

Reports/BankStatement endpoint : parsing the result?

Started by John Milton -   in API Endpoints

Hi

New to the Xero API, reasonably experienced with PHP, JSON etc

Modified the xero.php library to access the Reports/BankStatement endpoint and successfully receive JSON data back.

I've found this a bit of a beast to parse :-)

Using the XML schema and walking through the results I have PHP that looks like this :

$statementArr = $result['Reports']['Report']['Rows']['Row'][1]['Rows']['Row'];

to get to an array of statement "lines" to iterate over (and then drill to ['Cells']['Cell'] to finally get to an array of the values).

My question : this works - but am I missing something? Such as an easier way to approach the parsing!

I normally work in JSON (spending most my time in Javascript) but can deal with XML if it makes the parsing easier...

Thanks for any advice.

John
@John - thanks for your feedback. We're looking into improving this so the XML/JSON is returned in a much friendlier format for developers rather than the Row and Column approach. I appreciate the current way you extract values is hard slog!

We will get to this work but unfortunately isn't on our high priority radar right at this moment. You can keep track of our progress on this over on Uservoice
 

Tony Rule (Xero Staff)  

Hi
I am new to Xero API.
John I am using C#, can you show me how you managed to get to the values in Reports/ BankStatement.

Thanks
Charles
 

Charles van Niekerk  

Hi Charles

Do you mean how to retrieve the Report, or how to get to the data within the report?

I'm using PHP but there's C# info here if you haven't seen it already :

http://blog.xero.com/developer/getting-started/code/using-the-xero-api-with-c/

I never finished implementing this but can share what PHP code I have if that's any help!
Regards
 

John Milton  

Hi John
I want to get the data within the report. I would like it if you share the code.
Regards
 

Charles van Niekerk  

Hi Charles sorry for delay in replying.

I never got this beyond proof of concept, but here's what I came up with (and prompted my original post). PHP code (simplified) :

//Retrieve the Bank Statement
$result = $xero->Reports('BankStatement', $account['AccountID'], '10 Jan 2012', '26 Jan 2012' );
//Extract the statement 'lines'
$statementArr = $result['Reports']['Report']['Rows']['Row'][1]['Rows']['Row'];
//Loop through the statement lines
foreach ($statementArr as $lineArr){
$line = $lineArr['Cells']['Cell'];
// Extract 'Date', 'Description', 'Reference', 'Reconciled', 'Source', 'Amount', 'Balance'
for ($i = 0; $i <= 6; $i++) {
$value = $line["$i"]['Value'];
//Do something with $value...
}
}

I've not retested this so sorry if it contains any typos. I arrived at this by experimenting with the report (JSON) and referring to the schemas :

https://github.com/XeroAPI/XeroAPI-Schemas/

To be honest I find this sort of code unworkable and unmaintainable, so I stopped work on it as it wasn't a critical requirement :-(

HTH!

John

 

John Milton  

Hi John
Don't worry the about the delay but thanks for replying and for sharing your code.
Enjoy your day
Charles
 

Charles van Niekerk  

Good Morning,
I am parsing the BankStatement and BankSummary and I can get the values fine but bit worried that any change to the fomat/order of the columns and my code will collapse. Is this structure completely static.

For instance my code assumes I can get the closing balance from Cell 7, but this feels very hacky!

{code}

Report report = response.getReports().get(0);
LOGGER.info("Bank Statement = " + report.toString());
for (Row row : report.getRows()) {
if (StringUtils.equalsIgnoreCase("Section", row.getRowType())) {
Row_ closingBalRow = row.getRows().get(1);
String closingBalString = closingBalRow.getCells().get(6).getValue();
LOGGER.info("closing balance string = " + closingBalString);

{code}

It works fine as is, but worried any change to the Report would cause it to fail (I am defensively codign so not a disaster).

Cheers

Mat
 

Mathew Broughton  

Seven years on and AFAICS this has not been improved. :(
 

David Rose