Jump to content



Photo
- - - - -

Export orders into csv


  • Please log in to reply
103 replies to this topic

#1   dlan

dlan
  • Members
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 04 September 2007 - 10:05

Hello All,
Some of you are contacting me regarding the module with some problems to get the file. I then create this topic so all the community can help if someone else already had a problem related and managed to find a solution.

David

#2   hmiranda

hmiranda
  • Members
  • 4 posts
  • Real Name:Holly

Posted 04 September 2007 - 19:52

Do you know how to export orders? I can't figure it out? I posted a question but maybe I didn't ask the right one. I am using a drop shipping company that I need to download my orders to my computer and then send the file to them.

It has to contain all the customer's information in addition to the order details. Am I in the right spot to find out information on how to do that?

Any help is appreciated!! Thanks! /rolleyes.gif' class='bbc_emoticon' alt=':rolleyes:' />

#3   gregp

gregp
  • Members
  • 61 posts
  • Real Name:Greg

Posted 05 October 2007 - 12:11

Just posted a minor fix: in customers.php, the text

'<a href="' . tep_href_link(FILENAME_EXPORTORDERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_ORDERS . '</a>');

should be

'<a href="' . tep_href_link(FILENAME_EXPORTORDERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_EXPORTORDERS . '</a>');



Fixes a small error in /catalog/admin/includes/boxes/customers.php which causes the word 'Orders' to be shown twice in the Customers/Orders panel, with the bottom one activating the Export Orders function.

Replace

// BOF Export Orders to CSV
$contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_CUSTOMERS . '</a><br>' .
'<a href="' . tep_href_link(FILENAME_ORDERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_ORDERS . '</a><br>' .
'<a href="' . tep_href_link(FILENAME_EXPORTORDERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_ORDERS . '</a>');
// EOF Export Orders to CSV

with

// BOF Export Orders to CSV
$contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_CUSTOMERS . '</a><br>' .
'<a href="' . tep_href_link(FILENAME_ORDERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_ORDERS . '</a><br>' .
'<a href="' . tep_href_link(FILENAME_EXPORTORDERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_CUSTOMERS_EXPORTORDERS . '</a>');
// EOF Export Orders to CSV

Nice contribution dlan!

#4   simplytaty

simplytaty
  • Members
  • 54 posts
  • Real Name:Someone Here
  • Gender:Female
  • Location:NYC

Posted 08 October 2007 - 04:58

Hi Dlan
Thank you so much, great contribuition. One question, actually two. I need to adf the type of shipping (expedited or standard) and total of the order to the download. Any resources on how to do that?
Thanks a bunch.

#5   oxfamire

oxfamire
  • Members
  • 9 posts
  • Real Name:Oxfam Ireland Webmaster

Posted 25 October 2007 - 15:39

Hi Dlan

Thanks for the contribution, it's great, I just implemented it on our shop.

One question - is there a quick way to include the field names/table headers as part of the csv file?

Also, is there any way to add a currency option - so you can view the results in multiple currencies, or choose one currency to view all results in.

Thanks again - this will be a really useful contrib for us in months to come.

#6   Dragonmom

Dragonmom
  • Members
  • 367 posts
  • Real Name:Shawn
  • Location:Chicago

Posted 10 November 2007 - 05:49

I have implemented the contrib, and I get all of the buyer's information but none of the items that they've purchased. And that's what i need, since I offer custom-made goods-- I need the order number, the item and all of the options.

Any ideas?
psst... wanna buy a wand?

#7   dlan

dlan
  • Members
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 21 November 2007 - 14:04

Hi Dlan
Thank you so much, great contribuition. One question, actually two. I need to adf the type of shipping (expedited or standard) and total of the order to the download. Any resources on how to do that?
Thanks a bunch.


Hi,

Yes you can.In the export orders file, find the select query that selects the fields that should be exported. Just add the fields you want within the query (as long as they are in the same table. If this is not the case, you will have to join two tables). Then where you see "Queries 1" ... to 9 you can play with this to add the information you need.
Regarding your second question, I didn't really look into the file but you could add something like this that will display the number of exported orders on your page :

$reccount = (integer) mysql_query("Select COUNT(orders_id, date_purchased, customers_name, cc_owner, customers_company, customers_email_address, billing_street_address, billing_city, billing_state, billing_postcode, billing_country, customers_telephone, delivery_name, delivery_company, delivery_street_address, delivery_city, delivery_state, delivery_postcode, delivery_country, cc_type, cc_number, cc_expires) from orders");
echo 'Number of exported records : $reccount' ;

David

#8   dlan

dlan
  • Members
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 21 November 2007 - 14:08

I have implemented the contrib, and I get all of the buyer's information but none of the items that they've purchased. And that's what i need, since I offer custom-made goods-- I need the order number, the item and all of the options.

Any ideas?


Hi,
Normally you should get the description of the item. Have you modified in any way the exportorders.php?
David

#9   peter_of_stirling

peter_of_stirling
  • Members
  • 52 posts
  • Real Name:Peter Robertson

Posted 24 November 2007 - 04:06

Hi all,

I can not get the data to download to my desktop. I have no trouble with getting the XML export to download to my desktop. I note with that script they use a dataexport folder within the admin folder. This contains an xml file.

I have never had any success with the cache setup.

Would this have any bearing on my problem?

I am not a php programmer but use cut and paste plus trial and error to achieve results.

Regards,
peter_of_stirling

#10   oojacoboo

oojacoboo
  • Members
  • 3 posts
  • Real Name:Jacob

Posted 29 November 2007 - 21:59

how do you filter by the order status....?

#11   oojacoboo

oojacoboo
  • Members
  • 3 posts
  • Real Name:Jacob

Posted 30 November 2007 - 00:10

I think adding in the filter from this contribution would be best. I tried to implement it, but, unfortunately my php skills are really quite limited... /dry.gif' class='bbc_emoticon' alt='<_<' />

http://addons.oscommerce.com/info/4675

#12   dlan

dlan
  • Members
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 30 November 2007 - 08:22

how do you filter by the order status....?


Hi,
Simply by changing the orderby statement in your sql query in the exportorders.php file.
David

#13   oojacoboo

oojacoboo
  • Members
  • 3 posts
  • Real Name:Jacob

Posted 30 November 2007 - 20:51

I'm sorry, I don't see that statement in the exportorders.php file. Would you mind telling me what it says, or the line number? Maybe I have the wrong contribution file?

I see 9 Queries
- Order Comments
- Order Sub-Total
- Tax
- Insurance
- Shipping
- Giftwrap
- Order Total
- Product Count
- List of Products Ordered

I just don't see anything on here to filter out by Order Staus. I only want to export out Orders that have been paid for, via paypal, and OSC logs all orders even if the customer doesn't complete the order process...

Thanks!

#14   Schadeboy

Schadeboy
  • Members
  • 90 posts
  • Real Name:Brian Schade
  • Location:Sierra Vista, AZ

Posted 30 November 2007 - 21:58

Hello, everyone.

I'm not sure if I'm missing something, but can someone tell me where the exported file is supposedly stored? I can't see anything anywhere on my site. When I run the script, after everything is said and done, I get a blank screen in my browser. Is this how this is supposed to work? Everyone else appears to have made it work properly. I don't know what I'm missing. The install seems fairly straight forward, so I just don't see what's wrong.

Thanks.

--------- UPDATE ---------

Nevermind. I was using an older version of the exportorders.php file. I downloaded the newest one and got the function working. Thanks to everyone who made this contrib! It will save me a ton of work.

Edited by Schadeboy, 30 November 2007 - 22:05.

Brian (Schadeboy) Schade
CIO and Webmaster
Twilight Teez, LLC

"Camping is nature's way of promoting the hotel industry."
-Dave Barry

#15   Schadeboy

Schadeboy
  • Members
  • 90 posts
  • Real Name:Brian Schade
  • Location:Sierra Vista, AZ

Posted 30 November 2007 - 22:35

Okay, another thing. Now that I have the file exported, I need to include the attributes associted with our orders. Any way of doing this?

Also, I too would like to see column headers in the exported file. This would be extremely helpful.
Brian (Schadeboy) Schade
CIO and Webmaster
Twilight Teez, LLC

"Camping is nature's way of promoting the hotel industry."
-Dave Barry

#16   peter_of_stirling

peter_of_stirling
  • Members
  • 52 posts
  • Real Name:Peter Robertson

Posted 02 December 2007 - 12:57

Hi Schadeboy

I note that you have succeeded!

I am still not having any thing download.

Which latest version are you using - is it the one with dropdown lists? If so, yes my orders do show both order no and date. I select a number of them but when I press Export to CSV the routine seems to run whilst the green bar is open at the bottom of the screen. It then throws me right out of /admin and brings me back to the admin log in screen.

I am not a php programmer and if I experiment I only seem to make matters worse.

I have also tried with several other contributions supposodly producing CSV also without success.

The only one that works is on http://www.mt-soft.c...ript-mysqldump/.
This seems to use ob gzhandler instead of Generate CSV.

I am not clever enough to know how to adapt the ob gzhandler approach to David's script.

Any help would be appreciated!

Regards,
peter_of_stirling

Hello, everyone.

I'm not sure if I'm missing something, but can someone tell me where the exported file is supposedly stored? I can't see anything anywhere on my site. When I run the script, after everything is said and done, I get a blank screen in my browser. Is this how this is supposed to work? Everyone else appears to have made it work properly. I don't know what I'm missing. The install seems fairly straight forward, so I just don't see what's wrong.

Thanks.

--------- UPDATE ---------

Nevermind. I was using an older version of the exportorders.php file. I downloaded the newest one and got the function working. Thanks to everyone who made this contrib! It will save me a ton of work.



#17   nudylady

nudylady
  • Members
  • 234 posts
  • Real Name:nudylady

Posted 02 December 2007 - 17:54

Hi,
Simply by changing the orderby statement in your sql query in the exportorders.php file.
David


I have 2 exportorders.php
catalog\admin\includes\languages\english\exportorders.php
catalog\admin\exportorders.php

which one to edit?

Can anyone tell me this statement?

#18   nudylady

nudylady
  • Members
  • 234 posts
  • Real Name:nudylady

Posted 02 December 2007 - 19:35

I have so many unpaid orders. I just wanna export paid PROCESSING status orders.
I am a dummy. I can't figure out what dlan said.
after 3 hours of trying. I added a order status colum after orders Id.
After export to excel. I delete orders not in Processing status. it worked.

here is what I have done:

open catalog\admin\exportorders.php

-------lines 115,119,123,127----------
after orders_id, add orders_status,

----line 136---------------
after $Orders_id = $row_orders["orders_id"];
add $Orders_status = $row_orders["orders_status"];

----line 275----------------
after $csv_output .= $Orders_id . "," ;
add $csv_output .= $Orders_status . "," ;

one thing I want to mention. in excel sheet pending shows as 1, processing shows as 2.
I dont know how to show the acctual word in steady of numbers.

#19   peter_of_stirling

peter_of_stirling
  • Members
  • 52 posts
  • Real Name:Peter Robertson

Posted 04 December 2007 - 04:37

Hi David,

I am still trying to make your efforts work.

I have even installed it on to another clean domain without any success!

Conversely, the latest contribution of export customers worked without me needing to do any changes apart from those recommended.

Admittedly, that requires far less filtering than the full extraction of orders.

I note that with that code it starts with if (!_POST['submit']) instead of if (!$submitted || $submitted !=1)
and concludes with :-
header("Content-Type: application/force-download\n");
header("Content-Disposition: attachment; filename=exportsname_" . date("Ymd") . ".txt");
header("Pragma: no-cache");
header("Expires: 0");
echo $csv_output;
die();
}
require(DIR_WS_INCLUDES . 'application_bottom.php');
//function main
?>

I have tried applying some of these different ideas to your code - but still without success.

Any further ideas as to why I can not make it work will be appreciated!!

Regards,
peter_of_stirling

#20   nudylady

nudylady
  • Members
  • 234 posts
  • Real Name:nudylady

Posted 04 December 2007 - 19:21

Hi David,

I am still trying to make your efforts work.

I have even installed it on to another clean domain without any success!


very strange.
try from another computer, a clean domain, on a different host must work.