Exporting Data

Greg
Topic Author
Offline

15 years 7 months ago #7193 by Greg

Exporting Data was created by Greg

Does Hika Shop Essential or Business have a way to download order data in Excel format with all the order data on one row?

If so, is it customizable so I can choose which fields download (including custom fields)?

Thanks for your help.

Please Log in or Create an account to join the conversation.

nicolas
Away

15 years 7 months ago #7194 by nicolas

Replied by nicolas on topic Re: Exporting Data

It's not yet possible in HikaShop. That's something we want to add in the future though.

Please Log in or Create an account to join the conversation.

ctsang007
Offline

15 years 7 months ago #7482 by ctsang007

Replied by ctsang007 on topic Re: Exporting Data

That's a pity.

I am using the business version for registrations for events/courses/seminars. I need to get an overview of all registrants per course/seminar and their basic details.

Can you advise how I can achieve that?

Thanks in advance.

Please Log in or Create an account to join the conversation.

nicolas
Away

15 years 7 months ago #7544 by nicolas

Replied by nicolas on topic Re: Exporting Data

Could you be more precise ?

How do you handle the registrations on your website ? With custom entry fields ?

Please Log in or Create an account to join the conversation.

ctsang007
Offline

15 years 7 months ago #7549 by ctsang007

Replied by ctsang007 on topic Re: Exporting Data

I have created products (=seminars) that visitors can add to their cart as way of registering (nothing special here I guess). Payment is now done via bank transfer until the Dutch IDEAL plugin is available.

I would like to have a report (excel) with the following info:
ordered product(seminar), product id, first name, last name, address, zip code, city, country, date registered, name on certificate(customfield in address table), name of association(customfield in address table), order status.

The standard reports in the dashboard all don't have the user info (except ID and email) nor custom fields.

Does this make sense? I hope you can help me out here.

Please Log in or Create an account to join the conversation.

nicolas
Away

15 years 7 months ago #7550 by nicolas

Replied by nicolas on topic Re: Exporting Data

As I said in my first post on that thread there is no such function at the moment in hikashop. Retrieving all that information for each item purchased and making it a CSV file for excel is not something we can do like this. It will require some work.
We are currently in discussion with another user to do such custom work for a fee but it's not yet confirmed and will take us a few weeks.

Please Log in or Create an account to join the conversation.

ctsang007
Offline

15 years 7 months ago #7571 by ctsang007

Replied by ctsang007 on topic Re: Exporting Data

Thanks. Yes, I understood from the previous posts that it is not possible at the moment.

I was looking for some suggestions/tips/recommendations as a workaround until the solution is ready.

Do you/anyone have experience with this using Joomla extensions or something like that?

Please Log in or Create an account to join the conversation.

nicolas
Away

15 years 7 months ago #7575 by nicolas

Replied by nicolas on topic Re: Exporting Data

I don't know of such extensions capable of that. The only way is to develop an export function.

Please Log in or Create an account to join the conversation.

Chill Creations
Offline

15 years 5 months ago #13526 by Chill Creations

Replied by Chill Creations on topic Re: Exporting Data

Hi

I know there are extensions that enable you to get the database values and export as CSV. They are probably not that easy to use, but you can try them. Here is the link to JED.
extensions.joomla.org/extensions/migrati...data-import-a-export

@ctsang007: see my "handtekening", iDEAL plugin is ready.

Regards
David

iDEAL for Hikashop by Dutch iDEAL experts

Please Log in or Create an account to join the conversation.

nicolas
Away

15 years 5 months ago #13554 by nicolas

Replied by nicolas on topic Re: Exporting Data

Ah yes, good idea ! Indeed such extensions might be able to do the interim while we develop a great export directly in hikashop.

Please Log in or Create an account to join the conversation.

aheng5
Offline

14 years 2 weeks ago #64902 by aheng5

Replied by aheng5 on topic Re: Exporting Data

I use (Joomla 2.5) Ari Data Tables to create table from SQL however, because the way Hika Shop does it db tables, there's not common field that I can make a join report. Right now I can only join orders and order products table (order_id in common) without being able to included the associated buyers name (not the choice I was looking for). This can give category and product filtering only.

www.ari-soft.com/vmchk/Joomla-Components...d-product-flyer.html

hope this helps.

Please Log in or Create an account to join the conversation.

nicolas
Away

14 years 2 weeks ago #65008 by nicolas

Replied by nicolas on topic Re: Exporting Data

Hi,

If you want to load the user address, including its name, you can use the order_billing_address_id to link the order table with the address table

Note that HikaShop already has such reporting tools:
www.hikashop.com/en/support/documentatio...hop-report-form.html
And that it is also possible to feed the ARI Data Tables extension a CSV so you could just export your orders via the corresponding button of the orders listing and use it instead of the tables. The CSV will contain all the information of the orders.

Please Log in or Create an account to join the conversation.

aheng5
Offline

14 years 2 weeks ago - 14 years 2 weeks ago #65104 by aheng5

Replied by aheng5 on topic Re: Exporting Data

Thank you for the information. For my needs I wanted to provide a restricted report view online with only specific fields by pulling via SQL; not allow them access to the backend reporting stats (which are good). Downloading via CSV I still need to format to get rid of those fields I don't want; especially all the empty fields. It's still not a clean easiest process to download for me but for now it will have to do as I've spent the money to buy your Essential level. Maybe I'm still missing something.

Last edit: 14 years 2 weeks ago by aheng5.

Please Log in or Create an account to join the conversation.

nicolas
Away

14 years 2 weeks ago #65109 by nicolas

Replied by nicolas on topic Re: Exporting Data

There is no interface to select the columns you want for the export for now. It's on the todo list for a future release.

Please Log in or Create an account to join the conversation.

aheng5
Offline

14 years 2 weeks ago #65143 by aheng5

Replied by aheng5 on topic Re: Exporting Data

Ok so I'm successful in combining 3 tables to get the results I need (code below); however I'm having problems with the 'order_created' date. I don't know the right SQL code to convert this field to date format mm-dd-yyyy. I get a long digit similar to what I get when I export to cvs from the backend. Any suggestions? Thanks

SELECT DATE_FORMAT(jml_hikashop_order.order_created) as 'Order Date', jml_hikashop_order.order_status as 'Status', jml_hikashop_order.order_number as 'Order #',
jml_hikashop_address.address_firstname as 'First Name', jml_hikashop_address.address_lastname as 'Last Name',

jml_hikashop_address.address_telephone as 'Phone',
jml_hikashop_order.order_payment_method as 'Payment Method', Round(jml_hikashop_order.order_full_price,2) as

'Total', jml_hikashop_order_product.order_product_quantity as 'Qty', jml_hikashop_order_product.order_product_name

as 'Item', jml_hikashop_order_product.order_product_options as 'Options'
FROM jml_hikashop_address
JOIN jml_hikashop_order
ON
(jml_hikashop_address.address_user_id=jml_hikashop_order.order_user_id)
JOIN jml_hikashop_order_product
ON jml_hikashop_order.order_id=jml_hikashop_order_product.order_id

ORDER BY jml_hikashop_address.address_lastname DESC

Please Log in or Create an account to join the conversation.

nicolas
Away

14 years 2 weeks ago #65145 by nicolas

Replied by nicolas on topic Re: Exporting Data

You should do like that: DATE_FORMAT(DATE( FROM_UNIXTIME(jml_hikashop_order.order_created) ), '%m-%d-%Y' ) AS 'Order Date'

Please Log in or Create an account to join the conversation.

aheng5
Offline

14 years 2 weeks ago #65152 by aheng5

Replied by aheng5 on topic Re: Exporting Data

THANK YOU

this completes what I need to do on my web site.

Here's my final sql coding. I just change my filter to the product code I want to report:

SELECT DATE_FORMAT(DATE( FROM_UNIXTIME(jml_hikashop_order.order_created) ), '%m-%d-%Y' ) AS 'Order Date', jml_hikashop_order.order_status as 'Status', jml_hikashop_order.order_number as 'Order #',
jml_hikashop_address.address_firstname as 'First Name', jml_hikashop_address.address_lastname as 'Last Name', jml_hikashop_address.address_telephone as 'Phone',
jml_hikashop_order.order_payment_method as 'Payment Method', Round(jml_hikashop_order_product.order_product_price,2) as 'Item Price', jml_hikashop_order_product.order_product_quantity as 'Qty', jml_hikashop_order_product.order_product_name as 'Item', jml_hikashop_order_product.order_product_options as 'Options'
FROM jml_hikashop_address
JOIN jml_hikashop_order
ON
(jml_hikashop_address.address_user_id=jml_hikashop_order.order_user_id)
JOIN jml_hikashop_order_product
ON jml_hikashop_order.order_id=jml_hikashop_order_product.order_id
WHERE jml_hikashop_order_product.order_product_name LIKE '%Member'
ORDER BY jml_hikashop_address.address_lastname DESC

Please Log in or Create an account to join the conversation.

aheng5
Offline

13 years 11 months ago #71635 by aheng5

Replied by aheng5 on topic Re: Exporting Data

Hi, by chance what is the syntax after the date to include the time of the order? Thanks Alana

Please Log in or Create an account to join the conversation.

nicolas
Away

13 years 11 months ago #71905 by nicolas

Replied by nicolas on topic Re: Exporting Data

It should be:
DATE_FORMAT(DATE( FROM_UNIXTIME(jml_hikashop_order.order_created) ), '%m-%d-%Y %H:%i' ) AS 'Order Date'

dev.mysql.com/doc/refman/5.5/en/date-and...function_date-format

Please Log in or Create an account to join the conversation.

mavievs
Offline

12 years 9 months ago - 12 years 9 months ago #134894 by mavievs

Replied by mavievs on topic Re: Exporting Data

is there any progress in exporting functionality?

Last edit: 12 years 9 months ago by mavievs. Reason: grammar

Please Log in or Create an account to join the conversation.

Time to create page: 0.325 seconds