Solved

Powershell - check csv format

Posted on 2016-11-15
4
135 Views
Last Modified: 2016-11-17
Hello I want to import a users.csv to create new AD users but I want to check its in the correct format first, how can I do this in powershell? Checking correct column headings, maybe also checking the entire contents not containing illegal characters like hyphens, apostrophes etc..

Any advice please?
0
Comment
Question by:Pete
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 6

Expert Comment

by:Niten Kumar
ID: 41888888
What is the format of your csv file?  Best would be if you use a proper template where the required data is input correctly then there should be no issues.  See attached .xls file.  You can modify it to your requirements.  The instructions are on the first sheet.
MassUserCreationScriptSource.xls
0
 
LVL 1

Author Comment

by:Pete
ID: 41889281
That is not the answer to the question, I know what a properly formatted csv should look like, I want to script in some protection in case it is wrong.
0
 
LVL 6

Expert Comment

by:Kevin Stanush
ID: 41889826
What Niten is suggesting, and I'll second, is that Powershell might not be the best tool for doing this 'validation'.  You are going to have to read each line from the CSV file, and then perform custom checks on it, based on your own needs, like not allowing certain characters, checking length, if each element is supplied, etc.

It might be easier and more efficient to perform these checks before the file is created.
0
 
LVL 85

Accepted Solution

by:
oBdA earned 500 total points
ID: 41891224
Here's a starting point. The first part checks if all expected headers appear in the csv; this can be used pretty universally.
The content check you're going to have to customize yourself; I've put some samples in on how it could be done.
$Csv = Import-Csv -Path "C:\Temp\ADUser.csv"

## List all columns that MUST be in the csv:
$ColumnsExpected = @(
	'SamAccountName',
	'DisplayName'
)
## Error and help messages for the content:
Function Set-ContentError($Index, $Column, $Value) {
	"Row $($Index), column '$($Column)': value '$($Row.$Column)' is invalid!" | Write-Host -ForegroundColor Red
	$HelpMessage = Switch ($Column) {
		'SamAccountName'	{'Allowed characters are a-z, 0-9, -, maximum length 20.'}
		'DisplayName'		{'No single quotes, please.'}
	}
	If ($HelpMessage) {$HelpMessage | Write-Host -ForegroundColor Yellow}
	Return $False
}

## Verify that all expected columns are there (additional columns in the csv will be ignored)
$ColumnsOK = $True
$ColumnsCsv = $Csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name

$ColumnsExpected | ForEach-Object {
	If ($ColumnsCsv -notcontains $_) {
		$ColumnsOK = $False
		"Expected column not found: '$($_)'" | Write-Host -ForegroundColor Red
	}
}

If (-not $ColumnsOK) {
	Throw "The csv format is incorrect!"
}

## Verify that the contents are OK:
$ContentOK = $True
$RowIndex = 0
ForEach ($Row In $Csv) {
	$RowIndex++
	$Column = 'SamAccountName'
	If (-not ($Row.$Column -match '\A[-a-z0-9]{1,20}\Z')) {
		$ContentOK = Set-ContentError -Index $RowIndex -Column $Column -Value $Row.$Column
	}
	$Column = 'DisplayName'
	If ($Row.$Column.Contains("'")) {
		$ContentOK = Set-ContentError -Index $RowIndex -Column $Column -Value $Row.$Column
	}
}

If (-not $ContentOK) {
	Throw "The csv content is incorrect!"
}

Open in new window


A sample csv:
"samAccountName","DisplayName"
"DoeJohn", "John Doe"
"DoeJohn", "John'Doe"
"DoeJane12345678901234", "Jane Doe"

Open in new window

0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question