×

Notice

The forum is in read only mode.
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC: import data from excel through phpmyad v 2.0.5

import data from excel through phpmyad v 2.0.5 5 years 3 months ago #16485

  • Eliecer Marchante
  • Eliecer Marchante's Avatar Topic Author
  • Offline
  • Platinum Boarder
  • Platinum Boarder
  • Posts: 493
  • Karma: 11
  • Thank you received: 13
FIRST PART

To follow this tutorial you will need:
- OpenOffice.org 3.3.0 Calc
- Notepad ++
- Access to your website's PHPmyadmin


- Go to your PHPadmin and Select Databases



- Select the database you are using with hotspots


- These are the elements related with hotspots


- Click on Browse


-These are the fields to use with the OpenOffice.org Calc,



but they must be in these order:
id - catid - name - street - plz - town - country - gmlat - gmlng - description - description_small - picture - picture_thumb - autor - autorip - autoruserid - postdate - vote - votenum - published



The id and the catid, will match the category id number.
plz corresponds to the postal code
published - value 1 (the hotspot will be published), and value 0 (the hotspot won't be published)
The following user(s) said Thank You: Daniel Dimitrov, Sherzod Kamaldinov, Joe Brickley

import data from excel through phpmyad v 2.0.5 5 years 3 months ago #16486

  • Eliecer Marchante
  • Eliecer Marchante's Avatar Topic Author
  • Offline
  • Platinum Boarder
  • Platinum Boarder
  • Posts: 493
  • Karma: 11
  • Thank you received: 13
SECOND PART

- Once you finish to populate the table just go to File/Save As/
and select:
- Save as type: Text CSV (.csv) (*.csv)
- Character set: Western Europe (Windows-1252/WinLatin 1)
- Field delimiter: ; <
it's originally a ,
- Text delimiter: "
- Check: Save cell content as shown
- Uncheck: Fixed column width
- Click Ok
(I only filled the first 7 fields, and the last one, I left the rest empty)


- Open the CSV file with Notepad ++
- Now, we will need to add / to the end of the line
- We need to replace: ;;;;;;;;;;;;;1 with ;;;;;;;;;;;;;1/ 
- or ;;;;;;;;;;;;;0 with ;;;;;;;;;;;;;0/
  depending if you published the hotpots (1) or not (0)
- To replace everything, we go to Search/Replace...
- Now, we have to save the file (Go to File/Save)

- Let's go back to PHPmyadmin

- Select jos_hotspots_marker

- Select Import
- Choose the CSV File


- Check CSV using LOAD DATA
- Check the options and leave them in the way I am showing


- Click on Go

- The database has been populated with the new information.

and don't forget to do what Daniel said:

In 2.0.5 there is a geolocation feature. Go to the marker page in the backend, select max 100 hotspots click on geolocate, read the instructions and click geolocate in the popup After few seconds, or minutes most of your hotspots will get some coordinates.
The following user(s) said Thank You: Joe Brickley

import data from excel through phpmyad v 2.0.5 4 years 11 months ago #18231

  • Attila Gáspár
  • Attila Gáspár's Avatar
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 6
  • Thank you received: 0
Hi Mr. Marchante!

I noticed that in version 3.2.1 there is an asset_id column which is a FK to the table #_assets. If i'm right, it's for defining the permissons for hotspots. But i don't see any difference if the connected records in _assets table are exists or not. Either way the hotspots have the next settings in the backend:

Delete - Inherited - Not Allowed
Edit- Inherited - Not Allowed


Could you explain how to handle it or do i need it at all? What if i just don't create the connected records in the _assets table?

Thanks in advance,
Attila

import data from excel through phpmyad v 2.0.5 4 years 6 months ago #20086

  • Glenn MacKenzie
  • Glenn MacKenzie's Avatar
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 1
  • Thank you received: 0
Thanks for the fine work importing hotspot data.

I'm wondering how to setup the hotspot categories table.

There are 2 fields that I'm not sure about:
cat_icon and cat_shadowicon. There's a unique number attached to each record.

Here is the field data (for those two fields) for one record:

1364509634_S_petsdark.fw.png
1364509634_S_shadow.png

I can create the other data, but its the number (1364509634) in those two fields that I'm not sure about. If I'm going to use the tutorial for importing csv data, how would I create those numbers or can I?
I have a large number of categories to create and very few hotspots. (My users will create the hotspots at the frontend.)

thanks,
Glenn

import data from excel through phpmyad v 2.0.5 4 years 6 months ago #20088

  • Eliecer Marchante
  • Eliecer Marchante's Avatar Topic Author
  • Offline
  • Platinum Boarder
  • Platinum Boarder
  • Posts: 493
  • Karma: 11
  • Thank you received: 13
The tutorial was create to hotspots v. 2.0.5
I am still using that version, and I am not planning to switch to hotspots v. 3.x by the moment.

The structure of the database's tables were modified with the new version, and in order to make the tutorial works, you will need to modify it considering the new format of the tables.

import data from excel through phpmyad v 2.0.5 4 years 6 months ago #20091

  • Daniel Dimitrov
  • Daniel Dimitrov's Avatar
  • Away
  • Administrator
  • Administrator
  • Posts: 9618
  • Karma: 154
  • Thank you received: 1081
Hey Guys!
As Eliecer said you'll have to modify the columns a little for it to work with 3.0. However there icon and shadow fields should not be of any concern to you. When create the category we generate this random number for the shadow and icon to make sure that if you upload different files with the same name they won't get overriden.
If you put my_icon.png then the system will search for the icon in media/com_hotspots/images/icons/my_icon.png -> it won't look for randomly generated string...

Regards,
Daniel
The following user(s) said Thank You: Glenn MacKenzie

import data from excel through phpmyad v 2.0.5 2 years 9 months ago #29660

  • Joe Brickley
  • Joe Brickley's Avatar
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 4
  • Karma: 1
  • Thank you received: 0
Hey Daniel,
It would be really amazing and useful to have a cvs import tool added to the hotspots pro front end dashboard !
Or even an update from Compojoom to Eliecer Marchante's import guide covering any necessary changes to columns etc for Hotspots pro 4.3.3 . Its such a useful function.
Many Thanks
Joe
  • Page:
  • 1
Time to create page: 0.184 seconds