5 Replies Latest reply on Sep 13, 2015 6:40 PM by Daniel Johns

    batch uploading - custom2 field

    Bill Kasper Guide

      I've screwed up and put some incorrect data in my custom2 field on uploading my new users for the coming school year.


      I'd like to re-upload, leaving custom2 blank so it deletes that incorrect information, but so far haven't found the field content magic to make that happen.  A blank space or more than one in the .csv throws an error, quotes reproduce as quotes, a quote with a blank space reproduces as a quote with a blank space and throws an error.  The only success I've had is to put a . in the field, and it uploads that character, replacing the erroneous data.


      Is there any way to clear the data in custom2 using batch upload?




        • Re: batch uploading - custom2 field
          Steven English Guide



          I'll leave the batch file troubleshooting to the gurus, but here is a query that you can use if you feel comfortable with it.  You could update all of the users to have a very unique yet specific value in custom2, then target that string as the users to update.  If the period or dot is already sufficiently unique, just replace UniqueStringHere with a period.  Make sure you update the select query AND the update query.  The select query will show you who is going to get updated and the update one will do just what it says.  It is currently commented out to help protect from being executed unintentionally.


          USE pharos
          SELECT * FROM users u
          WHERE custom2 = 'UniqueStringHere'
          UPDATE users
          SET custom2 = NULL
          WHERE custom2 = 'UniqueStringHere'


          I can get you a fancier query if needed (to filter by group or whatever).



          No guarantee or warranty of any kind.  Back up your database first, and if you are uncomfortable executing SQL queries please get someone to help!




          • Re: batch uploading - custom2 field
            Daniel Johns Tracker

            Changing a field to be blank, when it already has a non-blank value, is one thing that the Batch Loader simply does not support.


            If you want to clear the data, you'll need to do that using Pharos Administrator.  This is not something that must be done one user at a time.  You can select all the affected users together, then go into the "Custom Field 2" property and blank it.  This should be especially easy if you have first used the batch loader to cause every such user to have the same "Custom Field 2" value, as you can then filter users on Custom Field 2 (as described in the "Filtering Records" page of the Help).  If there are more than a thousand such users, the Administrator will only show a thousand at a time; once you have applied the filter, you will need to select the users that are shown, clear the property, click "Refresh", and repeat until you have cleared the property for all of them.


            This may not be the answer you were hoping for, but I hope it provides a way to solve your problem.

              • Re: batch uploading - custom2 field
                Daniel Johns Tracker

                Sorry, I missed Steve's reply when writing mine.  Using a SQL query is probably the easier way of fixing up your data, if it's something you feel comfortable doing.

                • Re: batch uploading - custom2 field
                  Paul Klump Wayfarer

                  Are there any plans to add some sort 'blank out comment/custom fields" functionality to the userload utility?  I asked Pharos support about this in August, and the answer was to come up w/ a SQL solution as described by Steve above.  However, I'd really prefer that this functionality be in the userload tool.  I plan on coming up with a custom procedure to update proctor access on a scheduled basis, using feed files generated from our student employee system.  I use the comment field to denote which campus a proctor works on when proctor access is set, so once a student no longer works for our labs, I'll need to set their access back to User (which works via userload), but I can't clear the comment field.  It would be much easier to set this via a correctly formatted feed file for userload, instead of having to add a SQL update to the process.


                  One thing I neglected to ask Pharos support: is this inability to blank out comment/custom fields on the SQL side, or with the userload tool itself?  I haven't had a chance to load up SQL Profiler to examine what queries are being run when the accounts are being updated when I run a batch via userload.