PowerShell更改Data.DataTable对象的数据类型 [英] PowerShell change datatypes of the Data.DataTable object
问题描述
我正在处理一个脚本,该脚本使用.csv文件中的数据创建 Data.DataTable
。一切正常,我在数据库中以正确的值保存了它。但是为了提高数据库性能,我想为 Data.DataTable
的列设置数据类型。不幸的是,我所有的值都从CSV转换为字符串,到 Data.DataTable
列。
我的CSV看起来像这个(示例):
名称; UserID;工资
彼得; 1 ; 1200.03
Jennifer; 2; 1000.50
I尝试了以下方法:
foreach($ csv中的$ object){
Write-Output line $ countline
foreach($ object.PsObject.get_properties()中的$ property)
{
$ property.TypeNameOfValue = [System.Type] ::'System.Int32'
***和其他尝试***
$ property.Value = [System.Type] ::'System.Int32'
}
}
两次均未发生(错误除外)。
因此,我希望发生以下情况:第一列应为字符串(名称)的数据类型。
有人对此有解决方案吗?
有多种方法可以解决此难题。我会说,导入CSV
有一些局限性,如果您想使用字符串以外的任何东西,但这是可以做到的。
方法1:哈希表
$ data = import-csv .\data.csv -Delimiter';'
$ hashtable = [ordered] @ { Name =($ data).name; UserID =($ data).UserID.ToDouble($ _); Salary =($ data).Salary.ToDouble($ _)}
示例输出:
PS C:\> $ hashtable.Name
Peter
Jennifer
PS C:\> $ hashtable.Name | GM
TypeName:System.String
PS C:\>用户ID
1
2
PS C:$> $ hashtable.UserID | GM
TypeName:System.Double
方法2:包含类型信息的CSV文件
这种方法使用我习惯 Import-Csv
的传统方式要容易一些,但是我无法将结果合并为一个变量。
示例CSV文件:
p>
#TYPE System.String
名称
彼得
詹妮弗
并且
#TYPE System.Double
UserID;薪金
1; 1200.03
2; 1000.50
使用 Import-Csv
的示例结果:
PS C:\> Import-Csv .\double.csv -Delimiter';'
用户ID薪水
------ ------
1 1200.03
2 1000.50
PS C:\ Import-Csv .\double.csv-分隔符’;’| GM
TypeName:CSV:System.Double
可选方法:如果您很勇敢,有人在这里为 Import-Csv
编写代理脚本:从CSV文件中导入具有类型属性的类型对象,但是我做到了不测试我自己。我之所以提及它,是因为如果它经常出现,这似乎是解决该问题的一种好方法。
我找到了这篇有关如何处理某些哈希表的问题会有所帮助。
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?
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更改Data.DataTable对象的数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!