Date Time field csv import

How to assign a field type on import csv

Sometime you are working with CSV files for importing reports and so on. In a good world all header fields have the right syntax an good values. :-)

But what ca I do when the wold is not good to me and i have bad headers or values?

With Power Shell you can do a typechange of one field directly on importing the CSV file. I will show how it work.

For example we have an CSV file that have a header like this:

Client,Agent,Instance,Backup Set,Subclient,Job Id,Status,Type,Scan Type,Start Time

Now we have some fields with a blank or we have a DateTime field. When we import that file we have only fields from type STRING and we want to have the field (Job Id) without a blank and we want the field (Start Time) as DateTime. In my example I have in the DateTime field an en-US Date not an de-DE one as I must have.

So first we have to reset the field (Job Id) to (JobId) and we can do it like this:

Import-Csv -Delimiter ',' -Path "$($PathSource)\$($CsvFile)" | | Select-Object -Property Client, Agent, Subclient, @{Name='JobId';Expression={$($_.'job Id')}}

As you can see we reassign the field (Job Id) with an Expression:
@{Name='JobId';Expression={$($_.'job Id')}}
That is very easy and for this field is the type STRING ok.

The next thing is the DateTime field and for that we have to do some more things. First we create an instance of the right culture and than we import with the field (Start Date).

# Set Culture for DateTime $Culture = New-Object System.Globalization.CultureInfo('en-US') $Month = ((Get-Date).AddMonths(-1)).Month $Year = ((Get-Date).AddMonths(-1)).Year Import-Csv -Delimiter ',' -Path "$($PathSource)\$($CsvFile)" | Select-Object -Property Client, Agent, Subclient, @{Name='JobID';Expression={$($_.'JobId')}}, @{Name='StartTime';Expression={[datetime](Get-Date -Date $_.'Start Time' -Format ($Culture.DateTimeFormat.ShortDatePattern))}}}

After you importing this data you can do select with DateTime options like this:

Where-Object { (($_.StartTime.Month -eq $Month) -and ($_.StartTime.Year -eq $Year)) }


Written by Arne Tiedemann on Friday May 20, 2016
Permalink - Tags: PowerShell, CSV, Import, DateTime

« Dynamic group members - Exchange Certificate Request »