Tutorial: Links in Excel to DB

[phoenixdown]

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.

********

Step 1:

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.


Step 2:

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:
http://www.vbadvanced.com/links/browselinks.php?c=2


Step 3:

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.


Step 4:

After you add everything - save the workbook somewhere just because its a good idea.


Step 5:

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.


Step 6:

Repeat step 4 but do it for the links_customfields_entries tab.

Tip: if you dont have any custom fields, just ignore this.


Step 7:

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"


Step 8:

Hit Browse and locate the "links.csv" file you saved.

Where it says "Fields terminated by" change that to a comma.

Hit submit.

I think you should be done.


Step 9:

Your going to repeat the steps 6 and 7 for the links_customfields_entries table (links_customfields_entries.csv file).


Step 10:

Thats more or less it - you should be good to go.

[aacircle]

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.

[phoenixdown]

Good to know. Thanks for sharing the tip.

[johnstires]

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:

Invalid field count in CSV input on line 15.

I keep getting it for different lines and I can't narrow it down as to why I am getting that error as "line 15" is similar to others.