Gossamer Forum
Home : Products : Gossamer Links : Discussions :

import data

Quote Reply
import data
I have a database (excel spreadsheet) with 2,000 links that I would like to import into LinksSql

how do I do this?Unsure

any advice or help please.
Colin Thompson
Quote Reply
Re: [colintho] import data In reply to
Export from excel into a delimited file and then use MySQLMans import feature.
Quote Reply
Re: [Paul] import data In reply to
thanks, Smile

I'll try that on monday morning.
Colin Thompson
Quote Reply
Re: [colintho] import data In reply to
I wanted to ensure that I didn't "screw" this up - so I wanted to do a "trial" using just a small amount of data - so that I get the data format correct / procedure correct etc..

(I'm assuming that the imported data should be in the same format - field names etc..?)

I logged into my admin area - went to the Links table and did an export to see what the data looked like / what sort of format the data was in.

exported my data as listoflinks.txt (only 7 links)

could see that the data is delimted by this "|"

eg.....lsql_Links.ID|lsql_Links.Title|

in Excel - imported the data from listoflinks.txt - with a view to "adding a few records" and then I was hoping to save file as "delimited file type" - then import to my server LinksSql - build changed? (not sure) then look to see if the additional records were correct.

Excel - saving file - only see save as text file with tab / space or comma delimiter

In MySqlMan - could see import data with delimter option showing this "|"

need some help pleaseUnsure

on importing data - does the imported data need to include the records already in the database?

or does LinksSql "add" the new imported records to the database?
Colin Thompson
Quote Reply
Re: [colintho] import data In reply to
Hi!

I have less knowledge of Excel. I used Acess. Since this is actually a real database program, has also many export features. I would STRONGLY recommend it.

In Acesss, you can actually save all the configuration what you have dereived during your experiment of export and then import. What you did, I did the same. So whan you do the real thing, you can simply use the import and export specification saved earlier.

Further, during the test, you may have a format of the table. This would be also there and you can save the format into anathor one serving it like a table template in relation to your database.

If you have problem with the delimiter in Excel, then let it be in the export. In there, you can your search and replace function and replace it with a delimiter. This is not always good as comma seperated is not the very best form of delimiter, although it serves for many other data-types.

My other advice is to use Links SQL import feature, if you had your links already in an earlier format. I am not clear in telling you this, since I do not know what is the format. May I tell you the difference.

MySQLMan will do the import like Links SQL import. The Links SQL will also import the category information of each link, if it is there in the line of the text file and insert into the CatLinks table. This table is to store information of each link and its category. Links SQL import will create a LinkID number being inserted and then also insert that LinkID into Catlinks table after getting the ID of the category it belongs.

Using MySQLMan will also do the import but will simply import the links information into the links table. Since it is NOT a specific program for Links SQL, but a general one, it cannot import the category information of each link.

Hence when you import from a datafile.txt, the data is actually imported that is inserted into the tables. Thereafter, you need to click on repair tables functions like repair search. This will build anathor table and insert keywords into it so that the searches are faster, if you have selected indexing method internal (of Links SQL, i.e.).

Then you can use Build all function, since it is not actually modification of each link but simply transporting them from one format to anathor. Build modify is used only when a link field Modify_date is greater than Add_date, i.e. if it is actually modified. Build_Staggered will also work with the same build_all differing in time of building the pages.

By the way, if you do not have category information in the link file imported, is also not a disaster, because it will offer you as orphan links. Then you need to go to each and then modify them placing each one into their respective categories. Obviously a tedious work when you have to click x times 2000 links!!!

Last edited by:

dearnet: Jan 14, 2003, 1:10 PM
Quote Reply
Re: [dearnet] import data In reply to
thanks for the help.Smile
Colin Thompson
Quote Reply
Re: [colintho] import data In reply to
Welcome!!!
Quote Reply
Re: [dearnet] import data In reply to
I've came across this a long time ago and the solution I found was to ADD blank colums in-between your data columns in the Excel worksheet, and fill those cells with the | (pipe) character.

When exported as plain text they look the same as a pipe delimited file would.

Hope this helps.

All the best
Shaun
Quote Reply
Re: [qango] import data In reply to
thanks

looks like a good solution - alternative to "find - replace" in text editor.
Colin Thompson
Quote Reply
Re: [qango] import data In reply to
Ha! That`s clever!!!