CSV: Add a Carriage Return for PowerShell Import

CSV: Add a Carriage Return for PowerShell Import

  • Comments 13
  • Likes

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.

Image of items

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:

Image of text box

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

Image of list

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:

Image of menu

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 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • @Tim

    Very cool man! I've gone so far as using Notepad++ for making edits like that, but it's cool to know you can still key in the special characters directly in :)

    Sean

  • Nice trick -where is the PowerShell script?

  • You can probably accomplish this in your PowerShell script without any manual effort.  Something like this:

    (Get-Content .\test.csv -Raw) -replace '\r(?!\n)', ';' | ConvertFrom-Csv

  • Actually, it looks like you're replacing newlines, not carriage returns.  Same idea, different regex pattern:

    (Get-Content .\test.csv -Raw) -replace '(?<!\r)\n', ';' | ConvertFrom-Csv

  • There is really no need to replace those things.  CSVs don't care about those.  That is why we have quoted strings.

    We can import a CSV and all it does is make the screen look ugly.  the CSV is still imported correctly.

  • Thanks Sean!

    @jrv  -  It is coming on Monday

    @David Wyatt - I actually still had to use this -

    Set-User -Identity $Entry.Alias -Notes $Entry.Notes.replace(";", "`r`n")

    Which you will see on Monday.   I had to combine both methods to make this work, Just using the replace would not work, and without it adding the ; would not work either...

  • Don't forget about:

    $a=1,2,3,4,5

    [array]::Join('|',$a)

  • @jrv - Full Script will be available Monday.  Please feel free to make any changes or suggestions.  This has been a great learning opportunity for me so all comments and suggestions are welcome...

  • @jsclmedave

    I was just kidding although script could do the same thing.  It is not necessary to do that to have a legal CSV. CSV rules are very simple.  If it moves quote it.  Excel nearly always does this when in doubt.

    CSV Rule #1 - if any unprintable character or a comma arrive in a column then the column gets quoted (actually the cell.  Line breaks of any kind require quotes and nearly all modern tools do this correctly.

    Look up the RFC to see how it is spec'ed.  I don't remember the number and I am going out.

  • @all

    I lied - I looked it up: www.ietf.org/.../rfc4180.txt

    RFC-4180

  • Okay, so it sounds like what you are really doing in the end is replacing isolated \n characters with \r\n.  The same code I posted earlier should probably still work, but without the semicolon middle man:

    (Get-Content .\test.csv -Raw) -replace '(?<!\r)\n', "`r`n" | ConvertFrom-Csv

  • @David - Right!  At least you would think so...  It would "not" work most of the time even after trying numerous CSV files with different data in them.

    Using this method was the only "Sure Way" that I could get it to work and it did work for over 100 new conference rooms.

  • This does not work for me - just passes through the semi colon.