Discontinued/Classic Products: Listings Manager Add-ons: Importing a database. : Interactivetools.com Forum

Home: Discontinued/Classic Products: Listings Manager Add-ons:
Importing a database.

irishis
User

Dec 11, 2003, 7:21 AM

Post #1 of 21 (14450 views)
Shortcut

Importing a database.	Can't Post

Ok I've had my client buy the program, and I have the files. I've taken their database and have successfully created a delimated file using the upside down !, I know I'll have to redo this to match fields, where exactly?? And after I match files, and I save a copy of the original data file with the program. What do I save the new data file as? Is there a tutorial somewhere on this.

Erin

dedbob
User

Dec 11, 2003, 10:42 AM

Post #2 of 21 (14444 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

Your new database has to match how you build your database in Listings Manager under the "Setup Listings Fields" area. If you've already created the delimited file, then just set your database up in sequence. If your first field is the name, then make your first database field the name and so on and so on.

The first 14 fields are used for other things in the listings.dat.cgi file, so I'm not sure how you're going to get all of that info in there. It contains the user#, listing#, post/modify times, and photos to be displayed. Here's an example of a line in mine:

16¡8¡1030896368¡1057034964¡16_1.jpg¡16_2.jpg¡16_3.jpg¡16_4.jpg¡16_5.jpg¡16_6.jpg¡16_7.jpg¡16_8.jpg¡16_9.jpg¡16_10.jpg¡Your database fields will start here¡

As for what you save the new file as, I would assume you would save as listing.dat.cgi because that's the file that holds the listing info :-)

Hope this helps.
Scott

Need ImageMagick? GD.pm? You need to [url "http://www.getstacked.com"]GetStacked[/url]!
Listings Manager friendly [url "http://www.getstacked.com"]Cheap Web Hosting[/url] only $5.95 a month!

(This post was edited by dedbob on Dec 11, 2003, 10:46 AM)

irishis
User

Dec 11, 2003, 12:52 PM

Post #3 of 21 (14428 views)
Shortcut

Re: [dedbob] Importing a database. [In reply to]	Can't Post

THank you Scott you make it sound easy Crazy

Really. I'm itching to get started. Thank you for telling me the file name that must be the one I didn't look at, I couldn't find one that had data in it.

Wish me luck, here I go! Blush

Donna
Staff / Moderator

Dec 11, 2003, 12:57 PM

Post #4 of 21 (14425 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

I think Scott has summed it up pretty well. :) The only other bit of advice I have is... make backups! Lots and lots of backups. :)

There's no existing tutorial for this, it's basically a matter of experimentation. Keep in mind that this is very advanced Listings Manager work you're doing.

I hope this helps! :)

Donna

--
support@interactivetools.com

irishis
User

Dec 11, 2003, 1:26 PM

Post #5 of 21 (14422 views)
Shortcut

Re: [Donna] Importing a database. [In reply to]	Can't Post

Donna is it ok to ask minor questions about the importing here? Like is the first field pre-assigned by the listings manager or can I modify it to match the item number of a particular listing? I see the code is this

1¡Listing Name¡1¡1¡Item Number¡1¡1¡Price¡1¡1¡Preview Description¡2¡1¡Full Description¡5¡1¡Availability(,In Stock,Backorder,Call Us,Discontinued)¡3¡1¡Sizes (Small,Medium,Large,Extra Large)¡4¡1¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Specialty¡1¡1¡Background¡2¡1¡Phone¡1¡1¡Fax¡1¡1¡Cellular¡1¡1¡Pager¡1¡1¡Email¡1¡1¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡asdf¡asdf¡02068-EDI-0792330-93389¡1¡1¡1¡1¡60¡9999¡10¡10¡10¡1¡Listings Manager¡¡¡../images¡../listings¡¡../homepages¡¡¡add¡0¡add¡0¡0¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡0¡2.51¡300¡3148¡0¡1¡html¡0¡160¡120¡generic¡Generic¡Listings Manager¡¡../templates/_generic/¡1¡2¡3¡4¡5¡6¡7¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡1¡2¡3¡4¡5¡6¡7¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡

There seem to be a lot of extra fields that aren't defined "¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡" like these. Are there ones that are off limits for changing and must be kept intact for database function? Am I asking to many question? Thank you for so much help already.

Eric
Product Specialist

Dec 11, 2003, 1:27 PM

Post #6 of 21 (14422 views)
Shortcut

Re: [Donna] Importing a database. [In reply to]	Can't Post

Hi Erin,

Thank you for the forum post. Hats off to Scott for helping out.

One suggestion I would add is to add only a few records the first time to test it out first. Make sure the information is intact and it is updating properly. Then back it up. Add the rest of the records, and test it again. As Donna said, backing it up often really helps in case you get into a jam.

If you don't mind spending a little cash to help make this easier, you may want to consider using a powerful text editor such as UltraEdit. Interactivetools.com uses UltraEdit and personally I like it.

Let me know how it goes, and Happy Holidays to everyone!
Cheers,
Eric Lau - Product Specialist
support@interactivetools.com

Eric
Product Specialist

Dec 11, 2003, 2:24 PM

Post #7 of 21 (14416 views)
Shortcut

Re: [Eric] Importing a database. [In reply to]	Can't Post

Hi Erin,

Thank you for the forum post. I didn't realize you posted when I submitted the previous reply.

From my understand, there are some fields that are automatically generated by Listings Manager and should not be modified. There are also some fields that, although not defined, you still need the separator to make it work. I do not know the details because you need a solid understanding of the source code to figure it all out.

The best advice I can give is to manually add some mock data, record exactly what you entered for each field in each record (make them distinct enough so you recognize them right away), and see how they are placed. Document the separators and what is stored in each field, and you can use that as a reference when importing.

Let me know how it goes. Whenever I can, I am here to lend a hand Smile

.
Cheers,
Eric Lau - Product Specialist
support@interactivetools.com

(This post was edited by Eric on Dec 11, 2003, 2:25 PM)

irishis
User

Dec 14, 2003, 4:56 PM

Post #8 of 21 (14389 views)
Shortcut

Re: [Eric] Importing a database. [In reply to]	Can't Post

Well I've just about pulled all my hair out. I've tried creating a small delimited file with the 14 blanks in front, my database and then a whole lotta, I think it's 138, blanks behind. I've used the ¡ to seperate my fields and the blanks I've matched what I entered for fields but still it doesn't work. One of the hardest things is all the empty ¡ containers. These are impossible to add as far as I can tell except manually in a text editor...I have 1000 listings! Am I missing something. I've also made sure the permissions are correct, I republish and ........nuttin' honey!

Any ideas? Donna, Eric Scott??? Scott if you've done this can you shed any light on the programs you used, the amount of records you imported etc...

Thanks again to all who continue to help. I've gained so much info from these forums, their users and their moderators.

Donna
Staff / Moderator

Dec 14, 2003, 5:18 PM

Post #9 of 21 (14387 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

Hi there!

Hmm, curious... does anything at all show up in the Listings Manager database? Does it come back formatted terribly, or just nothing at all? How many listings does Listings Manager report? Can you post a short example of your listing.dat.cgi for us to look at?

Thanks. :) I hope we're able to give you a hand with this!

Donna

--
support@interactivetools.com

Durham_rm
User

Dec 14, 2003, 7:31 PM

Post #10 of 21 (14378 views)
Shortcut

Re: [Donna] Importing a database. [In reply to]	Can't Post

Hi there,

just thought I would toss in my 2pence worth on this one.

Have imported rm listings data into an Access 2000 database.

Downloaded the listing.dat.cgi. Openned it in Textpad, free version available online, took out the perl header and used the find and replace to convert the upside down exclamation delimiters with semi colons ( no semi colons are used in site data but probably pipes may be as good if data did have semi colons)

Using the Access database import function, take the file in and make a table. You could then make a query taking in all fields from that table thus keeping the field layout intact and then from there it is possible to import data from another table by including that table into the query and making it the source for selected fields as you wish. In that way the data from the second table can be added to the data you downloaded from the site. I should say that I am able to relate my two tables as I have a unique Identifier field common to both. (customer number)

The query containing data from the site and new data from database was then exported through the Access export function using the appropriate delimiter style, the text file was give the required perl header, renamed accordingly and uploaded.

It worked fine. Please note I did not create new records so did not need to tangle with any of the data rm/ presume list manager creates although I think that would be quite possible.

Haven't tried with Excel but imagine you could do something similar

durham_rm

irishis
User

Dec 14, 2003, 8:09 PM

Post #11 of 21 (14376 views)
Shortcut

Re: [Donna] Importing a database. [In reply to]	Can't Post

OK here's what I know, I replaced the listings.dat.cgi and I know it's replacing the right field because it went from 2 listings, which I entered manually to showing no listings. I had actually uploaded 1 listing, it looked like this:

#!/usr/bin/perl
print"Content-type: text/html\n\n<html></html>";
__END__
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡

Should I be leaving out the end "¡" s? Gosh wouldn't that be nice!

I do make sure the permission is 777

There are no listings showing up in the search either. So something is wrong with the above piece.

Thanks for your help Donna!

dedbob
User

Dec 15, 2003, 12:43 AM

Post #12 of 21 (14368 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

I haven't actually done an import like you're trying to do, but my guess is that you're missing the beginning fields, particularly the first 4 which include the listing number, which Listings Manager will need to publish the listings. The listing # is what the actual page is generated from I think, and that IS the first field. So listing #1 would be l0001.html and so on and so on.

Try doing this... I'd bet $$$ that it will work. (I removed the extra "¡"'s at the end for simplicity). Of course, just make the contents of each line in the database unique. I duplicated your field contents several times because that's all I have to go off of.

1¡999¡1069103609¡1069129566¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡
2¡999¡1069103609¡1069129566¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡
3¡999¡1069103609¡1069129566¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡
4¡999¡1069103609¡1069129566¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡
5¡999¡1069103609¡1069129566¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡
6¡999¡1069103609¡1069129566¡¡¡¡¡¡¡¡¡¡¡1005¡OST¡Oklahoma¡Capitol¡WAO 595¡LP¡1955¡1959¡VG+¡VG+¡$5.00¡¡¡¡¡¡¡¡¡¡¡¡

Don't worry about the 3rd and 4th fields so much but just make sure they are there like above. Those are the timestamps and the second field, 999, is the user # (administrator by default). If this doesn't work, then let me know... I'll take a look at everything you have and see what I can do with it. I'm confident that we can help get this working for you Smile

By the way... can you give us your url?

Thanks!
Scott

Need ImageMagick? GD.pm? You need to [url "http://www.getstacked.com"]GetStacked[/url]!
Listings Manager friendly [url "http://www.getstacked.com"]Cheap Web Hosting[/url] only $5.95 a month!

(This post was edited by dedbob on Dec 15, 2003, 12:49 AM)

showme
User

Dec 15, 2003, 1:54 AM

Post #13 of 21 (14361 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

Hi Erin,

If Scott is not able to help you I might be able to write you a custom script to import your pre-existing database via a web-browser.

---------
http://www.ShowMeSites.com
Designs starting at $5
Free Web Hosting

irishis
User

Dec 15, 2003, 5:18 AM

Post #14 of 21 (14356 views)
Shortcut

Re: [dedbob] Importing a database. [In reply to]	Can't Post

Scott in the immortal words of the Partridge Family, I think I love you! I imported what you did, taking out the ending '¡'s except for the one ending the last thing I did in the database. I left in all the beginning fields. Needless to say.....it worked!!! Thanks for making my day! heck lets make it my week! I'm sure I'll be back with more little glitches but I'm so darn happy I could spit!

Erin

irishis
User

Dec 15, 2003, 11:19 AM

Post #15 of 21 (14345 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

Just to catch you up I've converted, uploaded and it's working! All 1100 listings. I learned a few things thanks to you all and a Access forum I found. Adding those 14 fields in front was necessary, adding the primary key in the first field, and the admin codes in the 3rd and 4th fields was also necessary. The great news is the end ones(138!) did not need to be entered. I learned how to fill a whole field with the same thing all they way down(it's not as easy as it sounds) you have to do an update query. All and all a very good day. A BIG THANK YOU TO YOU ALL! Smile

Erin
Irishis Website Design

Donna
Staff / Moderator

Dec 15, 2003, 1:24 PM

Post #16 of 21 (14337 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

Hi Erin! That's fantastic! I'm so glad to hear that you got everything imported. :)

I look forward to seeing your site when it's up & running. :)

Donna

--
support@interactivetools.com

dedbob
User

Dec 15, 2003, 4:05 PM

Post #17 of 21 (14331 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

That's great Erin! Thanks for your kind words... though I'm kind of concerned about the spitting part Tongue

.

So.... let's see the site! U-R-L! U-R-L! U-R-L! U-R-L! U-R-L! U-R-L! Wink

Thanks!
Scott

Need ImageMagick? GD.pm? You need to [url "http://www.getstacked.com"]GetStacked[/url]!
Listings Manager friendly [url "http://www.getstacked.com"]Cheap Web Hosting[/url] only $5.95 a month!

irishis
User

Dec 15, 2003, 4:26 PM

Post #18 of 21 (14327 views)
Shortcut

Re: [dedbob] Importing a database. [In reply to]	Can't Post

There really isn't a site yet. The database was the big hurdle, next will be the shopping cart after I finish designing. I'll send the url with it all put together, it will be an online vintage album shop, with a brick and mortar store here in Toledo Ohio. Thanks again!

Adam M
Novice

Dec 16, 2003, 1:27 AM

Post #19 of 21 (14306 views)
Shortcut

Re: [Donna] Importing a database. [In reply to]	Can't Post

This would be a trivial action if the product were MySQL-based...

Eric
Product Specialist

Dec 16, 2003, 2:18 PM

Post #20 of 21 (14282 views)
Shortcut

Re: [irishis] Importing a database. [In reply to]	Can't Post

Hi Erin,

Thank you for the updates! Cool

I am so glad it all worked out for you. Definitely send us an URL when you are done so we can all admire your hard work.
Cheers,
Eric Lau - Product Specialist
support@interactivetools.com

(This post was edited by Eric on Dec 16, 2003, 2:19 PM)

Eric
Product Specialist

Dec 16, 2003, 5:20 PM

Post #21 of 21 (14277 views)
Shortcut

Re: [Adam M] Importing a database. [In reply to]	Can't Post

Hi Adam,

You are right. If Article Manager uses MySql, importing and exporting data would be simplier. Article Manager currently does not use MySql. While I cannot guarantee anything or provide any timelines, hopeful there will be a future version that supports MySql.
Cheers,
Eric Lau - Product Specialist
support@interactivetools.com

interactivetools.com

Company Info

Contact Us

Newsletter

interactivetools.com

Company Info

Contact Us