Create a CSV file from MS-SQL server query

  • Posts: 3
  • Thank you received: 0
12 years 5 months ago #53533

Hi!

I need to produce an input CSV file for importing into HikaShop. I've created an export file which is attached to this post.
The data is related to food products and has several custom fields, like EAN code, Ingredients, Original Description, etc., various categories and subcategories.
The products can be sold either in standard cartons or as single items. For this I have created variant which is called Unit (of measure) with two options Scatole (Boxes) and Pezzi (single items).

The original data is all on a Microsoft SQL Server and I can create a simple SELECT query to produce a table with item numbers, descriptions, prices, categories, etc.
However, when I do export from HikaShop product page to see the format of the file to be created, I notice that it's not just a simple table, but some rows are dedicated only to categories and subcategories, while some are specific to variants, etc.
So, I'm a bit confused about producing an input CSV file.
Can you advise how to produce such table using a SQL query? Or any other way to accomplish this task? Also, if possible, I would like to automate importing proc using a CRON job once I figure out the way to produce an import CSV file for HikaShop.

Many thanks in advance!

File Attachment:

File Name: export.csv
File Size:3 KB

Attachments:

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

  • Posts: 82821
  • Thank you received: 13368
  • MODERATOR
12 years 5 months ago #53607

Hi,

The lines regarding the categories are not necessary. They can be used to import a category structure. If you already have your categories, or if you don't have ambiguous category names (several different categories with the same name), you should not need them.

Regarding variants/characteristics, when you have characteristics attached to a product in HikaShop, each combination of characteristic values corresponding to a variant. Each variant of the product is stored in the hikashop_product table. That allows you to specify different prices,quantities, information, etc for different variants.
Without the variants generated the characteristics won't display properly on the product page.

I can't tell you how to do your query. It depends on your MSSQL database structure which I don't know.

What I can say though is that it sure is possible to import it automatically. In the commercial editions of Hikashop, there is a data feed plugin which uses the cron system available to automatically import CSV files in HikaShop. You could either use it if the format is the correct one, or modify it to adapt the data of your CSV to HikaShop (so that could be a solution in order to inject the variants in the CSV, if you can't generate these lines with your MSSQL CSV export).

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

Time to create page: 0.043 seconds
Powered by Kunena Forum