Search results for 'app:weblogs' matching tags 'Weekend Scripter' and 'CSV and other delimited files'

Learn How to Manually Create a CSV File in PowerShell

Anonymous — Sat, 24 Mar 2012 05:00:00 GMT

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.

“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

Use PowerShell to Automatically Create a CSV File

Anonymous — Sun, 06 Nov 2011 05:00:00 GMT

Summary: Learn how to use Windows PowerShell to automatically create a CSV file.

Microsoft Scripting Guy Ed Wilson here. I am about finished playing around with CSV files for the time being. I do not always use the ConvertTo-CSV or the Export-CSV cmdlet. There are times that I write code and create my own CSV file from scratch. An example of this is the CreateCSVFile.ps1 script that follows:

CreateCSVFile.ps1

1..5 | ForEach-Object -begin { "path,itemtype" } -process{

"c:\folder$_,directory"

for($i=0; $i -le 10; $i++) {"c:\folder$_\file$i.txt,file"}} |

Out-File c:\fso\filesAndFolders.csv -Encoding ascii -Force

This script creates a CSV file that I can use to create a nested folder hierarchy. It is useful when testing certain applications, or for testing a Windows PowerShell script. I create five folders, and inside each folder I create 11 files. The folders are off the root and are named Folder1, Folder2, Folder3, Folder4, and Folder5. The files are named file0, file1, and so on through file10.

When using the Foreach-Object, I use the begin parameter to perform an action once. Inside the process block, I create the path to the folder, and I include the itemtype of directory. Next, I use a loop to create the path to the files.

The newly created CSV file is shown in the following figure.

I use the Import-CSV cmdlet to import the CSV file, and I pipe it to the New-Item cmdlet as shown here:

Import-Csv C:\fso\FilesAndFolders.csv | New-Item

The command and associated output are shown in the following figure.

One of the folders and associated files is shown in the following figure.

As you can see, there are times when it is easier to use a little bit of code to create the CSV file. This makes it a lot easier when it comes time to import the file and to pipeline it to another cmdlet.

This is all for today. I hope you have a great day. See you next week.

Ed Wilson, Microsoft Scripting Guy

Use PowerShell to Read CSV Files and Create Files and Folders

Anonymous — Sat, 05 Nov 2011 05:00:00 GMT

Summary: Learn how to use Windows PowerShell to read CSV files and create files and folders.

Microsoft Scripting Guy Ed Wilson here. This past week has been fun. I enjoy talking about comma-separated value files (CSV) because they are very flexible, and Windows PowerShell makes working with them really easy.

One of the cool things that can be done using a CSV file was only alluded to in yesterday’s post, Use a CSV File to Populate Parameters of PowerShell Cmdlets.

In yesterday’s article, I piped information from a CSV file to the Get-WmiObject cmdlet. The problem is that the Get-WmiObject cmdlet does not accept piped input for either the class or computername parameter.

However, the New-Item cmdlet does accept piped input. When a cmdlet accepts piped input, it will automatically match column names from a CSV file with parameter names of the cmdlet receiving the piped input. This allows for some extremely powerful scenarios. Here are the steps for this scenario:

Create a CSV file that uses column headings that are the same as the parameter names for the cmdlet to use.
Use the Import-CSV cmdlet to read the CSV file and to create a custom object from that file.
Pipe the results to a cmdlet that accepts piped input.

Suppose I create a CSV file that contains a file system layout. In that file, I specify the name of the folder and the names of files I want to create in that folder. I use the column names of path and itemtype because those are the parameter names used by the New-Item cmdlet. A sample CSV file that meets these requirements is shown in the following figure

After I have created my file, the Windows PowerShell command itself is trivial. I use the Import-CSV cmdlet to read the CSV file, and I pipe the results to the New-Item cmdlet. This command is shown here:

Import-Csv C:\fso\FilesAndFolders.csv | New-Item

The use of the command to read a CSV file and create files and folders along with the associated output are shown in the following figure.

When this command runs, the following is displayed in Windows Explorer.

That is about all there is to reading a CSV file and automatically creating files and folders. Given the Windows PowerShell providers that work with New-Item, this one scenario and one cmdlet provide lots of possibilities. And this is only one cmdlet. I will leave it to you to explore and see what you can come up with. Let me know.

Ed Wilson, Microsoft Scripting Guy