Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

LOAD command for importing data

Quote Reply
LOAD command for importing data
One of the questions people have had is how to import data into Links. MySQL has a LOAD command that takes care of that. The basic syntax for a Links 2.0 import file would be:

Code:
LOAD DATA [LOCAL] INFILE '/full/path/to/file'
[REPLACE|IGNORE] INTO TABLE Links.Table
FIELDS TERMINATED BY '|'
(comma,separated,list,of,fields,you,are,importing)
;

REPLACE will over write the existing data, IGNORE will ignore the new values and keep the old.

The [LOCAL] parameter is necessary if you are on a remote machine, and doesn't hurt if you aren't.

There are other delimiters for enclosing fields and ending lines, the defaults work fine for Links 2.0 no ' or " in string fields only | as field terminator.

Hope this helps someone.



------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/








Quote Reply
Re: LOAD command for importing data In reply to
Thanks for the info. I still haven't been able to import my data into Links SQL.

I tried your suggestion using the SQL Monitor and the [REPLACE] option. For my specific data it looked like:

LOAD DATA [LOCAL] INFILE '/u/web/studya/tester.txt'[REPLACE] INTO TABLE Links.Table FIELDS TERMINATED BY '|'
(ID,Title,Title_J,Title_J_Nolink,URL,Destination,CategoryID,Description,Description_J,Contact_Email,School,Address,Country,Tel,Fax,Prefix,Fi rst,Last,Position,Emailform,Priority,Paid,Japanese_Web,Add_Date,Mod_Date,Contact_Name,Hits,Rating,Votes,Status,Date_Checked,isNew,isChanged, isPopular)

When I tried this I got the following error:

Query Error: You have an error in your SQL syntax near '[LOCAL] INFILE 'u/web/studya/tester.txt'[REPLACE] INTO TABLE Links.Table FIELDS ' at line 1

Any idea what this might be? I'm probably missing something obvious here.

-FM
Quote Reply
Re: LOAD command for importing data In reply to
<G> ... the [LOCAL] is the problem... the '[' means its' an optional parameter. Just use LOCAL and EITHER REPLACE or IGNORE (not both)

Code:
LOAD DATA LOCAL INFILE '/u/web/studya/tester.txt' REPLACE INTO TABLE Links.Table FIELDS TERMINATED BY '|'
(ID,Title,Title_J,Title_J_Nolink,URL,Destination,CategoryID,Description,Description_J,Contact_Email,School,Address,Country,Tel,Fax,Prefix,Fi rst,Last,Position,Emailform,Priority,Paid,Japanese_Web,Add_Date,Mod_Date,Contact_Name,Hits,Rating,Votes,Status,Date_Checked,isNew,isChanged, isPopular)


For general information something written like [LOCAL|FOREIGN|ALIEN]

Means it's an optional parameter and you use LOCAL or FOREIGN or ALIEN The brackets just group the set, and usually mean "optional".

Sorry about that..... programmer shorthand.


Oh!! One more thing... the "Links.Table" is the way MySQL represents DATABASE.tablename, so if you were importing into the standard main "Links" table that was in a database called "Links" it would be:

...TABLE Links.Links ....

To import into the Validate table (as long as it had all the fields) you would use:

...TABLE Links.Validate

The '.' links the database name to the table name, so you can even use this to copy across databases using the INSERT INTO command:

Code:
INSERT INTO Database1.Tablename1
(comma,list,of,fields)
SELECT
(comma,list,of,fields)
FROM Database2.Tablename2
ORDER BY fieldname

Hope that helps Smile
------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/


[This message has been edited by pugdog (edited January 12, 2000).]
Quote Reply
Re: LOAD command for importing data In reply to
BTW.... the full documentation for MySQL is really great to have, and you can find it at:

http://mysql.org

Once you get there, do a search for the LOAD command, and you'll see all the available options and syntax.

I thought it was useful to point out, since it does bypass all the Links features, but it inserts the data right into the database. It doesn't do any error checking (other than minor field type) and you do need to REINDEX the database after using it.

I'm using it successfully on a number of databases and applications, and it's a general solution (albiet a technical one) for some sticky problems.

Quote Reply
Re: LOAD command for importing data In reply to
Pugdog:

I have had a problem that keeps popping up in regards to importing links, and I was hoping you might be able to answer whether the LOAD command will fix this.

If you have 100 Categories, and delete #3, export the data and re-import, # 4 becomes # 3, # 5 bevcomes # 4, etc. Will LOAD Keep the ID Number from the file on Import, or will it re-number the order, thus screwing up all the category links.

Does this make sense?

Dave
Quote Reply
Re: LOAD command for importing data In reply to
Dave,

Hmm, that's a good point. I think you can avoid this if you use the Quick Import and Quick Export (which are a wrapper for the mysql LOAD DATA and SELECT INTO OUTFILE SQL calls).

I'll have to polish those up a little more.

Cheers,

Alex
Quote Reply
Re: LOAD command for importing data In reply to
Alex:

Good- I am NOT crazy! Unfortunately, I have never been able to get Quick Import to work, I always use the other method (and therein is my problem!)

Dave
Quote Reply
Re: LOAD command for importing data In reply to
So:

If I use Pugdogs LOAD Import, will it re-number (starting at 1), or use the ID numbers that already exist in the Import file?

Dave

I want to try the new Parse_RDF.pl, but not until I can be sure I can Import and Export the existing data...

Also, the new Parse_RDF.pl will start adding categories to the END of mys links, right?
Quote Reply
Re: LOAD command for importing data In reply to
Ok,

The way the auto increment field works is that you can insert any value into the field, as long as it's non-zero and greater than the last loaded value.

So, if you wanted to re-import the links, you need to include the ID field. If you export the database, and re-import with the ID field IN ASCENDING ORDER you should keep the ID values.

If you CHANGE a field to auto-increment, it re-numbers starting at 1

I could be wrong, but this is how it's apparantly worked for me -- since when I imported DMOZ I have categories with 227504 or something crazy like that.

And the re-numbering by changing a field to Autoincrement is something by experience.

I have set up and dumped literally dozens of databases and hundreds of tables trying to figure things out.

To specifically answer your question:

Quote:

If you have 100 Categories, and delete #3, export the data and re-import, # 4 becomes # 3, # 5 bevcomes # 4, etc. Will LOAD Keep the ID Number from the file on Import, or will it re-number the order, thus screwing up all the category links.

The answer should be YES it will keep the fields, as long as you are loading into a NEW database (one that has been fully emptied).

Logicially, this would have to be so, since most people trying to backup/restore a database would not want values of any field arbitrarily changed.

The key is that values must ASCEND so links must be exported/imported in numerical order _AND_ the database must have been emptied to reset the counter to 0

Actually, that's been my experience, the "official" word from the docs is:

Quote:
An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 20.4.29 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused. If you delete all rows in the table, the sequence starts over. Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query:
SELECT * FROM tbl_name WHERE auto_col IS NULL

What this is saying, is you can insert any integer value except '0' directly into the auto_increment field. If you try to enter a value of '0' or NULL the next-highest value is taken. Values _can_ be reused, but are implied (at least) to be unique.

What I haven't seen in the docs is what happens if you try to insert a duplicate key value -- do you get an error message and a fail? Do you get a successful unsertion? Or does it increment the highest value by one, and continue (but by doing that screw up all subsequent values).

My guess is the first -- an error message and a fail.

I'll have to try it Smile



Quote Reply
Re: LOAD command for importing data In reply to
Pugdog:

Thanks for the reply. I figured out that empty the database myself- been having a lot of fun importing and exporting Smile What I missed is how to turn off auto-increment (sp?) or turn on "force the new ID number..."

I mean if you have been working with a database for a while, you are gonna have holes in the category numbers (right?) So if I export that, my ID numbers will go 1, 2, 3, 5, 6, 9, 10....

So I want to FORCE it to import those same numbers, not 1, 2, 3, 4

OR, is there a way to compact the database BEFORE you export, to get numbers back to the 1, 2, 3, 4 progression (that also updates all the Links?)

Also, for those who care, if you change $CATID and $CATCNT in Parse_RDF.pl to the last Category.ID number, you can Import a second DMOZ set into Links (I wanted Arts/Animation and Arts/Comics, but not Arts/Music...) That got VERY messy!

Thanks P-Man!

Dave
Quote Reply
Re: LOAD command for importing data In reply to
Try using it on the empty database, assigning the existing "ID" to the autoincrement "ID" field (not doing it automatically).

It should import with the old numbers, according the docs.



Quote Reply
Re: LOAD command for importing data In reply to
I tried this thing and it worked but MySQL man brought me back to the login page saying "Permission Denied".
Is my hosting company not alowing me to perfom this task?

Please clear my doubts!

Best Regs
JackofNone

Quote Reply
Re: LOAD command for importing data In reply to
You should ask them. They may have some restriction on loading files from disk.

I have hit a few ISP's who felt this was a "security risk" for some unknown reason... since you can pipe the same thing in via the interactive interface, and there is no additional risk of a file, as long as you already have an account on the ISP....

Ask them before going crazy.

PUGDOGŪ
PUGDOGŪ Enterprises, Inc.
FAQ: http://postcards.com/FAQ


Quote Reply
Re: LOAD command for importing data In reply to
Thanks PUGDOG,
Can you help me to make it the interactive way.
Actually i m not sure with the fields like Escape character, Records, Delimiter etc etc.
Please tell me what do i have to add or remove in the space provided against these feilds
For a look at my database you can visit my post at
http://gossamer-threads.com/perl/forum/showflat.pl?Cat=&lookup=1&Number=113564

Thanks in advance
Best Regs
JackofNone