CSV Exporter to Microsoft Excel

  • Posts: 49
  • Thank you received: 0
13 years 3 months ago #2940

I just saw that there is a new version of Hika, and that you did something to the CSV exporter for business plans. The last version 1.3.8 when exported out to csv wouldn't come in right into Microsoft Excel, meaning the information wouldn't be set into rows and columns. It was just a long list of the data in one column. Have you tested this csv eport to Excel? I know for myself and my client, they want this data to be able to be brought into Excel.

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

  • Posts: 81361
  • Thank you received: 13036
  • MODERATOR
13 years 3 months ago #2942

We tested the CSV files from the beginning with Excel and it has always worked for us properly. Your are talking about the export capability on the dashboard, right ?

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

  • Posts: 49
  • Thank you received: 0
13 years 3 months ago #2947

If I go to control panel and export out Last orders and bring in the csv file into Excel, what I see is rows with each order, but only one column. The title for the columns are all in one row, not separated out into individual cells or columns like I would like.

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

  • Posts: 81361
  • Thank you received: 13036
  • MODERATOR
13 years 3 months ago #2948

Could you copy the header of that file here (with the column names) ?

Normally, each column should be spearated by a ;. If that's not the case, then, look at the file administrator/components/com_hikashop/views/dashboard/view.html.php near line 70. You should have a line defining the separator character like this:

$separator = ";";

Please make sure that you have a ; there and not a ,

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

  • Posts: 49
  • Thank you received: 0
13 years 3 months ago #2950

It is a semi-colon separator. Actually most csv files I have dealt with are comma separated. I have one from another company I use to use before I went Joomla, and when I put comma it puts everything into columns. This is the header:

user_id;user_cms_id;user_email;user_partner_email;user_partner_id;user_partner_price;user_partner_paid;user_created_ip;user_unpaid_amount;user_partner_currency_id;user_created;user_currency_id;user_partner_activated;order_id;order_billing_address_id;order_shipping_address_id;order_user_id;order_status;order_discount_code;order_created;order_ip;order_currency_id;order_shipping_price;order_discount_price;order_shipping_id;order_shipping_method;order_payment_id;order_payment_method;order_full_price;order_modified;order_partner_id;order_partner_price;order_partner_paid;order_type;order_partner_currency_id

All of this was found in 1 row 1 column.

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

  • Posts: 81361
  • Thank you received: 13036
  • MODERATOR
13 years 3 months ago #2952

Maybe we don't use the same version of Excel then ? I use version 2007 and I can import hikashop CSV files without any issue.

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

  • Posts: 49
  • Thank you received: 0
13 years 3 months ago #3041

Because I am not a big Micorsoft person my Excel is only 2003. I know, I know, I should upgrade. But my Mac Numbers program didn't like it either. However, I went to the file that you noted and changed semi-colon to comma and it worked. I now have columns. Just a suggestion, you might want to consider having something in configurations to allow people to pick what they want as a separator.

Something too, How do I get the names addresses, city, and stuff on this Excel order table? It might be a good idea to have this on. I know you have two or more tables working together to pull this stuff. I have another Joomla component that I might use for this to get all the info together. What tables hold what? Which one is the billing address details, and which one orders? Also which column joins the two or more if I make a LEFT JOIN for my SQL.

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

  • Posts: 81361
  • Thank you received: 13036
  • MODERATOR
13 years 3 months ago #3043

Ok, we will see to add an option for the separator in CSV files then in a future version :)

The addresses are stored in the table hikashop_address
The columns order_shipping_address_id and order_billing_address_id link to the address_id column of the hikashop_address table.
Adding the addresses in the CSV would be possible...we will see in a future version as well.

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

  • Posts: 49
  • Thank you received: 0
13 years 3 months ago #3050

Thanks for the information and consideration

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

  • Posts: 49
  • Thank you received: 0
13 years 3 months ago #3051

Is there any way of having the value of country and state not to have the country, number and underscores ie

country_Antigua_and_Barbuda_9

to

Antigua and Barbuda

and

state_New_South_Wales_431

to

NSW

I see the state abbreviation in the system, but not sure how to make that appear rather than the long version? If my client wants to use this Excel document to tie back into Microsoft word for mailing addresses, they will have to do a lot of editing to make that work. I like the fact that I someone can select the country and then have the state load, but just would like the value different.

Also, something for the CSV exporter, it would be great if there was the option of check boxing the columns you wanted to have on the csv export. Some of these id fields from an Excel point of view, unnecessary.

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

  • Posts: 81361
  • Thank you received: 13036
  • MODERATOR
13 years 3 months ago #3054

The normal name of the states and countries are stored in the table hikashop_zone. You could make additional LEFT JOIN from the address_state and address_country fields to the zone_namekey field in hikashop_zone to get the zone_name_english value.

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

  • Posts: 1
  • Thank you received: 0
10 years 2 months ago #138110

Hi,

I'm just new to hikashop and after messing around with it for a day i'm going to try another program, however I also had this problem, If you import the data to excel as a .txt file, you can then tell it that it uses ; instead of ,
the only problem I have is I want to alter data and re-import it and there is no way to tell excel to use ; instead of , when saving the file

here's an eg.
opened as you did with excel 2010
product_id;"product_parent_id";"product_name";"product_description";"product_quantity";"product_code";"product_published";"product_hit";"product_created";"product_sale_start";"product_sale_end";"product_delay_id";"product_tax_id";"product_type";"product_vendor_id";"product_manufacturer_id";"product_url";"product_weight";"product_keywords";"product_weight_unit";"product_modified";"product_meta_description";"product_dimension_unit";"product_width";"product_length";"product_height";"product_max_per_order";"product_access";"product_group_after_purchase";"product_min_per_order";"product_contact";"product_display_quantity_field";"product_last_seen_date";"product_sales";"product_waitlist";"product_layout";"product_average_score";"product_total_vote";"product_page_title";"product_alias";"product_price_percentage";"product_msrp";"product_canonical";"categories_ordering";"parent_category";"categories_image";"categories";"price_value";"price_currency_id";"price_min_quantity";"price_access";"files";"images";"related";"options"

imported as a .txt file ( these are all in seperate cells on excel)
product_id product_parent_id product_name product_description product_quantity product_code product_published product_hit product_created product_sale_start product_sale_end product_delay_id product_tax_id product_type product_vendor_id product_manufacturer_id product_url product_weight product_keywords product_weight_unit product_modified product_meta_description product_dimension_unit product_width product_length product_height product_max_per_order product_access product_group_after_purchase product_min_per_order product_contact product_display_quantity_field product_last_seen_date product_sales product_waitlist product_layout product_average_score product_total_vote product_page_title product_alias product_price_percentage product_msrp product_canonical categories_ordering parent_category categories_image categories price_value price_currency_id price_min_quantity price_access files images related options

Hope that helps you with your issue though

Cheers
Daz

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

  • Posts: 81361
  • Thank you received: 13036
  • MODERATOR
10 years 2 months ago #138135

Hi,

HikaShop can import CSVs with either , or ; as a separator, that's not a problem.
In the same manner, HikaShop can export CSVs with either , or ; as a separator. For that, simply change the "CSV export separator" option of the configuration. That way, you can change the separator of the CSVs generated by HikaShop to the one preferred by your editor without having to mess with the import options of your editor.

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

Time to create page: 0.083 seconds
Powered by Kunena Forum