• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 884
  • Last Modified:

Powershell - check csv format

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
Pete
Asked:
Pete
1 Solution
 
Niten KumarPrincipal Systems AdministratorCommented:
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
 
PeteAuthor Commented:
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
 
Kevin StanushApplication DeveloperCommented:
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
 
oBdACommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now