Summary: Guest blogger, Tim Bolton, talks about modifying a .csv file to promote cleaner import.

Microsoft Scripting Guy, Ed Wilson, is here. Today we have the first of two posts by guest blogger, Tim Bolton. This one feeds into his post that will be published on Monday, but the idea is a standalone. Take it away, Tim…

When I set up new conference rooms, I was requested to list the items that are available in the room within the Notes section of Exchange Server, for example:

Seating: 12, Ceiling Projector: YES, Projection Screen: YES, White Board: NO, TV: NO

When I created these accounts with a .csv file imported into Windows PowerShell, I inserted them as a line entry. Like the previous example, this resulted in:

Seating: 12, Ceiling Projector: YES, Projection Screen: YES, White Board: NO, TV: NO

This was causing confusion when users were using Outlook 2010 to search for available items in the conference rooms. I was asked to “stack them,” meaning stack each item instead of using a single, comma-separated line.

They were stacked in the .csv file; however, the carriage return was not passing during the import from the .csv file to Windows PowerShell. This is shown in the image that follows.

Here is my fix…

In Excel, prior to saving as a .csv file:

1) Highlight the entire column for the stacked info.

2) Click Find and Replace, and then click Replace.

3) In the Find what: text box:  
Enable Num Lock. Press the ALT key, and then use the number pad to type the numbers 010. (I got this numeric value from my brilliant coworker, Chris Duck.) It will not show up when you type it, which is normal.

Note  It is important to use the number pad because the numbers that run across the top of your keyboard may have an alternate task; and therefore, the ASCII key code for the carriage return/line feed may not register properly.

 (If you are using a laptop without a number pad, you should be able to press the Fn + ScrLk keys to enable Num Lock for the alternate number pad in the middle of your keyboard, if your laptop supports this feature.)

4) In the Replace with: text box, type a single semicolon ( ; ), as shown in the following image:

5) Click Replace All. The modified fields now appear as shown here:

6) Now save the file as a .csv file. When you import the file to Windows PowerShell, it will stack them because it now sees the semicolon ( ; ) as a carriage return, which Windows PowerShell will understand.

Now when you view the conference room through either Outlook 2010 or Exchange Server, the information will be “stacked,” as shown in the following example:

Cool, right?

~Tim

Thank you, Tim, for an excellent post. Join me tomorrow when I will talk about more cool Windows PowerShell stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy