Advice on Importing MAIL format file...

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • websissy
    Senior Member
    • Sep 2003
    • 189

    Advice on Importing MAIL format file...

    Okay, I'm running vB3.03 happily and have been using the product since beta 7. However, one of my goals has always been to come up with a way to capture the users and messages from my Yahoo group and import them into vB3. As a 35 year IT pro, I'm no novice at data conversions... so I have a pretty good idea what I'm faced with here.

    I have now found and successfully tested a helpful little utility that will login to my Yahoo group and export the member list there into a tab-delimited Ascii file.

    Once the member list is in Ascii, I can easily convert that data into any format and import the users to vBulletin. If I MUST, I can import into MSAccess, download my vBulletin MySQL user table to MSAccess, purge the Yahoo member list of duplicates against my vBulletin user table, port the purged table (via ODBC) directly into the Windows version of MySQL, export a SQL dump from there, upload the dumped file back to linux and import into vB3's database. That's the LONG way around the barn. But I've already done a very similar thing successfully. So, I know it's possible for me to do that.

    Therefore, I assume that if I study this for a while I can figure out how to import my existing Yahoo users into vBulletin correctly. The initial password selection and assignment for those account will be a minor issue; but I'm convinced I can solve that one too.

    My main question has to do with preserving and importing my Yahoo messages.

    I have found two utilities that can download the entire Yahoo Groups Message database and store it on my server in standard linux MAIL format. One utility does this as a single linux MAIL file containing all messages. The other does it as one linux MAIL file PER message. I've tested them and they both seem to work fine. I can even read the messages with a linux MAIL client and they look okay. So far, so good as they say...

    My question is once I retrieve those messages from Yahoo and have them in a standard linux MAIL format, will any of the vB3 importers ACCEPT a standard linux MAIL file(s) and import those messages into the vB3 database?

    If not, does anyone know if any of the free Forum products that ARE supported for import by vB3 (e.g. ikonboard2 or ikonboard3, etc) CAN accept a Linux Mail file format for message importing? If that's possible, I could always take the Linux Mail file and import the messages into another product then use vB3's importer to move them from there into vBulletin.

    Thanks a lot for any help or advice you may be able to offer!
    Last edited by websissy; Mon 22 Nov '04, 1:09pm.
  • Jerry
    Senior Member
    • Dec 2002
    • 9137
    • 1.1.x

    #2
    I've written a Yahoo Groups importer that imports the users as well as the categories, forums and messages.

    Threaded on start date and subject, sorted by replies in to the respective threads.


    There is a function to export a yahoo group as an access database.

    I'm am currently waiting on a test database from a user to be able to test it fully.

    If you have one then I could import your site or I'll wait for the other user to get back to me.

    ImpEx can accept feeds in just about any format, just needs the module written.
    I wrote ImpEx.

    Blog | Me

    Comment

    • Jerry
      Senior Member
      • Dec 2002
      • 9137
      • 1.1.x

      #3
      I've written a file delimited importer too for people who want to import from a text file, though that is still being tested and I'm looking at ways of improving the GUI for it.
      I wrote ImpEx.

      Blog | Me

      Comment

      • websissy
        Senior Member
        • Sep 2003
        • 189

        #4
        Originally posted by Jerry
        I've written a Yahoo Groups importer that imports the users as well as the categories, forums and messages.

        Threaded on start date and subject, sorted by replies in to the respective threads.


        There is a function to export a yahoo group as an access database.

        I'm am currently waiting on a test database from a user to be able to test it fully.

        If you have one then I could import your site or I'll wait for the other user to get back to me.

        ImpEx can accept feeds in just about any format, just needs the module written.
        Hi, Jerry...

        Let me begin by saying I think you've found your guinea pig. If you're willing to work with me, I'm surely willing to tackle this issue which has been a millstone around my neck for at least 16 months now.

        I would describe my Yahoo group as "moderate" in size. It contains about 1,600 members and roughly 6,800 messages. I have (so far) not been able to export the entire message list because Yahoo restricts "interactive message downloads" to some undefined number of messages or amount of data and it bases that limit on IP address. Noone seems to know exactly what the limit is. But when you hit it (as I've done within the last hour) you start to get error messages instead of successful downloads. The problem apparently goes away after a while. So, I'm waiting now to run another test.

        What I DO know so far, is that the limit occurred for me in my first download test at around 950 messages. So, I'm convinced I'll be able to get the entire message database captured here within a few days. Of course, that's in the MAIL forrmat and not in the access format you mentioned...

        Despite my long experience in IT and in administering with Yahoo Groups, you've still managed to mention a couple of things that confused me...

        First you said you've written a Yahoo Groups importer that handles "users, categories, forums and messages". I'm well aware of the users and messages tables in Yahoo Groups. But, I'm completely unaware of any definition of "categories" or "forums" there. Could you elaborate a bit please?

        Next, you mentioned another option... something about "a function to export a yahoo group as an access database." I am completely unaware of any such capability built into Yahoo. Could you give me some more information on this?

        Finally, I have done a brief study of the ImpEx documentation in an effort to understand exactly how the process is designed to work. But I've only scratched the surface there. Could you perhaps briefly describe for me what the process is designed to do and in what sequence? Did I correctly understand you to say that the source database format you expect to receive the Yahoo Groups data in is an access database?

        The bottom line is I'm both eager and willing to work with you on this. And I believe I have the experience on my end to hold my my part of the bargain too. But we probably need to come to a common definition of terms and strategies first!

        Best Professional Regards,
        WebSissy
        Last edited by websissy; Mon 22 Nov '04, 2:01pm.

        Comment

        • Jerry
          Senior Member
          • Dec 2002
          • 9137
          • 1.1.x

          #5
          Originally posted by websissy
          Hi, Jerry...

          Let me begin by saying I think you've found your guinea pig. If you're willing to work with me, I'm surely willing to tackle this issue which has been a millstone around my neck for at least 16 months now.
          Sounds good to me

          Originally posted by websissy
          I would describe my Yahoo group as "moderate" in size. It contains about 1,600 members and roughly 6,800 messages.
          Bigger is better for testing as far as I'm concerned !

          Originally posted by websissy
          I have (so far) not been able to export the entire message list because Yahoo restricts "interactive message downloads" to some undefined number of messages or amount of data and it bases that limit on IP address. Noone seems to know exactly what the limit is. But when you hit it (as I've done within the last hour) you start to get error messages instead of successful downloads. The problem apparently goes away after a while. So, I'm waiting now to run another test.

          What I DO know so far, is that the limit occurred for me in my first download test at around 950 messages. So, I'm convinced I'll be able to get the entire message database captured here within a few days. Of course, that's in the MAIL forrmat and not in the access format you mentioned...
          Then I'll have to have a look at the MAIL format to see how I can get ImpEx to read the data in, before being able to explain it.

          Originally posted by websissy
          Despite my long experience in IT and in administering with Yahoo Groups, you've still managed to mention a couple of things that confused me...

          First you said you've written a Yahoo Groups importer that handles "users, categories, forums and messages". I'm well aware of the users and messages tables in Yahoo Groups. But, I'm completely unaware of any definition of "categories" or "forums" there. Could you elaborate a bit please?
          The test data I've had to date, has organised the groups into seprate tables, which I've been able to extrapolate out forums from. i.e. map the yahoo lists data to a vBulletin forum. I'm not sure if this is something that the customer I'm working with has done, I'm chasing him now.

          Originally posted by websissy
          Next, you mentioned another option... something about "a function to export a yahoo group as an access database." I am completely unaware of any such capability built into Yahoo. Could you give me some more information on this?
          They are using this : http://www.pgoffline.com/index.htm

          Originally posted by websissy
          Finally, I have done a brief study of the ImpEx documentation in an effort to understand exactly how the process is designed to work. But I've only scratched the surface there. Could you perhaps briefly describe for me what the process is designed to do and in what sequence?
          Err, thats a big one .....

          I've covered the basics here in the Overview.

          Originally posted by websissy
          Did I correctly understand you to say that the source database format you expect to receive the Yahoo Groups data in is an access database?
          That is the format I have recived example data in to date, though I can work with just about anything.

          Originally posted by websissy
          The bottom line is I'm both eager and willing to work with you on this. And I believe I have the experience on my end to hold my my part of the bargain too. But we probably need to come to a common definition of terms and strategies first!

          Best Professional Regards,
          WebSissy
          Cool, if you can get me some of the example data in the format you have I'll take a look at get back to you (I'll PM you me email) .

          I suggest taking a look at the link above for the tool that the other customer is using.
          I wrote ImpEx.

          Blog | Me

          Comment

          • websissy
            Senior Member
            • Sep 2003
            • 189

            #6
            Originally posted by Jerry
            I've written a file delimited importer too for people who want to import from a text file, though that is still being tested and I'm looking at ways of improving the GUI for it.
            Jerry,

            This might be a very helpful feature to have available for the Yahoo Groups user list. I know that list can be exported as a flat ascii file which is comma delimited... It includes the username and the email address and a few other fields. It of course, does NOT include the user's Yahoo password. But once I have the table downloaded I can relatively easily assign a unique and secure password to each YahooGroups member. We have no birthdate there, but a default birthdate could be assigned if needed.

            Can you tell me off hand which fields are required/mandatory for a vBulletin user? I gather from the ImpEx manual that there are only four such fields and assume that username, email and password are three of the four...

            Also if you're importing into an existing vB3 database, do you provide for at least SOME duplicate checking to avoid creating users with the same username or email address?

            I did double-check my Yahoo group administration and found the member export feature (which I already knew about) and a new feature I hdn't noticed before, "threads". Clearly using the threads feature coupled with a message download would permit us to recreate the thread hierarchy in vB3. But I could not figure out a way to easily capture that information.

            I hope this is all helpful to you.

            Best Professional Regards,
            WebSissy

            Comment

            • Jerry
              Senior Member
              • Dec 2002
              • 9137
              • 1.1.x

              #7
              Originally posted by websissy
              Jerry,

              This might be a very helpful feature to have available for the Yahoo Groups user list. I know that list can be exported as a flat ascii file which is comma delimited... It includes the username and the email address and a few other fields. It of course, does NOT include the user's Yahoo password. But once I have the table downloaded I can relatively easily assign a unique and secure password to each YahooGroups member. We have no birthdate there, but a default birthdate could be assigned if needed.

              Can you tell me off hand which fields are required/mandatory for a vBulletin user? I gather from the ImpEx manual that there are only four such fields and assume that username, email and password are three of the four...
              Code:
              [size=1] SELECT `fieldname` , `vbmandatory` FROM `vbfields` WHERE `tablename` =  'user' AND `vbmandatory` = 'Y'[/size]
              username and email are the only mandatory fields needed.

              usergroupid can be pre-generated and importuserid isn't very relevant as the matching is done on username and email address so this can be any int.

              Everything else can be updated or generated after the import or set to defaults on import (which is the norm for nonmandatory data that isn't passed in).

              Originally posted by websissy
              Also if you're importing into an existing vB3 database, do you provide for at least SOME duplicate checking to avoid creating users with the same username or email address?
              Yes, ImpEx will merge users on email address if the are the same, and selected, you can turn if off if you wish to merge manually later for some reason.

              It will not duplicate usernames, if the same user name exsists i.e. Jerry ImpEx will prepen imported_ to make imported_Jerry so you know who is who.

              Originally posted by websissy
              I did double-check my Yahoo group administration and found the member export feature (which I already knew about) and a new feature I hadn't noticed before, "threads". Clearly using the threads feature coupled with a message download would permit us to recreate the thread hierarchy in vB3. But I could not figure out a way to easily capture that information.
              I have done it on a combination of date and subject title, not ideal but the only data available and seems to be in the 90% accuracy range, which I'm happy with.
              I wrote ImpEx.

              Blog | Me

              Comment

              • Peter Walker
                Member
                • Oct 2005
                • 67
                • 3.8.x

                #8
                Hi Jerry,
                I have been following your posts on the Yahoo Group import filter. It seem to me you are the right person to help me transfer my messages.

                I have a large Yahoogroup (about 15000 messages, 1500 members) which I want to import into my vBulletin 3.5.0 board.

                I have already downloaded the messages into an Access database using the http://www.pgoffline.com program, you mentioned above (great program by the way), and installed Impex including the Yahoogroup filters.

                What do I have to do now to complete the job. I could not find any instructions on what exactly has to be done (step by step) to import such a Yahoogroup. The info in the Impex filters is not enough. You do mention that it uses some kind of UNIX mail file, but I have no idea how to generate such a thing. The pgoffline program appears to me to be the ideal solution for downloading all the existing messages and the memberlist. It is easy to use, inexpensive and puts the data in an Access database.

                What I need to know now is how to transfer that data into a threaded format that I can import into vBulletin 3.5.0.

                Due to the popularity of Yahoogroups, and the times such a feature has been requested here, I believe that making it possible to import such groups into vb will be very worthwhile.

                If you need a large group to use for testing, let me know. I really want to get the posts from my group onto my vb 3.5.0 board.


                An international forum for research into the microscope and resonance therapy, developed by Royal Raymond Rife, as well as their modern counterparts.
                Regards

                Peter Walker
                http://www.rifeforum.com

                Comment

                • Jerry
                  Senior Member
                  • Dec 2002
                  • 9137
                  • 1.1.x

                  #9
                  Originally posted by Peter Walker
                  Hi Jerry,
                  I have been following your posts on the Yahoo Group import filter. It seem to me you are the right person to help me transfer my messages.

                  I have a large Yahoogroup (about 15000 messages, 1500 members) which I want to import into my vBulletin 3.5.0 board.

                  I have already downloaded the messages into an Access database using the http://www.pgoffline.com program, you mentioned above (great program by the way), and installed Impex including the Yahoogroup filters.

                  What do I have to do now to complete the job. I could not find any instructions on what exactly has to be done (step by step) to import such a Yahoogroup. The info in the Impex filters is not enough. You do mention that it uses some kind of UNIX mail file, but I have no idea how to generate such a thing. The pgoffline program appears to me to be the ideal solution for downloading all the existing messages and the memberlist. It is easy to use, inexpensive and puts the data in an Access database.

                  What I need to know now is how to transfer that data into a threaded format that I can import into vBulletin 3.5.0.

                  Due to the popularity of Yahoogroups, and the times such a feature has been requested here, I believe that making it possible to import such groups into vb will be very worthwhile.

                  If you need a large group to use for testing, let me know. I really want to get the posts from my group onto my vb 3.5.0 board.


                  http://www.rifeforum.com

                  The mail file importer is quite an archaic importer now, from when you could get the raw mail from YahooGroups.

                  The access is the ideal way to go, though unfortunately I don't have a suitable copy of an board that has been downloaded into an access database to be able to write a yahoogroup_access importer.

                  If you are interested in giving me a copy of yours so I can get it written PM me
                  I wrote ImpEx.

                  Blog | Me

                  Comment

                  • dc3dreamer
                    Member
                    • Oct 2005
                    • 49
                    • 4.2.x

                    #10
                    Originally posted by Jerry
                    The mail file importer is quite an archaic importer now, from when you could get the raw mail from YahooGroups...
                    I have a Unix mail file. I don't necessarily want to import users, as there's no way to get their passwords! It's OK to just have the users register. All I want is to be able to import the messages into an "archive" forum for peoples' reference.

                    Do you think this is possible?

                    Comment

                    • Jerry
                      Senior Member
                      • Dec 2002
                      • 9137
                      • 1.1.x

                      #11
                      Originally posted by dc3dreamer
                      I have a Unix mail file. I don't necessarily want to import users, as there's no way to get their passwords! It's OK to just have the users register. All I want is to be able to import the messages into an "archive" forum for peoples' reference.

                      Do you think this is possible?
                      Thats what the current importer does.
                      I wrote ImpEx.

                      Blog | Me

                      Comment

                      • dc3dreamer
                        Member
                        • Oct 2005
                        • 49
                        • 4.2.x

                        #12
                        Ah, perfect then! I saw the buttons for users. I guess I have to figure out how to dupe my database and make a test forum, then give 'er a whirl. I'm on a remote system with no telnet! Not your problem though.

                        Comment

                        • Peter Walker
                          Member
                          • Oct 2005
                          • 67
                          • 3.8.x

                          #13
                          Originally posted by dc3dreamer
                          I have a Unix mail file.
                          I am curious, how did you get that Unix file. I have not seen any options in my Yahoogroups to download such a thing.

                          Regards

                          Peter
                          Regards

                          Peter Walker
                          http://www.rifeforum.com

                          Comment

                          • Jerry
                            Senior Member
                            • Dec 2002
                            • 9137
                            • 1.1.x

                            #14
                            Originally posted by Peter Walker
                            I am curious, how did you get that Unix file. I have not seen any options in my Yahoogroups to download such a thing.
                            I'm curious as to what is going on there as well as I've been asked by a few customers.

                            Either way I think I'm going to pursue the access database option and retire the mail file importer once it is done.
                            I wrote ImpEx.

                            Blog | Me

                            Comment

                            • dc3dreamer
                              Member
                              • Oct 2005
                              • 49
                              • 4.2.x

                              #15
                              It's coming from my Thunderbird email program (and previous to that Mozilla 1.7). I get the Yahoo postings via email and filter them into a folder for reading. I hate reading with Yahoo's lame web interface! So anyway, I have messages going back over a year. Of course, T-bird saves mail archives in the usual standard/common "unix" (really RFC-822) format.

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...