Skip to Content
Have you ever wanted to take the user export file that came out of the UME and convert it into an excel file which could be manipulated.  After my last weblog I had several requests to provide a solution to do just that.  Here is what I have found.  The first step is to import your text file into Excel.  This can be done by opening Excel and selecting Data -> Importa External Data -> Import Data.  A Windows file browser box will pop up. image Navigate to and select your file.  The Text Import Wizard will be displayed.  Click on next. image This is step two of the text import wizard.  Make sure to check the Other box and enter “=” without the “‘s in the box.  Then click the next button. image Step Three will presnet itself.  Click on the finish button. image The import location dialog box will present itself.  Choose where you would like to import the data.  In this case, we will use the default location. image You will then have columns A and B in your worksheet.  I have added column C which increments by 8 because that is how far apart similar attributes are.  The reason for this will become clear shortly. image At this point we need to manipulate the data to place in back in a format that we can use.  I typed the column headers in and then used the following formulas to transpose the columns and rows. image Users =OFFSET($B$2,$C1,0) Password =OFFSET($B$3,$C1,0) Email =OFFSET($B$4,$C1,0) First Name =OFFSET($B$5,$C1,0) Last Name =OFFSET($B$6,$C1,0) Telephone =OFFSET($B$7,$C1,0) Department =OFFSET($B$8,$C1,0)  I then used the fill handle to drag these forumlas down the sheet until I had captured all of the data.
To report this post you need to login first.


You must be Logged on to comment or reply to a post.

  1. Pankaj Kumar

    Thanks. This is really cool. Earlier I have been using sed/awk combination to achieve the same. But doing it from Excel is pretty neat. Didn’t knew the row-to-column transposition technique. Kudos for sharing.

    – Pankaj


Leave a Reply