Importing dealer names & addresses to Store Locator?

[Urbanfarmer]

Hi.....is there a way to use a csv import from Excel to upload 150 retailer names and addresses for the Store Locator Map? I can't imagine doing it manually. Next year we may be in several hundred stores.

 

Thanks........

[RogueWaveLimited]

You should be able to import the store data via SQL if the import function doesn't support that table.

[osdznr14mm]

Hi Urbanfarmer,

 

There is a way to do it directly in the mySQL database if you use something like phpMyAdmin to access it. I spent ages trying to figure this out as I had hundreds of stores to input and wasn't about to enter them by hand. The stores would never show up in the store locator until I finally got it to work recently.

 

I tested this method in PS 1.5.6.

 

So first you would adjust your excel table to mimic the same categories and order as the ps_store table in PrestaShop. 

 

Then you would convert it to a sql query or csv file that you could import with phpMyAdmin.

Example query using default stores:

 

INSERT INTO `ps_store` (`id_store`, `id_country`, `id_state`, `name`, `address1`, `address2`, `city`, `postcode`, `latitude`, `longitude`, `hours`, `phone`, `fax`, `email`, `note`, `active`, `date_add`, `date_upd`)

VALUES

(1,21,9,'Dade County','3030 SW 8th St Miami','','Miami',' 33135',25.76500500,-80.24379700,'a:7:{i:0;s:13:\"09:00 - 19:00\";i:1;s:13:\"09:00 - 19:00\";i:2;s:13:\"09:00 - 19:00\";i:3;s:13:\"09:00 - 19:00\";i:4;s:13:\"09:00 - 19:00\";i:5;s:13:\"10:00 - 16:00\";i:6;s:13:\"10:00 - 16:00\";}','','','','',1,'2012-12-06 23:48:16','2012-12-06 23:48:16'),

(2,21,9,'E Fort Lauderdale','1000 Northeast 4th Ave Fort Lauderdale','','Miami',' 33304',26.13793600,-80.13943500,'a:7:{i:0;s:13:\"09:00 - 19:00\";i:1;s:13:\"09:00 - 19:00\";i:2;s:13:\"09:00 - 19:00\";i:3;s:13:\"09:00 - 19:00\";i:4;s:13:\"09:00 - 19:00\";i:5;s:13:\"10:00 - 16:00\";i:6;s:13:\"10:00 - 16:00\";}','','','','',1,'2012-12-06 23:48:16','2012-12-06 23:48:16'),

(3,21,9,'Pembroke Pines','11001 Pines Blvd Pembroke Pines','','Miami','33026',26.00998700,-80.29447200,'a:7:{i:0;s:13:\"09:00 - 19:00\";i:1;s:13:\"09:00 - 19:00\";i:2;s:13:\"09:00 - 19:00\";i:3;s:13:\"09:00 - 19:00\";i:4;s:13:\"09:00 - 19:00\";i:5;s:13:\"10:00 - 16:00\";i:6;s:13:\"10:00 - 16:00\";}','','','','',1,'2012-12-06 23:48:16','2012-12-06 23:48:16'),

(4,21,9,'Coconut Grove','2999 SW 32nd Avenue','','Miami',' 33133',25.73629600,-80.24479700,'a:7:{i:0;s:13:\"09:00 - 19:00\";i:1;s:13:\"09:00 - 19:00\";i:2;s:13:\"09:00 - 19:00\";i:3;s:13:\"09:00 - 19:00\";i:4;s:13:\"09:00 - 19:00\";i:5;s:13:\"10:00 - 16:00\";i:6;s:13:\"10:00 - 16:00\";}','','','','',1,'2012-12-06 23:48:16','2012-12-06 23:48:16'),

(5,21,9,'N Miami/Biscayne','12055 Biscayne Blvd','','Miami','33181',25.88674000,-80.16329200,'a:7:{i:0;s:13:\"09:00 - 19:00\";i:1;s:13:\"09:00 - 19:00\";i:2;s:13:\"09:00 - 19:00\";i:3;s:13:\"09:00 - 19:00\";i:4;s:13:\"09:00 - 19:00\";i:5;s:13:\"10:00 - 16:00\";i:6;s:13:\"10:00 - 16:00\";}','','','','',1,'2012-12-06 23:48:16','2012-12-06 23:48:16');

 

Or you could import as a csv file. 

 

Now here's the important part: you MUST also update the ps_store_shop or else it won't recognize your stores. 

 

Example using default stores:

 

INSERT INTO `ps_store_shop` (`id_store`, `id_shop`)

VALUES

(1,1),

(2,1),

(3,1),

(4,1),

(5,1);

 

Or import as a csv file. You have to create a corresponding entry with id_store and id_shop in ps_store_shop for every new store you enter. I only had one online shop so my id_shop was always "1." BTW, you need to have the latitude/longitude for each store location too. 

[Urbanfarmer]

"BTW, you need to have the latitude/longitude for each store location too."

 

Ouch! That was all great info up until the last sentence. I thought I might have a chance of figuring it out. but then the lat/long......

 

I might try disabling the Store Locator and see if I can put in BatchGeo instead. No lat/long. And I already have the csv file for that. I used it on another site which I'm migrating from. It's not ideal, but simple.

 

A big thanks for your help. 

[RogueWaveLimited]

The only fields required on the store locator preferences (adding a store) are name, address, city, country, state, zip.  So these should be the only fields that would be required to use phpmyadmin to add stores. 

[Urbanfarmer]

The only fields required on the store locator preferences (adding a store) are name, address, city, country, state, zip.  So these should be the only fields that would be required to use phpmyadmin to add stores. 

Oh, terrific. I'll give it a shot. Thank you..........

[Urbanfarmer]

The only fields required on the store locator preferences (adding a store) are name, address, city, country, state, zip.  So these should be the only fields that would be required to use phpmyadmin to add stores. 

Ok......I'm exploring this and having problems. I think I understand to go to phpmyadmin and use the import function to upload a file. The drop down gives me the choice of sql or csv. Which?

 

And then, is there a sample template in the BO that shows how the Excel file should be laid out?

[Urbanfarmer]

I clicked on Preferences-Store Contacts and then the Help icon. This quote was part of it. It seems to say that lat/long is necessary. I'm guessing it will take less time to manually enter the addresses than to find and file lat/long for all of them, and then figure out how to upload a csv file.

 

"One very important field is the "Latitude/Longitude" one, as this is what PrestaShop uses to indicate the location of your store. You can use Steve Morse's online tool to get the lat/long coordinates of a given address:"

[osdznr14mm]

Hi Urbanfarmer,

 

Yes, you need the latitude/longitude coordinates to use the map function in the store locator. Unfortunately, even if you enter all of the stores manually, you still need to enter the lat/lon anyway. It's up to you to decide if entering them manually is going to be faster than trying to learn phpmyadmin. I understand if you'd rather use your existing BatchGeo finder.

 

I will give pointers here to help you or anyone who's interested in the phpmyadmin way. There are some batch geocoders online that you can use. If you can get your addresses on one line each, then you can copy & paste the list in the batch converter and then it will give you a list of all the corresponding lat/lon coordinates which you can then paste into 2 columns in your excel file. This might take some trial & error. The batch converter might give you some errors if the address is malformed. You also should get a free API key to get better results--that will be explained on the batch converter pages. Here are 2 of them you can try:

 

http://www.gpsvisualizer.com/geocoder/

http://stevemorse.org/jcal/latlonbatch.html

 

About the layout of your store list excel file: PrestaShop does not provide a csv import in their back office for the store locator, so they don't have a template. You have to go into your PrestaShop database in phpmyadmin, select the table ps_store. If you select the tab "Structure" it will give you a list of the fields vertically: "id_store, id_country, etc." If you click on "Browse" you can see it laid out horizontally, with the column names & content in rows. You need to match your excel table to this setup exactly. 

 

Since you are new to phpmyadmin, I recommend you back up your database before making any changes. Or just back up the tables you're editing. Just click on the table name ps_store and click the "Export" tab. You can leave all the checkboxes unaltered but select csv and save to desktop/file down at the bottom. I guess you should import/export as csv since that is what you are familiar with. Maybe you could do some googling on how to use phpmyadmin. Also back up ps_store_shop.

 

[Urbanfarmer]

Thanks osdznr14mm. I have made progress. I have generated lat/long for all the addresses and created an excel file matching the ps_store in the phpmyadmin table. I have exported backups for both ps_store and ps_store_shop.

 

I think I'm ready for the next step (upload), but I'm not sure what to do next. Do I name the new file the same, ps_store, and import it to ps_store. Does that overwrite what is in there now?

 

Or should I do something else?

 

I'm not able to find a list of numbers for the state id's (id_state). 

Edited December 21, 2013 by Urbanfarmer (see edit history)

[osdznr14mm]

Oh, good for you! Oh yeah, the id_state… you'll need to put those in.

 

You can find the id_state in the ps_state table if you're in phpmyadmin or you can look it up in the PrestaShop back office under Localization/States.

 

As for importing the file, it doesn't matter what the name of the file is, it will just import the contents of the file. I'm assuming you're importing a csv? You select your table, then hit the import tab. When you select the file to import, there's going to be a checkbox asking if you want to overwrite or append. If you don't need any of the stores that already exist in your table you can overwrite. Or append if you want to, either way is fine.

 

Have you got your ps_store_shop ready too? It must correspond with your ps_store list, I mean have the same number of stores with the same id_store list. So if you overwrite your ps_store table, then overwrite your ps_store_shop too. If you haven't built your ps_store_shop file yet, here's an easy way to build it. I wrote a simple loop and put it in a php file & ran it in my browser:

 

<?php

 

for ($i = 1; $i<=661; $i++) {

 

echo $i. ",1<br />";

}

 

?>

 

Change the 661 to your number of stores. I updated my code for csv. 

 

So your ps_store_shop csv would look like this:

 

id_store,id_shop

1,1

2,1

3,1

 

… etc.

 

[Urbanfarmer]

OK, I figured out where to find the state id numbers in PS BO right after my last post. I saw no override query, so I deleted the five default stores that were in ps_stores and then tried uploading a csv. There were errors, so I tried XML and was successful.

 

Except, only the first 5 stores appear in ps_stores. I assumed the rest (131) were in there but I don't see a way to scroll down.

[Urbanfarmer]

I was able to upload ps_store_shop ok (132 rows) in cvs, but ps_store keeps giving me this error message and I get no more rows after that. So currently I only display 16 stores on my map. Well, that's progress, but I'm hung up on this error. There is nothing different in line 17. It's all in correct columns and rows in Excel before I save them to csv. 

 

 Invalid column count in CSV input on line 17.

 

It turns out that XML only imports 5 rows and stops. No error message. So it looks like csv is my best bet. In my version of Excel I don't have the ability to save to mysql.

 

Any ideas?

[Urbanfarmer]

I think it's solved. I tried repeatedly to upload in csv and xml, with no luck past 16 rows. I kept getting the error above, and I couldn't see anything wrong.

 

In desperation I deleted the unused/unimportant columns from both ps_store and my csv file to match, uploaded, and I've got all rows and all the addresses on the map.

 

I'm so proud of myself! 

[osdznr14mm]

Congrats! Getting used to this stuff eh? Are all the proper table cells there? Sorry I wasn't around to help you, I've been real busy. About that error, there might be some punctuation mark on line 17 that it is interpreting as a separator so it thinks you've got an extra column in there. Excel could be adding weird characters in your csv or something. Here are a couple threads that might give you some ideas:

 

http://stackoverflow.com/questions/16675355/invalid-column-count-on-line-1

 

http://stackoverflow.com/questions/12815304/importing-csv-file-directly-into-phpmyadmin

[Urbanfarmer]

"Excel could be adding weird characters...."

 

That was probably it. Deleting those extra columns must have done it. The map is working perfectly. Can't thank you enough for your help. Getting deeper into PS is like peeling an onion. And sometimes I hold my breath.

[osdznr14mm]

Hi Urbanfarmer,

I didn't know you had replied--usually I get replies delivered to my mailbox on my hard drive, but oddly sometimes I don't get them. I'm so happy for you that your store locator works, that's great! Re-reading your post on what you did to make it work, I got a little scared that you deleted some columns from the ps_store table? In the future, I wouldn't alter PrestaShop's table structure, even if you think some columns are unnecessary, it could mess things up. I'm surprised everything's still OK with your store, whew. PrestaShop's database is so complex & intertwined so it could easily get jumbled. 

[Urbanfarmer]

Well, I had the backup file, so I figured if I messed something up I'd just replace ps_store. But thanks for the heads up. In the future I won't do that again.

 

For a newby like me this is all so much like skating on thin ice. You never know when you're going to break through......in a bad way.