• Home
  • About 404TS
  • Contact

404 Tech Support

Where IT Help is Found

  • Articles
    • Code
    • Entertainment
    • Going Green
    • Hardware, Gadgets, and Products
    • Management
    • Network
    • News
    • Operating Systems
    • Security and Privacy
    • Software
    • System Administration
    • Talking Points
    • Tech Solutions
    • Web
    • Webmaster
  • Reviews
  • Media
    • Infographics
    • Videos
  • Tech Events
  • Tools
    • How do I find my IP address?
    • Browser and plugin tests
  • Get a Technical Consultation
You are here: Home / Articles / Tech Solutions / Massaging data to have usable information and save a few keystrokes

Massaging data to have usable information and save a few keystrokes

2013-01-10 by Jason

I needed to create and populate a security group in active directory so I could limit a group policy to only the relevant users. Those relevant users each had a folder under the server share with the folder name being their username. Given that there were several dozen users already in the directory, I was hoping to be able to get that data in the format I needed so I could do a simple copy/paste instead of typing them all in again. Here is the process I came up with, feel free to drop any further tips in the comments.

What I had: a directory on a server share full of folders named with each user’s username

What I needed: a semi-colon separated list of said usernames

Step 1: Map drive

My previously suggested registry trick to allow the command prompt to use UNC paths wasn’t working in Windows 7 (at least not without a restart). Instead, I mapped a network drive to the server share containing the user folders.

Step 2: Run dir from command prompt on mapped drive

dir output

I switched to Z: (CD Z:) and then wrote out the output from the dir command to a text file (dir > c:personaldrives.txt).

Step 3: Import the text into Excel

The text output included more data than what I needed. I deleted the information out of the top and bottom that didn’t fit the column format and saved/closed the file.

textfile output

I now took the scrubbed data and opened up Excel. Switching to the Data tab, I selected to “Get External Data” from Text. I browsed to the personaldrives.txt file and chose to import it. On the screen that popped up, I chose Delimited.

excel delimited

Import into Excel as delimited and on the next screen check ‘Space’ as the delimiter and ensure the ‘Treat consecutive delimiters as one’ is checked. This should add lines to each column and you can finish the Import Wizard.

Step 4: Add the semicolon after each username

Copy column E data out of Excel and into Notepad++. Next, we’re going to do a Find and Replace. Hit Ctrl + F to bring up the dialog.

Choose the radio button near the bottom for ‘Search Mode’ to set it to Extended.

Then in the ‘Find what’ field, enter: r
The ‘Replace with’ field should have: ;r

Then hit the Replace All button once.

data massaging

The r symbolizes the carriage return after each line. Your data is now properly formatted.

Step 5: Copy into your AD group

Copy and paste the semicolon-separated information into the group’s Members tab in Active Directory Users and Computers.

Mission accomplished.

Alternate Step 4: Use only Excel

If you don’t want to include Notepad++ in the process, you can complete it using only Excel. With the column to the right of your usernames, put a semicolon in the top field. Drag that box down to repeat the semicolon for each row where there are user names. Then, use another column to concatenate the two columns together into this third column.

Optimize step 2 and skip step 3: Use parameters to clean up the output

For the sake of illustration, I went the long way around with this example. In our case, however, we could have revised Step 2. Instead of using dir > C:personaldrives.txt, we could use dir /B > c:personaldrives.txt.

The /B parameter “Uses bare format (no heading information or summary).” It provides only the folder names on separate lines and nothing else to clean up. This means we could output text, open it in Notepad++, do a find and replace to add the semicolon and been done. In case you’re using other commands that don’t make it so easy, the full example should still be useful of different ways to massage and manipulate the data into a format you need.

Filed Under: Tech Solutions

Trending

  • The results of Pwn2Own 2013
    In Security and Privacy
  • CAPTCHAs Become Ads, Block Spam, Make Site Owners Money, Infuriate Site Visitors
    In Media, Security and Privacy, Webmaster
  • What Is An Ellucian Migration And Is It Important?
    In Articles, Tech Solutions

Latest Media Posts

Find Out Where To Download SNES ROMs

Find Out Where To Download SNES ROMs

Multifunctional Video Conversion Tools – Wondershare Video Converter

Multifunctional Video Conversion Tools – Wondershare Video Converter

  • Popular
  • Latest
  • Today Week Month All
  • How to ‘Unblock’ multiple files at a time with PowerShell How to 'Unblock' multiple files at a time with PowerShell
  • Command line to take ownership and change permissions Command line to take ownership and change permissions
  • Increase IIS Private Memory Limit to improve WSUS availability Increase IIS Private Memory Limit to improve WSUS availability
  • SOLVED: “This modification is not allowed because the selection is locked.” SOLVED: "This modification is not allowed because the selection is locked."
  • Read the Event Logs on Windows Server Core Read the Event Logs on Windows Server Core
  • Making Distributed Software Development Work: Strategies and Best Practices for Managing Remote Teams Making Distributed Software Development Work: Strategies and Best Practices for Managing Remote Teams
  • customer contactless payment for drink with mobile phon at cafe counter bar,seller coffee shop accept payment by mobile.new normal lifestyle concept The Latest Innovations In Payment Technology
  • How Digital Technology Brought the Rise of the CMO   How Digital Technology Brought the Rise of the CMO  
  • How to Purchase Cryptocurrencies? How to Purchase Cryptocurrencies?
  • Top 6 necessary aspects to consider when hiring Angular developers Top 6 necessary aspects to consider when hiring Angular developers
Ajax spinner

Elevator Pitch

404 Tech Support documents solutions to IT problems, shares worthwhile software and websites, and reviews hardware, consumer electronics, and technology-related books.

Subscribe to 404TS articles by email.

Recent Posts

  • Making Distributed Software Development Work: Strategies and Best Practices for Managing Remote Teams
  • The Latest Innovations In Payment Technology
  • How Digital Technology Brought the Rise of the CMO  

Search

FTC Disclaimer

404TechSupport is an Amazon.com affiliate; when you click on an Amazon link from 404TS, the site gets a cut of the proceeds from whatever you buy. This site also uses Skimlinks for smart monetization of other affiliate links.
Use of this site requires displaying and viewing ads as they are presented.

Copyright © 2023 · Magazine Pro Theme on Genesis Framework · WordPress · Log in