Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Bradley Schacht

Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer and tech edited the SQL 2011 Bible. His experience on the Microsoft BI platform includes DTS, SSIS, SSRS, SSAS and migrations and conversions. He has helped numerous companies in successfully developing and implementing new business intelligence solutions into their organizations. Bradley also participates as a speaker in community events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG).

View Flat File Contents Using PowerShell

We have all been there; you need to take a look at the data inside a file. Sure you can open it in Notepad if it is under 20-30MB, bigger than that and you may run into problems depending on the computer. Sometime WordPad will handle the larger files, again up to about 60MB is where I tend to run out of luck. So what happens if the file is a hundred MB? 200 MB? How about 1 GB?

I’ve recently been working on a PDW and it isn’t uncommon to see files that are many GB in size that need to be loaded into the database. There is no way to view the contents of a file that size. What’s worse is when you need to view the contents of the file because of an error. There is no way to load it into a database and look at it if the file has problems. Luckily PowerShell can help in this case using the cmdlet Get-Content.

To use this handy feature fire up PowerShell and let’s take a look at the contents of a text file. Here we will look at an export of the Adventure Works Production.ProductModel table.

(You can export the table by running the following at the command prompt assuming you have a local instance with Windows Authentication: BCP AdventureWorks.Production.ProductModel OUT C:\ProductModel.txt -S localhost -T -c)

At the prompt type Get-Content C:\ProductModel.txt (Get-Content FullFilePath)

The results will show the entire file scrolling by on the screen until the end is reached:

Now for the perfect use case; I have a file on my computer that is just over a gig in size; the export of the Contoso Fact Online Sales table containing about 12 million records. This file will never open in Notepad. It also isn’t useful to have the entire file stream through a PowerShell window (it’s probably not useful on the previous file either honestly). In most cases we just need to take a quick look at the data to see what we are dealing with. The Get-Content cmdlet can be extended to bring in only a subset of the data.

This time at the prompt type Get-Content C:\FactOnlineSales.txt -totalcount 5

This will bring back the first 5 records from the file. A perfect way to preview the file without needing to open something that is potentially gigs in size. Check out below for some cool functions and samples using the ProductModel file we just created above.

Bring back the first x rows of the file: Get-Content FilePath -TotalCount X
Example: Get-Content C:\ProductModel.txt -TotalCount 5

Bring back the last x rows of the file: Get-Content FilePath |Select-Object -Last X
Example: Get-Content C:\ProductModel.txt | Select-Object -Last 5

Some file statistics: Get-Content FilePath | Measure-Object
Example: Get-Content C:\ProductModel.txt | Measure-Object


Leave a comment on the original post [, opens in a new window]

Loading comments...