SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Bradley Schacht

Bradley Schacht is a Data Platform Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Admin". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and 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 [www.bradleyschacht.com, opens in a new window]

Loading comments...