Trent Richardson
Now we’re ready to create an Extract to link our Source to our Target. From the main menu go ahead and click Create from the Extract menu.
Creating an Extract
- Step one asks for the name, Source and Target. It would be a good idea to name it the same as your Source to keep up with it. Choose the cooresponding source and our target we created in the previous tutorial. Click Apply.
- The next step is for Batch Parameters. I kept the defaults except Batch_Size, set it to 100. This setting is really up to you. This is 10 by default but runs fine for me at 100. Clicked Apply.
- Step three in my case warns me my provider does not have a media server, which is fine since we will be downloading everything anyway. Click Apply.
- Step four ask about refreshing values. Set this to false. Click Apply.
- Step five is very important. Here is where you tell it which fields go to which database table columns. I generally place my mls number, class, type, status in the first few fields. Also it is a very good idea to write these down! As you create more Sources and Extracts you will want to keep the columns as similar as possible to search across all types of listings. (keep price in the same column so no matter if you’re searching Land or Condos, its always going to be in that column).
- Step six is the same thing but with the image table. You should be able to include all the fields, but keep these the same throughout all of your extracts.
Now we have our Extract. Our next step is to alter our batch file to retrieve listings with an Active Status only, and execute our batch via command line.
- RETS Retrieval with PHP and VieleRETS: Part 1, Introduction
- RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source
- RETS Retrieval with PHP and VieleRETS: Part 3, Creating a Target
- RETS Retrieval with PHP and VieleRETS: Part 4, Creating an Extract
- RETS Retrieval with PHP and VieleRETS: Part 5, Executing the Batch
- RETS Retrieval with PHP and VieleRETS: Part 6, Creating a Cron Job
Related posts:
26 Responses
Trent’s Blog » RETS Retrieval with PHP and VieleRETS: Part 3
07|Feb|2008[...] RETS Retrieval with PHP and VieleRETS: Part 2, Creating a Source [...]
Trent’s Blog » RETS Retrieval with PHP and VieleRETS: Part 2
07|Feb|2008[...] RETS Retrieval with PHP and VieleRETS: Part 4, Create an Extract [...]
David
18|Feb|2008Thanks for the great tutorial Trent. Question: Why not just name the columns in your database something more meaningful than c1,c2,c3? Seems like that just adds a layer of confusion. Wouldn’t it just be easier to set the table up with names that make sense and just take a little more time in mapping them correctly?
trent
18|Feb|2008I agree with you completely. I’m not sure if it will allow it though. When setting it up it doesn’t really give you an option of what the columns are, but it simply refers to them by c1,c2,c3. So by that I don’t know if it will break any sql within vieleRETS. It may do some sort of scaffolding to read the columns names? I would love to know if it breaks myself.
David
18|Feb|2008I just tried it with different names than c1,c2,c3 and it seemed to work fine. It will only allow the columns that you have created in your database, but I don’t see in the documentation where it says you need to name your columns like that. Is there a link that recommends this naming convention? I’m new to this so I’m just wading through it.
trent
19|Feb|2008Excellent, I actually might go back and change mine and change the blog entry as well. Will it allow more than 24 columns? I couldn’t really find much documentation on it and vieleRETS was suggesting c1,c2,c3..c24 so I took it as thats what it wanted.
The entire time I struggled throuh this and once I got it to work for the most part I decided I would atleast document that much in hopes of helping someone else. I will try and keep this tutorial up to date if any more improvements come along.
David
19|Feb|2008Yes, much appreciated. I’m amazed at how little documentation there is out there. If you check the VeileRets Mailing list for February you’ll see that I’ve been hammering them with questions the last few days so I’m definitely getting a better handle on it. I think part of the problem is that they have opted for a mailing list rather than a forum so everyone is in the dark.
Anyway, yes you can have as many columns as you want. Most RETS servers will have hundreds of columns. You would have gotten the c1,c2 naming convention and 24 fields from the auto-create documentation. Viele allows you to automatically create tables with a specified amount of fields and it will name them as such. So that would remove the need to manually create your table of c1,c2,c3. If you read the mailing list you’ll see that I’ve proposed auto-creation using the DBName or something more friendly, yet still automatic and structured.
trent
19|Feb|2008A forum would be much better. Yeah the entire table issue was very ambiguous. Thanks for the follow up.
I actually just noticed a problem with my feed. It seems to be only pulling 2500 listings. I have LIMIT set to NONE and I’ve tried turning pagination on and off but no luck. Also only one image per listing is pulling as well. I don’t know if that is the same problem or not. Did you have this trouble?
David
20|Feb|2008FYI…I answered this on the Viele mailing list.
David
20|Feb|2008There must be a delay before the mailing list updates. Here is what I wrote…
Hello Trent:
That is likely a limitation imposed by your RETS server. I was initially limited to 500 records, but given privileges for unlimited downloads after speaking with the MLS RETS contact.
As for the image issue, uncheck âMedia Multipartâ and see if that fixes the problem.
trent
20|Feb|2008Thanks David for your response. I will run the update again tonight with the multipart option and keep my fingers crossed. As far as the other part I that is they case they just have a limit on my account.
trent
12|Mar|2008As an update to the issue with being cut off at 2500 listings it was in fact by the provider. the Offset Adjustment was what needed to be changed. Each batch had to run in increments of 2500, so the first batch offset adjustment was 0, then 2500, 5000, 7500, etc until its updated all the listings
Brendan
02|Apr|2008Thanks for the excellent tutorial. It helped alot.
I’m not sure if you ran into this, but I’m trying to get an offset in vieleRETS, and I’ve entered 18910 into the batch control file, and viele just says “offset option ignored.” Do you know how to set the offset option in vieleRETS?
Thanks
trent
02|Apr|2008I did have to use offset, but it worked as expected. Are you sure your offset isn’t greater than the number of items? I could see that possibly being ignored.. but not sure. As in the comment above my provider was limiting me at 2500 items per request, so I had to execute the batch with different offset in steps of 2500 starting from 0: 0,2500,5000,7500.. Also pagination may need to be turned on?
Anu
07|Jul|2009Hello Trent,
I am glad that atlast I could find better documentation of vieleRETS. Trent I am having some problems with the table creattion. I am getting the listings downloaded. But the problem where comes is at the table creation. I believe that it automatically creates table while downloading the listings. It create s table for images and the data also inserted into it…But the table for listings is not created so some data insert error is shown..I tried with the mysql query generated at the time of downloading, in phpMyadmin and it returns
#1118 – Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
I am after this problem for the past one week. So if you could help me with this, I would be grateful.. http://idxsync.com/idx/index.php is the link I am working in.. Thanks in advance…Anu..
trent
07|Jul|2009Anu,
The problem could be that the data returned from your idx for that field is larger than mysql allows for a varchar field. By default I believe vieleRETS generates everything varchar(255). Indeed you may unfortunately have to change some columns.
You may also want to check with the mailing list as they’re generally very helpful:
http://mail.crt.realtors.org/mailman/listinfo/viele-dev
Also if you’re savvy at all with php you can find a much smaller php class here:
http://forums.rets.org/showthread.php?t=1208
I’ve not used this as I found it after my last rets project, however I wouldn’t hesitate to give it a go.
And a third option (according to your budget) is RETSBond:
http://retsbond.com/
They offer support and have plans to set it all up for you. I have used this product and can say they were very helpful.
Anu
08|Jul|2009Hey Trent,
Thanks a bunch for the reply….I realized the problem and I could solve it too. Now I am able to download listings and get them inserted into the database. But new problem is while downloading it gets timed out and I get maximum of 30 records downloaded. I get some server time out error message too. can you tell where I need to fix this problem…and how ??? I tried to contact with http://mail.crt.realtors.org/mailman/listinfo/viele-dev but no reply yet… So could you please help me?
trent
09|Jul|2009Do you always get exactly 30 listings? If so the mls provider is probably limiting to 30 and cutting you off. If that is the case you need to use the offset option and recall your script each time by incrementing the offset.. 1-30, 31-60, 61-90, etc..
If that isn’t the case it could be that your server has a short timeout setting. Try upping your timeout setting (in apache i think you can change this in the .htaccess file).
Anu
10|Jul|2009hey trent,
I created source, target and extract. At the first time when i tried with extract, in the datamap , I got all the database fields automatically mapped to the RETS server fields. But when I tried next time with another server, I didnt get any fields mapped and I got it mapped to *Not used* field.. I had to map all the long list of fields to its corresponding mapping field :(. why it happened? any idea?
Anu
10|Jul|2009trent,
one more doubt.. can we rename the database table fields to some descriptive forms..? will it create problems while downloading.. I see that what ever names i cahnge it to, they does not seem to appear in data map page… for eg :: c_list_15 is changed to ‘status’ in the database but it still appears as “c_list_15″ in the datamap…any idea ?
trent
10|Jul|2009I had one user say you could rename the table field names, I didn’t try it though. My guess is that you would have to change it manually in the config file, I don’t remember having that option using the interface.
trent
10|Jul|2009The newer version changed a good bit from the last time I used it, so I’m not sure anymore to be completely honest.
Anu
10|Jul|2009okay… thanks trent… I will try with config… :)
RETS Retrieval with PHP and VieleRETS: Part 4 - Affordable Web Developer - Just another WordPress weblog on Affordable open source web development.
19|Oct|2009[...] post by trent and syndicated here by [...]
sendil
16|Nov|2010hi trent. can you tell me how to install open houses in vielerets. it’s important. please help me. Advance thanks
sendil
25|Feb|2011Hello trent. How are you. can you tell me how to download more than 10 images. it very important reply me soon. Advance thanks…