Tutorial: Links in Excel to DB
I was browsing around and I saw a few posts where people were looking to add links from an excel file into the links directory.
Hopefully Links 2.0 will have this as a feature but in the meantime here is a manual way of adding links from excel into the database.
This little walk through assumes you have a basic knowledge of how to work with data in excel although its really shouldn't be too bad.
btw - this is a walkthrough if you dont have any existing links already. If you already have links in your db, you can find out the ID # of the last link by viewing hte latest links in your directory. If the last id is 420, then change the template to start with 421, etc.
Make a backup of your database. If you mess something up and you dont have a backup, you'll regret it. So lets just make one.
Go into your links directory admin panel and create all the catagories you need along with any custom profile fields that you want to have.
Make sure to write down the catagory ID's and custom profile field ID numbers.
You can find out the catagory ID #'s by looking at the URL.
For example the catagory id # below is 2:
Download the attached excel workbook. This is a TEMPLATE for you to use. Btw, make life simple and dont use commas.
Add or copy/paste the info you want into the "YOUR ENTRIES" tab. This info will automatically be added into the other right places on the other two tabs.
I provided 50 entries already, but if you need more, just copy and paste the last line on all three tabs.
If you don't use all 50 entries then make sure to delete the unused lines from all three tabs since it will add a lot of nothing to your directory which wouldn't be very useful would it?
You will need to do the same with the custom field columns in the first and last tabs for the same reasons. I added 5 fields but you may have more or less depending on your own needs.
This is where the basic understand of excel comes in btw.
After you add everything - save the workbook somewhere just because its a good idea.
Click on the "links" tab and go to File -> Save As. Choose to save it as CSV file.
It will give you a warning saying that a csv doesn't support multiple sheets, just hit okay.
It will give you another warning saying that csv doesn't support formatting, just hit okay.
Repeat step 4 but do it for the links_customfields_entries tab.
Tip: if you dont have any custom fields, just ignore this.
Go into P-H-P-M-Y-A-D-M-I-N
Click on the Links (or your prefix_links) table.
In the window that comes up, click on the SQL tab at the top.
Scroll down to the bottom and click on "Insert data from a text file into the table"
Hit Browse and locate the "links.csv" file you saved.
Where it says "Fields terminated by" change that to a comma.
I think you should be done.
Your going to repeat the steps 6 and 7 for the links_customfields_entries table (links_customfields_entries.csv file).
Thats more or less it - you should be good to go.
Re: Tutorial: Links in Excel to DB
The phpMyAdmin feature you are trying to use unfortunately does not work on many servers because it requires MySQL FILE
privileges which are inappropriate in a shared server environment because they give the same level of access as the MySQL daemon itself (therefore giving one client access to every other clients' databases).
Rather than using the method you mentioned, you can instead upload your SQL file (e.g. dump.sql - a file containing all the SQL
commands you want to run) and then log in to telnet/SSH and run the SQL using the command:
mysql -u Username -pPassword DatabaseName < dump.sql
Note: There is purposely no space between -p and Password.
If this is all too technical for some, then you can use a great program at www.navicat.com. You get a 30 day free trial with this software.
Last edited by aacircle; 12-16-2005 at 10:35 PM.
Re: Tutorial: Links in Excel to DB
I've been trying to follow this tutorial and it works pretty well for the most part. However, the MYSQL (step 7 and beyond) are not the same for everyone. My Myphpadmin lays everything out differently. I'll try to post how I was able to import this info when I am able to figure it out. Right now I am getting an error:
|Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)|
|Thread||Thread Starter||Forum||Replies||Last Post|
|Mod: Quick and dirty way to add screenshots of submitted links to your links Dir...||theMusicMan||Modifications||9||01-28-2007 02:13 PM|
|vBadvanced Links Directory vs Andrew's Links and Files Database||Lizard King||Pre-Sale Questions||4||11-14-2005 08:48 PM|
|Importing Links in Excel to vBa Links||gyankow||"How Do I..." Questions||1||10-15-2005 06:05 PM|