importing categories from db - using EasyPopulate !

[AzureCrystal]

Thanks in advance for any help !!

I am trying to use an existing state/county/city database to create those categories and products on the fly in ZenCart using Easy Populate, but having no luck so far.

My SQL skills are a bit rusty, but I was able to create a query to build a CSV file which looks to be in the same format as the exported EasyPopulate CSV file, I am not sure why EasyPopulate does not read it in properly, and not sure how exactly ZenCart builds categories based on the import file, etc... I know I must be missing something obvious !!

Here is the message from Easy Populate, usually I get a"blue" listing of the products imported:

File uploaded.
Temporary filename: /tmp/phpB9t2ko
User filename: cart-zipcode-merge.csv
Size: 147617

Upload Complete

And here is the Query I built using a zipcode db and padding with some dummy data:

SELECT
' ' as v_products_model,
'BuyThisAd.jpg' as v_products_image,
concat(`zip-codes-database-deluxe-sampl`.State, '-', `zip-codes-database-deluxe-sampl`.CountyName, '-', `zip-codes-database-deluxe-sampl`.City ) as v_products_name_1,
'This is a 148 x 200 pixel ad... It will consist of your picture or logo, your office information, and a link to either a video, website, your profile... The ad will on the left and right of our main directory site, any logo or picture must be 100x10' as v_products_description_1,
' ' as v_products_url_1,
' ' as v_specials_price,
' ' as v_specials_date_avail,
' ' as v_specials_expires_date,
'250' as v_products_price,
'0' as v_products_weight,
' ' as v_date_avail,
'9/3/2008 6:09:49 PM' as v_date_added,
'4' as v_products_quantity,
' ' as v_manufacturers_name,
'Banner Ads' as v_categories_name_1,
'Personal Use' as v_categories_name_2,
'Accident & Injuries' as v_categories_name_3,
`zip-codes-database-deluxe-sampl`.State as v_categories_name_4,
`zip-codes-database-deluxe-sampl`.CountyName as v_categories_name_5,
`zip-codes-database-deluxe-sampl`.City as v_categories_name_6,
'--none--' as v_tax_class_title,
'1' as v_status,
'EOREOR' as EOREOR
FROM
`zip-codes-database-deluxe-sampl`
ORDER BY
`zip-codes-database-deluxe-sampl`.ZipCode ASC

I think the data may be in the right format, but my export using Navicat into xls may have done something naughty with the conversion, any help will be DEEPLY appreciated :-))))

I will gladly post more info, I wish I knew more about the ZenCart db structures, but, too many things to learn tonight...

[misty]

http://www.zen-cart.com/wiki/index.php/Easy_Populate
Wiki for EP at above url should help

[AzureCrystal]

http://www.zen-cart.com/wiki/index.php/Easy_Populate
Wiki for EP at above url should help

Thanks a million Misty, I didn't know this wiki existed, exactly what I needed, might be my answer !!

[misty]

No problem...hope your mission proves successful

[AzureCrystal]

No problem...hope your mission proves successful

Indeed, thanks !!

Upload Results
File uploaded.
Temporary filename: /tmp/php2y4l03
User filename: cart-zipcode-merge7.txt
Size: 149998

NEW PRODUCT! - Model: 1001 | BuyThisAd. | 1001-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | ANSONVILLE | --none-- | 1 |
NEW PRODUCT! - Model: 1002 | BuyThisAd. | 1002-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | LILESVILLE | --none-- | 1 |
NEW PRODUCT! - Model: 1003 | BuyThisAd. | 1003-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | MC FARLAN | --none-- | 1 |
NEW PRODUCT! - Model: 1004 | BuyThisAd. | 1004-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | MORVEN | --none-- | 1 |
NEW PRODUCT! - Model: 1005 | BuyThisAd. | 1005-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | PEACHLAND | --none-- | 1 |
NEW PRODUCT! - Model: 1006 | BuyThisAd. | 1006-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | PEACHLAND | --none-- | 1 |
NEW PRODUCT! - Model: 1007 | BuyThisAd. | 1007-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | PEACHLAND | --none-- | 1 |
NEW PRODUCT! - Model: 1008 | BuyThisAd. | 1008-NC-AN | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | ANSON | POLKTON | --none-- | 1 |
NEW PRODUCT! - Model: 1009 | BuyThisAd. | 1009-NC-BE | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | BEAUFORT | PANTEGO | --none-- | 1 |
NEW PRODUCT! - Model: 1010 | BuyThisAd. | 1010-NC-BE | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | BEAUFORT | PINETOWN | --none-- | 1 |
NEW PRODUCT! - Model: 1011 | BuyThisAd. | 1011-NC-BE | This is a | | | | | 250 | 0 | | 9/3/2008 6 | 4 | | Banner Ads | Personal U | Accident a | NC | BEAUFORT | WASHINGTON | --none-- | 1

[AzureCrystal]

For sake of helping others, here is the revised SQL with a self incrementing number to create the model number: I used Navicat to export the query into a tab delimited text file:

SELECT
@rownum:=@rownum+1 v_products_model,
'BuyThisAd.jpg' as v_products_image,
concat(`zip-codes-database-deluxe-sampl`.State, '-', `zip-codes-database-deluxe-sampl`.CountyName, '-', `zip-codes-database-deluxe-sampl`.City ) as v_products_name_1,
'This is a 148 x 200 pixel ad... It will consist of your picture or logo, your office information, and a link to either a video, website, your profile... The ad will on the left and right of our main directory site, any logo or picture must be 100x10' as v_products_description_1,
' ' as v_products_url_1,
' ' as v_specials_price,
' ' as v_specials_date_avail,
' ' as v_specials_expires_date,
'250' as v_products_price,
'0' as v_products_weight,
' ' as v_date_avail,
'9/3/2008 6:09:49 PM' as v_date_added,
'4' as v_products_quantity,
' ' as v_manufacturers_name,
'Banner Ads' as v_categories_name_1,
'Personal Use' as v_categories_name_2,
'Accident & Injuries' as v_categories_name_3,
`zip-codes-database-deluxe-sampl`.State as v_categories_name_4,
`zip-codes-database-deluxe-sampl`.CountyName as v_categories_name_5,
`zip-codes-database-deluxe-sampl`.City as v_categories_name_6,
'--none--' as v_tax_class_title,
'1' as v_status,
'EOREOR' as EOREOR
FROM
(SELECT @rownum:=1000) numDummy, `zip-codes-database-deluxe-sampl`
ORDER BY
`zip-codes-database-deluxe-sampl`.State,
`zip-codes-database-deluxe-sampl`.CountyName,
`zip-codes-database-deluxe-sampl`.City ASC