Export transactions

chrisLchrisL Member
in Help edited November 2008
Hi -

Question about the "export transactions" feature...

I exported about 100 orders today as a test and I noticed that data captured in "product_options" is not exported consistently -- The data is not always in the same sequence as it appears when viewing it online.

For example, two different "product_options" from different transactions display the same in foxycart, but when exported they are re-ordered and look like this:
First_Name:Austyn - Gender:Girl - Town:Joplin
Gender:Boy - First_Name:Paul - Town:Carroll

I'm trying to parse this data point to import into another database, but the random ordering is making it challenging.

Is there a way to get this data broken out on the export, or fix it so it will always appear in the same order it was entered?
Comments
  • AdamWintleAdamWintle Member
    edited November 2008
    I've never had a problem with the Export Transactions feature, which method did you use to a CVS File or Tab Delimited? - I exported them as Tab Delimited then opened that file straight into Excel, sorted out the weird column widths and the file is usable...

    Although on an export of 185 transactions two of them have some weird results in the "product_options" field: "
    size:3 - submit3_x:86 - submit3_y:18
    
    "

    Do you know if there's a day to just export the transitions so it combines each customers orders into one row, instead of creating a new row for each item from that customer...
  • lukeluke FoxyCart Team
    We probably need to take a look at that. At one point we switched the sorting on those options and it may not have been changed in both places. Should be a small change. We'll update this thread when we get a chance to take a look at it.
  • brettbrett FoxyCart Team
    The submit_x and _y can show up sometimes if you're using an image map, fwiw.

    If you're going from an export into another database it should (theoretically) make more sense to use the XML rather than the CSV. Is that an option? Check out the script to save the XML to a file on your server: http://wiki.foxycart.com/integration
  • @mallmus - Yes, thanks. The file is certainly formatted correctly. It just orders the values that are stuffed in the product_options in different sequences from row to row.

    @brett - That says that this is for testing only? Can you pull XML in bulk on demand? Do you have a script that could load the xml data feed into a mysql database? I'm not experienced enough to sort that out, but that would make my life easier!
  • brettbrett FoxyCart Team
    There's a MySQL script that'll just dump the raw XML into a MySQL db, but it won't arrange them at all.

    The script that says it's for testing is (truly) for testing, but so long as you only leave it up right when you're playing with it you should be ok. The issue is with writing sensitive customer information to a file that's publicly accessible. (You could conceivably write it outside your webroot, which would obviously be good.)

    As far as pulling XML in bulk on demand: kind of. If you have the datafeed turned on in your FC admin it will attempt to "feed" every transaction until your script responds "foxy", at which point it will be marked "fed" and will not be sent again. So if you haven't turned on your datafeed, on the first run it'll attempt to send you all your previous transactions. At that point you should probably turn off the datafeed until you want to run another batch.

    As far as loading it into a MySQL db, the issue is really what you need to do with it. If you're on a PHP5 server (or if you're running another language like RoR or Python or etc.) you should be able to handle XML quite easily, at which point it's just a matter of looping through the XML and putting the appropriate XML values into the right db fields. It sounds a lot harder than it actually is.

    Any of that help?
  • Luke - Have you had a chance to look at how the product_options are sequenced?

    brett - thanks again. I got the datafeed working and downloaded a batch of my data as a test. I've tried parsing out the product_options, but it is beyond my skills. Here's a better description of what I'm trying to do (fixing the order of the product_options would solve it, too):

    For each transaction I need to pull out all of the product options for each item ordered. The product options are the same for each order. So, in an exported CSV file, the data might look something like this...
    id, product_option_a, product_option_b, product_option_c, product_option_d
    133347, Sam, Smith, Nixa, MO
    133347, Jane, Deux, St. Louis, MO
    133350, Brad, Hess, Denver, CO
    133356, Dalton, Johnson, Reston, VA
    133356, Dudley, Johnson, Reston, VA
    

    These are products that we are personalizing and we send the personalization info in a batch to a supplier to create. This would be a once-a-year process for me and not something I necessarily have to get fancy with... Getting the ordering of product_options to be consistent in the CSV export would solve my problem -- as long as they are in the same order I can do some creative search/replace to get them into separate columns in the CSV.
  • lukeluke FoxyCart Team
    Sorry Chris, we've been working on some other priorities right now. The datafeed is really the best way to work with this since it's a versioned system and future changes won't break what you're trying to do. We can't guarantee any automated process built off of the CSV format since that will probably change quite a bit over time as we add new features (such as tax line items, coupon discounts, etc). It's been a while since we updated it and it will be changing once we do.

    As for parsing the data in the XML, it should be a simple loop over the transaction_detail_options. A lot of the integration examples should do something similar already.

    If you have PHP5, it should be something simple like this:
    if (isset($_POST["FoxyData"])) {
    	$FoxyData_encrypted = urldecode($_POST["FoxyData"]);
    	$FoxyData_decrypted = rc4crypt::decrypt($DataFeedKey,$FoxyData_encrypted);
    	$FoxyDataArray = new SimpleXMLElement($FoxyData_decrypted);
    	foreach($FoxyDataArray->transactions->transaction AS $transaction) {
    		foreach($transaction->transaction_details->transaction_detail AS $transaction_detail) {
    			foreach($transaction_detail->transaction_detail_options->transaction_detail_option AS $transaction_detail_option) {
    				if ($transaction_detail_option->product_option_name == "my_cool_variable") {
    
    					$my_cool_variable_value = $transaction_detail_option->product_option_value;
    
    				}
    			}
    		}
    	}
    }
    print "foxy";
    

    From within the different for loops, you can write to your database or fire off emails or whatever it is you need to accomplish and it can be done automatically without having to baby sit the process at all.

    We'll get to updating the CSV format soon, but again, please do not rely on that format as it will be changing over time and then we'll be the bad guys once it breaks. We don't like being the bad guys. :)
  • lukeluke FoxyCart Team
    btw, we updated the sort order in the export so it matches the cart display... but we still suggest you use the XML datafeed instead of the export for any automated process as this file format will change overtime with no warning. The datafeed file format is versioned, the CSV/Tab Export file is not.
Sign In or Register to comment.