Learn How to Manually Create a CSV File in PowerShell

Learn How to Manually Create a CSV File in PowerShell

  • Comments 3
  • Likes

Summary: The Scripting Wife learns how to manually create a CSV file in this beginner Windows PowerShell blog.

Microsoft Scripting Guy, Ed Wilson, is here. Ah, it is the weekend. It seems that this week has been rather long, although I know that is a misconception because each week only has 168 hours in it. I guess it is kind of like one of the characters in One Flew Over the Cuckoo's Nest, a book I read in high school. They assumed that the staff sped up the clock when they were having fun, and slowed down the clock when they were doing things they disliked as a way of punishing them. Anyway, this morning I am in my office busily working on my new Microsoft Press book, Windows PowerShell 3.0 Step by Step.

“Hmmm…I like the new Where clause syntax in Windows PowerShell 3.0,” I said to myself.

“So what is a CSV?” said the Scripting Wife as she jolted me from my mental revelry.

“Is it a model of a car?” I facetiously queried.

“No. Not even close, Script Monkey,” she shot back, “Try again.”

“OK. It is a comma-separated value type of text file,” I said.

“Correct. Give the Script Monkey a peanut,” she said.

“So why do you want to know about CSV files?” I asked.

“Because silly, it is in the 2012 Scripting Games Study Guide, and the games start in exactly nie days,” she replied.

“OK. What do you want to know about CSV files?” I asked.

“Everything.”

“I see. Well, you could start by reading all the CSV blogs I have written on the Hey, Scripting Guy! Blog.”

“I could, but I want you to teach me. After all, you are the world famous Scripting Guy.”

Manually creating a CSV File

“What I am going to show you today is how to create CSV file manually. The first thing you need to do is to create a here string,” I said while turning my laptop screen so she could see.

“A hear string? What is this? Like a symphony or something?” she quipped.

“Try to stay with me. A here string allows you to create a string without worrying about any sort of formatting like quotation marks and commas, which are two things that CSV files really love, and that Windows PowerShell in general balks at.”

“OK. So how do I create this magical here string?” she asked with obvious signs of skepticism.

Use @” to begin and “@ to end a here string

“The first thing I do is use a variable to hold the resulting string. Then I begin the here string with the at plus quotation symbols (@”). Then I immediately press ENTER, and I add my column headings. A comma separates each heading, and then I again press ENTER. Now I add the value for each column and I use a comma to separate the values.”

“That is why it is called a comma-separated value file?” she asked.

“Exactly. When all of the rows of data are typed, press ENTER again, and I close the here string with the quotation mark plus at symbols (“@) and again press ENTER.”

“OK,” she said somewhat skeptically.

“Why don’t you give it a try,” I said as I turned the laptop over to her.

The Scripting Wife opened the Windows PowerShell console, and typed the following on the first line. In the following code, <enter> represents the ENTER or Return key (ASCII 13) (8 ).

$csv = @"<enter>

Next she added the column headings by typing the following.

"index","name"<enter>

After the columns were added, the Scripting Wife added the data. This is shown here.

1,"ed"<enter>

2,"teresa"<enter>

Finally, she closed the here string.

"@<enter>

<enter>

“Now you need to write your CSV data to a file,” I said. “When you are done, use the Import-CSV cmdlet to read it back.”

“Oh, I can use redirection to create the file,” she said.

The Scripting Wife quickly typed the following.

$csv >>c:\fso\mycsv.csv<enter>

Import-Csv C:\fso\mycsv.csv<enter>

“Very good. Now store the CSV information back into a new variable, and access the first element in the file,” I said.

“Huh? Do you mean for me to use square brackets and see the first row of information?”

“Yeah.”

“Then why don’t you just say that?” she chided.

The Scripting Wife typed the following.

$mycsv = Import-Csv C:\fso\mycsv.csv

$mycsv[0]

“Do you see that your first row of data contains two items—the index and the name?” I asked.

“Yes.”

“You can use dotted notation to access the specific column of information. These act just like properties. Why don’t you get the name from the second record?” I suggested.

The Scripting Wife typed the following.

$mycsv[1].name

“So how does all this look?” the Scripting Wife asked as she turned the monitor over so I could see.

As I looked at the screen, I beamed. “It is perfect.” The screen is shown in the image that follows.

Image of command output

“Cool,” she said as she jumped up and prepared to leave.

“By the way, how did you do on the new 2012 Scripting Games PowerShell Quiz?” I asked.

But by this time she was long gone. Perhaps she did not hear. Or maybe she did…

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
  • <p>$csv=@()</p> <p>$csv+=New-Object PSObject -Property @{Index=1;Name=&#39;John&#39;}</p> <p>$csv+=New-Object PSObject -Property @{Index=2;Name=&#39;Mary&#39;}</p> <p>$csv+=New-Object PSObject -Property @{Index=3;Name=&#39;Kate&#39;}</p> <p>$csv|ConvertTo-Csv -notype &gt; c:\fso\mycsv.csv</p>

  • <p>Hi Ed, Hi Teresa</p> <p>using CSV files is always a good idea in Powershell environments.</p> <p>They are automatically converted to objects by Import-Csv and can easily be created.</p> <p>Of course @jrv is right ... we could use other datastructures to produce CSVs.</p> <p>Many roads lead to rome ...</p> <p>btw: If you need help with your new book, ... let me know!</p> <p>I installed WIN8 in a virtual machine just to have PS3 available :-)</p> <p>So ... let the future begin! ( and the games in 9 days :-)</p> <p>Klaus</p>

  • <p>jrv, that bit of script was very helpful! However, to get it to work right, I had to modify the last line:</p> <p>$csv=@()</p> <p>$csv+=New-Object PSObject -Property @{Index=1;Name=&#39;John&#39;}</p> <p>$csv+=New-Object PSObject -Property @{Index=2;Name=&#39;Mary&#39;}</p> <p>$csv+=New-Object PSObject -Property @{Index=3;Name=&#39;Kate&#39;}</p> <p>$csv | Export-CSV C:\temp\mycsv.csv -notype</p> <p>That change to &quot;Export-CSV&quot; made the values and headings display in columns in Excel as expected, whereas using &quot;ConvertTo-Csv&quot; crammed the strings into one column.</p>