PowerShell change datatypes of the Data.DataTable object

Question

I am working on a script which creates a Data.DataTable with the data from a .csv file. Everything worked fine, I have it in my database with the right values. But to help my database performance I would like to set the data types for the columns of the Data.DataTable. Unfortunately all my values are converted to strings from the CSV to the Data.DataTable columns.

My CSV looks like this (example):

"Name";"UserID";"Salary"
"Peter";"1";"1200.03"
"Jennifer";"2";"1000.50"

I tried following methods:

foreach ($object in $csv){
    Write-Output "line $countline"

    foreach($property in $object.PsObject.get_properties()) 
        {   
        $property.TypeNameOfValue = [System.Type]::'System.Int32'

          ***and the other try*** 

        $property.Value = [System.Type]::'System.Int32'
        }
    }

Both times nothing happened (except errors).

So I want the following to happen: The first column should be a datatype of string ("name"). The others should be double or float.

Does anyone have a solution for this?

Booga Roo · Accepted Answer · 2015-04-18T15:01:19.267

There are several ways to solve this dilemma. I will say, Import-CSV has some limitations if you want to work with anything other than strings, but it can be done.

Method 1: Hash Tables

$data = import-csv .\data.csv -Delimiter ';'
$hashtable = [ordered]@{"Name" = ($data).name; "UserID" = ($data).UserID.ToDouble($_); "Salary" = ($data).Salary.ToDouble($_)}

Example output:

PS C:\> $hashtable.Name
Peter
Jennifer
PS C:\> $hashtable.Name | GM
TypeName: System.String
PS C:\> $hashtable.UserID
1
2
PS C:\> $hashtable.UserID | GM
TypeName: System.Double

Method 2: CSV Files with Type Information

This method is a little easier to use the traditional way I'm used to Import-Csv but I couldn't combine the results into a single variable. Not the end of the world, but also a little more work to make more CSV files and figure out how to apply a type to them.

Example CSV files:

#TYPE System.String
"Name"
"Peter"
"Jennifer"

And

#TYPE System.Double
"UserID";"Salary"
"1";"1200.03"
"2";"1000.50"

Example results from using Import-Csv:

PS C:\> Import-Csv .\double.csv -Delimiter ';'
UserID Salary
------ ------
1      1200.03
2      1000.50
PS C:\> Import-Csv .\double.csv -Delimiter ';' | GM
TypeName: CSV:System.Double

Optional Method: If you're feeling brave, someone who wrote proxy script for Import-Csv over here: Importing typed objects with typed properties from a CSV file, but I did not test that myself. I mention it because it looks like a good way to deal with this if it comes up frequently.

I found this article on how to deal with some of the quirks of hash tables to be helpful.

PowerShell change datatypes of the Data.DataTable object

1 Answers1

Linked