Ecwid Order export using RestAPI , PHP, CURL, JSON

reldridge · January 11, 2016, 2:14pm

Using Ecwid API I am trying to get an export of my new orders to csv.

Using the below to pull the json and start the export works until I get to some of the more indepth nested json objects and my spreadsheet starts to show “Array” in the field columns.

<?php
# An HTTP GET request example

$url = 'https://app.ecwid.com/api/v3/{STOREID}/orders?paymentStatus=PAID&fulfillmentStatus=AWAITING_PROCESSING&token={TOKEN}';

$ch = curl_init($url);
curl_setopt($ch, CURLOPT_TIMEOUT, 5);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($ch);
curl_close($ch);

echo $data;

$json_obj = json_decode($data, true);
$fp = fopen('hardmerch_orders.csv', 'a');
    foreach ($json_obj['items'] as $items) {
         fputcsv($fp, $items);
    }
fclose($fp);

?>

I am trying to figure out how to buld the array for all the items in the json export to correctly get the data to csv. I have started using a foreach loop to build the array but I am getting no data to my spreadsheet now.

Sample script:

<?php
# An HTTP GET request example

$url = 'https://app.ecwid.com/api/v3/{STOREID}/orders?paymentStatus=PAID&fulfillmentStatus=AWAITING_PROCESSING&token={TOKEN}';

$ch = curl_init($url);
curl_setopt($ch, CURLOPT_TIMEOUT, 5);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($ch);
curl_close($ch);

// echo $data;

$json_obj = json_decode($data, true);

//Ecwid Shopping Cart (API)  - GET Orders
//http://developers.ecwid.com/api-documentation#orders

$output = array();
foreach ($json_obj['items'] as $item) {
    $output['total'] = $item['total'];
    $output['count'] = $item['count'];
	$output['offset'] = $item['offset'];
	$output['limit'] = $item['limit'];
	$output['items'] = $item['items'];

    $output['vendorOrderNumber'] = $item['items'][0]['vendorOrderNumber'];
	$output['subtotal'] = $item['items'][0]['subtotal'];
	$output['total'] = $item['items'][0]['total'];
	$output['email'] = $item['items'][0]['email'];
	$output['externalTransactionId'] = $item['items'][0]['externalTransactionId'];
	$output['paymentModule'] = $item['items'][0]['paymentModule'];
	$output['paymentMethod'] = $item['items'][0]['paymentMethod'];
	$output['tax'] = $item['items'][0]['tax'];
	$output['ipAddress'] = $item['items'][0]['ipAddress'];
	$output['couponDiscount'] = $item['items'][0]['couponDiscount'];
	$output['paymentStatus'] = $item['items'][0]['paymentStatus'];
	$output['paymentMessage'] = $item['items'][0]['paymentMessage'];
	$output['fulfillmentStatus'] = $item['items'][0]['fulfillmentStatus'];
	$output['orderNumber'] = $item['items'][0]['orderNumber'];
	$output['refererUrl'] = $item['items'][0]['refererUrl'];
	$output['orderComments'] = $item['items'][0]['orderComments'];
	$output['volumeDiscount'] = $item['items'][0]['volumeDiscount'];
	$output['customerId'] = $item['items'][0]['customerId'];
	$output['membershipBasedDiscount'] = $item['items'][0]['membershipBasedDiscount'];
	$output['totalAndMembershipBasedDiscount'] = $item['items'][0]['totalAndMembershipBasedDiscount'];
	$output['discount'] = $item['items'][0]['discount'];
	$output['usdTotal'] = $item['items'][0]['usdTotal'];
	$output['globalReferer'] = $item['items'][0]['globalReferer'];
	$output['createDate'] = $item['items'][0]['createDate'];
	$output['updateDate'] = $item['items'][0]['updateDate'];
	$output['createTimestamp'] = $item['items'][0]['createTimestamp'];
	$output['updateTimestamp'] = $item['items'][0]['updateTimestamp'];
	$output['customerGroupId'] = $item['items'][0]['customerGroupId'];
	$output['customerGroup'] = $item['items'][0]['customerGroup'];
	
	//['discountCoupon']
		$output['name'] = $item['discountCoupon'][0]['name'];
		$output['code'] = $item['discountCoupon'][0]['code'];
		$output['discountType'] = $item['discountCoupon'][0]['discountType'];
		$output['status'] = $item['discountCoupon'][0]['status'];
		$output['discount'] = $item['discountCoupon'][0]['discount'];
		$output['launchDate'] = $item['discountCoupon'][0]['launchDate'];
		$output['usesLimit'] = $item['discountCoupon'][0]['usesLimit'];
		$output['repeatCustomerOnly'] = $item['discountCoupon'][0]['repeatCustomerOnly'];
		$output['creationDate'] = $item['discountCoupon'][0]['creationDate'];
		$output['orderCount'] = $item['discountCoupon'][0]['orderCount'];
		
	//['items']	
		$output['id'] = $item['items'][0]['id'];
		$output['productId'] = $item['items'][0]['productId'];
		$output['categoryId'] = $item['items'][0]['categoryId'];
		$output['price'] = $item['items'][0]['price'];
		$output['productPrice'] = $item['items'][0]['productPrice'];
		$output['weight'] = $item['items'][0]['weight'];
		$output['sku'] = $item['items'][0]['sku'];
		$output['quantity'] = $item['items'][0]['quantity'];
		$output['shortDescription'] = $item['items'][0]['shortDescription'];
		$output['tax'] = $item['items'][0]['tax'];
		$output['shipping'] = $item['items'][0]['shipping'];
		$output['quantityInStock'] = $item['items'][0]['quantityInStock'];
		$output['name'] = $item['items'][0]['name'];
		$output['tangible'] = $item['items'][0]['tangible'];
		$output['trackQuantity'] = $item['items'][0]['trackQuantity'];
		$output['fixedShippingRateOnly'] = $item['items'][0]['fixedShippingRateOnly'];
		$output['imageUrl'] = $item['items'][0]['imageUrl'];
		$output['fixedShippingRate'] = $item['items'][0]['fixedShippingRate'];
		$output['digital'] = $item['items'][0]['digital'];
		$output['productAvailable'] = $item['items'][0]['productAvailable'];
		$output['couponApplied'] = $item['items'][0]['couponApplied'];
		
		//['files']
			$output['productFileId'] = $item['files'][0]['productFileId'];
			$output['maxDownloads'] = $item['files'][0]['maxDownloads'];
			$output['remainingDownloads'] = $item['files'][0]['remainingDownloads'];
			$output['expire'] = $item['files'][0]['expire'];
			$output['name'] = $item['files'][0]['name'];
			$output['description'] = $item['files'][0]['description'];
			$output['size'] = $item['files'][0]['size'];
			$output['adminUrl'] = $item['files'][0]['adminUrl'];
			$output['customerUrl'] = $item['files'][0]['customerUrl'];
			
		//['selectedOptions']
			$output['name'] = $item['selectedOptions'][0]['name'];
			$output['value'] = $item['selectedOptions'][0]['value'];
			$output['valuesArray'] = $item['selectedOptions'][0]['valuesArray'];
			$output['Big'] = $item['selectedOptions'][0]['Big'];
			$output['type'] = $item['selectedOptions'][0]['type'];
			  // THERE ARE MORE SELECTED OPTIONS BUT I AM LOST AT HOW THEY SHOULD BE ADDED TO THE ARRAY.
			  
			//['taxes']
				$output['name'] = $item['taxes'][0]['name'];
				$output['value'] = $item['taxes'][0]['value'];
				$output['total'] = $item['taxes'][0]['total'];
				$output['taxOnDiscountedSubtotal'] = $item['taxes'][0]['taxOnDiscountedSubtotal'];
				$output['taxOnShipping'] = $item['taxes'][0]['taxOnShipping'];
				
		//['billingPerson']
			$output['name'] = $item['billingPerson'][0]['name'];
			$output['companyName'] = $item['billingPerson'][0]['companyName'];
			$output['street'] = $item['billingPerson'][0]['street'];
			$output['city'] = $item['billingPerson'][0]['city'];
			$output['countryCode'] = $item['billingPerson'][0]['countryCode'];
			$output['countryName'] = $item['billingPerson'][0]['countryName'];
			$output['postalCode'] = $item['billingPerson'][0]['postalCode'];
			$output['stateOrProvinceCode'] = $item['billingPerson'][0]['stateOrProvinceCode'];
			$output['stateOrProvinceName'] = $item['billingPerson'][0]['stateOrProvinceName'];
			$output['phone'] = $item['billingPerson'][0]['phone'];
			
		//['shippingPerson']
			$output['name'] = $item['shippingPerson'][0]['name'];
			$output['companyName'] = $item['shippingPerson'][0]['companyName'];
			$output['street'] = $item['shippingPerson'][0]['street'];
			$output['city'] = $item['shippingPerson'][0]['city'];
			$output['countryCode'] = $item['shippingPerson'][0]['countryCode'];
			$output['countryName'] = $item['shippingPerson'][0]['countryName'];
			$output['postalCode'] = $item['shippingPerson'][0]['postalCode'];
			$output['stateOrProvinceCode'] = $item['shippingPerson'][0]['stateOrProvinceCode'];
			$output['stateOrProvinceName'] = $item['shippingPerson'][0]['stateOrProvinceName'];
			$output['phone'] = $item['shippingPerson'][0]['phone'];
			
		//['shippingOption']
			$output['shippingMethodName'] = $item['shippingOption'][0]['shippingMethodName'];
			$output['shippingRate'] = $item['shippingOption'][0]['shippingRate'];
			$output['estimatedTransitTime'] = $item['shippingOption'][0]['estimatedTransitTime'];
			
		//['handlingFee']
			$output['name'] = $item['handlingFee'][0]['name'];
			$output['value'] = $item['handlingFee'][0]['value'];
			$output['description'] = $item['handlingFee'][0]['description'];
			
		//['paymentParams']
			$output['Company name'] = $item['paymentParams'][0]['Company name'];
			$output['Job position'] = $item['paymentParams'][0]['Job position'];
			$output['PO number'] = $item['paymentParams'][0]['PO number'];
			//$output['Buyer's full name'] = $item['paymentParams'][0]['Buyer's full name'];
			
		//['discountInfo']
			$output['value'] = $item['discountInfo'][0]['value'];
			$output['type'] = $item['discountInfo'][0]['type'];
			$output['base'] = $item['discountInfo'][0]['base'];
			$output['orderTotal'] = $item['discountInfo'][0]['orderTotal'];
			
		$output['hidden'] = $item['hidden'];
}	

echo $output;

$fp = fopen('hardmerch_orders.csv', 'a');
foreach ($output as $line) {
    fputcsv($fp, $items);
}
fclose($fp);

?>

Json data:

{"total":1,"count":1,"offset":0,"limit":100,"items":[{"vendorOrderNumber":"0137064","subtotal":44,"total":78.3,"email":"adrianlim07@hotmail.com","externalTransactionId":"51608869UC712171W","paymentModule":"PayPalStandard","paymentMethod":"PayPal / Credit Card","tax":0,"ipAddress":"110.92.125.18","couponDiscount":0,"paymentStatus":"PAID","paymentMessage":"Your order has been approved","fulfillmentStatus":"AWAITING_PROCESSING","orderNumber":7064,"refererUrl":"http://fvhardmerchandise.com/shop.html","volumeDiscount":0,"customerId":24249682,"membershipBasedDiscount":0,"totalAndMembershipBasedDiscount":0,"discount":0,"usdTotal":78.3,"globalReferer":"https://www.facebook.com/","createDate":"2016-01-11 08:29:32 +0000","updateDate":"2016-01-11 08:31:00 +0000","createTimestamp":1452500972,"updateTimestamp":1452501060,"items":[{"id":69590723,"productId":9673066,"categoryId":2455976,"price":22,"productPrice":22,"sku":"00003","quantity":2,"shortDescription":"The original. Worn by Captain Marciano and his First Mate Jay from the very first season of Wicked Tuna , and from the ...","tax":0,"shipping":34.3,"quantityInStock":10,"name":"Black T-Shirt","isShippingRequired":true,"weight":1.5,"trackQuantity":true,"fixedShippingRateOnly":false,"imageUrl":"https://dpbfm6h358sh7.cloudfront.net/images/1039296/36454966.jpg","smallThumbnailUrl":"https://dpbfm6h358sh7.cloudfront.net/images/1039296/36454968.jpg","fixedShippingRate":0,"digital":false,"productAvailable":true,"couponApplied":false,"selectedOptions":[{"name":"Size","value":"Medium","valuesArray":["Medium"],"type":"CHOICE"}]}],"billingPerson":{"name":"Adrian Lim","companyName":"","street":"Blk 330A, Anchorvale street #10-517","city":"Singapore","countryCode":"SG","countryName":"Singapore","postalCode":"541330","stateOrProvinceCode":"Singapore","stateOrProvinceName":"Singapore","phone":"+65 91698222"},"shippingPerson":{"name":"Adrian Lim","companyName":"","street":"Blk 330A, Anchorvale street #10-517","city":"Singapore","countryCode":"SG","countryName":"Singapore","postalCode":"541330","stateOrProvinceCode":"Singapore","stateOrProvinceName":"Singapore","phone":"+65 91698222"},"shippingOption":{"shippingCarrierName":"U.S.P.S.","shippingMethodName":"U.S.P.S. First-Class Package International Service™","shippingRate":34.3},"handlingFee":{"name":"Handling Fee","value":0,"description":""},"additionalInfo":{},"paymentParams":{},"hidden":false}]}

s_molinari · January 11, 2016, 3:04pm

Have a look at this.

gist.github.com

https://gist.github.com/Kostanos/5641110

json-to-csv.php

#!/usr/bin/php
<?php
/*
* Convert JSON file to CSV and output it.
*
* JSON should be an array of objects, dictionaries with simple data structure
* and the same keys in each object.
* The order of keys it took from the first element.
*
* Example:

This file has been truncated. show original

Scott

oddz · January 11, 2016, 5:53pm

If the value is an array you could use a bracket character to separate each key/value pair. That would work so long as none of the keys or values contain a bracket for the root key.

Another approach would be to allocate multiple columns for the multi vale keys. For example it seems reasonable that product options might have no more than 10 values. So you could designate 20 virtual columns to potentially spread that data across.

system · April 12, 2016, 12:55am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.