Manipulating data read from a file using PowerShell and changing the data type of variables

Three times a week (Monday/Wednesday/Friday), John Savill tackles your most pressing IT questions.

Read through the FAQ archives, or send him your questions via email.

In this batch of FAQs we look at manipulating data read from a file using PowerShell and changing the data type of variables.

Q. I am importing a dataset but the column names are not suitable to use. Can I rename columns when importing data?
Q. How can I easily convert a string to a type of numeric in PowerShell?
Q. How can I quickly check the type of a variable in PowerShell?

Q. I am importing a dataset but the column names are not suitable to use. Can I rename columns when importing data?
Dept - PowerShell

A. If you import a dataset, for example a CSV, the first line has the column names and become the attribute names however it may be those names are not suitable for ongoing interaction, for example I had a CSV with column names of:

• First name of student you are pledging
• Last name of student you are pledging

Instead I wanted column names like first_name. Fortunately this can be achieved. Below is an example:

$importfile = 'C:\data\pledge.csv'
$data = Import-Csv $importfile

$cleandata = $data | Select-Object @{ expression={$_.'First name of student you are pledging'}; label='first_name'} , `
@{expression={$_.'Last name of student you are pledging'}; label='last_name' }, `
@{expression={$($_.'Total Paid').Replace("$","")}; label='paid' }, `
@{expression={$_.'Grade of student you are pledging (K,1,2,3,4,5,6)'}; label='grade' }, `
@{expression={$_."Last name of student's homeroom teacher (or Unknown)"}; label='teacher' }

Notice I am renaming the columns and also if you look at "Total Paid" I even removed the $ from the strong so I can use as a numeric later on! Each line is an expression so I can really do whatever manipulation I choose.

Q. How can I easily convert a string to a type of numeric in PowerShell?
Dept - PowerShell

A. If you have a numeric in a string it is easy to treat it as a numeric. For example:

PS C:\> $number = "23.5"

PS C:\> $number.GetType().FullName
System.String

PS C:\> $asdecimal = $number -as [decimal]

PS C:\> $asdecimal
23.5

PS C:\> $asdecimal.GetType().FullName
System.Decimal

PS C:\> $asint = $number -as [int]

PS C:\> $asint
24

PS C:\> $asint.GetType().FullName
System.Int32

Q. How can I quickly check the type of a variable in PowerShell?
Dept - PowerShell

A. Use the .GetType().FullName to check the type of a variable. For example:

PS C:\> $number = [int]35

PS C:\> $number.GetType().FullName
System.Int32

PS C:\> $string = "Hello World"

PS C:\> $string.GetType().FullName
System.String

PS C:\> $date = Get-Date

PS C:\> $date.GetType().FullName
System.DateTime