我有这个csv文件我正在尝试删除前导零,但是我的代码中包含任何带有字母或特殊字符的数字,它会使值无效,我不希望...
column1 ,column2 ,column3 ,column4 ,column5,column6,column7,column8 ,column9,column10,column11,column12,column13 ,column14,column15,column16 0000013 , ,ShopKeep ,Store , , , , , ,1034627 , , , , , , 0000032 , ,Test411 ,User411 ,0007643, , , , ,1034632 , , , , , , 0000142 , ,Pinky ,Brain ,0571253, , , ,J ,1234 , , , , , , 0000145 ,Leo@ninjaturtles.com ,Leo ,Turtle ,0661203, , , ,J ,2345 , , ,RNixon , , , 0000264 , ,Richard ,Nixon ,0311247, , , ,J ,3456 , , , , , , 0000291 ,asdfasd.Random@random.com ,asdfasd ,Random ,0841212, , , ,J ,4567 , , ,aRandom , , , 8984207 ,gfdgssg.Random@random.com ,gfdgssg ,Random ,0883461,p209557, ,20160531,J , , , , , , , 8984208 ,hgdhdfd.Random@random.com ,hgdhdfd ,Random ,0883461,p209558, ,20151202,J , ,P209558 , , , , , 8984209 ,blankity.Random@random.com , ,Random ,0883461,p209559, ,20160531,J , ,P209559 , , , , , 8984210 ,zxcvzvc.Random@random.com ,zxcvzvc ,Random ,0883461,p209561, ,20151202,J , , , , , , , 231716352 ,asdf-Random@random.com ,asdf ,Random , , , , , ,5678 , , , , , , 0118969DEL20151007 , ,asdfg ,Random , ,s051699, , ,J ,6789 , , , , , , 0472549DEL20151021 ,asdfgh.Random@random.com ,asdfgh ,Random ,1565030,sB597RQ, , ,J ,7890 , , , , , , 0812310DEL111115 ,asdfghj.Random@random.com ,asdfghj ,Random ,1562482,s052177, , ,J ,0987 , , , , , , 0864549DEL20151029 , ,asdfghjs ,Random ,0561118,s051645, ,20150322,J ,9867 , , , , , , 0895960DEL20151007 , ,lkjh ,Random , ,s85GRHZ, , ,J ,8756 , , , , , , 0916691DEL20151021 ,lkjhg.Random@random.com ,lkjhg ,Random ,1565030,s051954, , ,J ,7654 , , , , , , 0937846DEL110920151 ,lkjhgf.Random@random.com ,lkjhgf ,Random , , , , ,J ,6543 , , , , , , 10262013%DEL%8912644DEL10672015,lkjhgfd.Random@random.com ,lkjhgfd ,Random ,0064035, , ,20150928,J , , , , , , , 10262013%DEL%8941949DEL106815 ,bnvxz.Random@random.com ,bnvxz ,Random ,886442 , , ,20151030,J , , , , , , , 10262013%DEL%8943747DEL10692015,zxcv.Random@random.com ,zxcv ,Random , , , ,20151030,J , , , , , , , 10262013%DEL%8946048DEL20651027,sgarnwxasf.Random@random.com ,sgarnwxasf,Random ,1173175, , ,20150801,J , , , ,SRandom , , , 10262013%DEL%8949542DEL106915 ,Bob.Bob@Normal.com ,Bob ,Bob , , , ,20150920,X , , , ,BBob , , , 10262013%DEL%8953549DEL106115 , ,John ,Doe , , , ,20151131,J , , , , , , , 10262013%DEL%8957746DEL116315 ,Homer.Simpson@Simpsons.com ,Homer ,Simpson ,T131456, , ,20150928,X , , , ,HSimpson , , , 10262013%DEL%8961640 ,Peter.Griffon@familyguy.com ,Peter ,Griffon ,1131998, , ,20151030,J , , , , , , , 10262013%DEL%8961742 , ,John ,Steward ,1131823, , ,20150414,J , , , ,JSteward , , , 10262013%DEL%8968648DEL10692015, ,Hello ,World ,1131440, , ,20170531,J , , , , , , , 10262013%DEL%8968649DEL10692015, ,Hello ,World ,1131440, , ,20170531,J , , , , , , ,
现在我使用以下代码删除前导零...
Write-Host "Removing leading zeros from column `"column1`" in C:\path\test.csv"
Import-Csv "C:\path\test.csv" |
select -Property @{n='column1';e={[int]$_.column1}},* -Exclude 'column1' |
Export-Csv "C:\path\testtemp" -NoTypeInformation
Move-Item "C:\path\testtemp" "C:\path\test.csv" -Force
Write-Host "Complete"
问题是字符串(带有alpha和特殊字符)无法转换为[int],它们将被删除,如下所示...
"column1" ,"column2" ,"column3" ,"column4" ,"column5","column6","column7","column8" ,"column9","column10","column11","column12","column13","column14","column15","column16"
"13" ,"" ,"ShopKeep" ,"Store" ,"" ,"" ,"" ,"" ,"" ,"1034627" ,"" ,"" ,"" ,"" ,"" ,""
"32" ,"" ,"Test411" ,"User411" ,"0007643","" ,"" ,"" ,"" ,"1034632" ,"" ,"" ,"" ,"" ,"" ,""
"142" ,"" ,"Pinky" ,"Brain" ,"0571253","" ,"" ,"" ,"J" ,"1234" ,"" ,"" ,"" ,"" ,"" ,""
"145" ,"Leo@ninjaturtles.com" ,"Leo" ,"Turtle" ,"0661203","" ,"" ,"" ,"J" ,"2345" ,"" ,"" ,"RNixon" ,"" ,"" ,""
"264" ,"" ,"Richard" ,"Nixon" ,"0311247","" ,"" ,"" ,"J" ,"3456" ,"" ,"" ,"" ,"" ,"" ,""
"291" ,"asdfasd.Random@random.com" ,"asdfasd" ,"Random" ,"0841212","" ,"" ,"" ,"J" ,"4567" ,"" ,"" ,"aRandom" ,"" ,"" ,""
"8984207" ,"gfdgssg.Random@random.com" ,"gfdgssg" ,"Random" ,"0883461","p209557","" ,"20160531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"8984208" ,"hgdhdfd.Random@random.com" ,"hgdhdfd" ,"Random" ,"0883461","p209558","" ,"20151202","J" ,"" ,"P209558" ,"" ,"" ,"" ,"" ,""
"8984209" ,"blankity.Random@random.com" ,"" ,"Random" ,"0883461","p209559","" ,"20160531","J" ,"" ,"P209559" ,"" ,"" ,"" ,"" ,""
"8984210" ,"zxcvzvc.Random@random.com" ,"zxcvzvc" ,"Random" ,"0883461","p209581","" ,"20151202","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"231716352","asdf-Random@random.com" ,"asdf" ,"Random" ,"" ,"" ,"" ,"" ,"" ,"5678" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"asdfg" ,"Random" ,"" ,"s051699","" ,"" ,"J" ,"6789" ,"" ,"" ,"" ,"" ,"" ,""
,"asdfgh.Random@random.com" ,"asdfgh" ,"Random" ,"1565030","sB597RQ","" ,"" ,"J" ,"7890" ,"" ,"" ,"" ,"" ,"" ,""
,"asdfghj.Random@random.com" ,"asdfghj" ,"Random" ,"1562482","s052177","" ,"" ,"J" ,"0987" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"asdfghjs" ,"Random" ,"0561118","s051645","" ,"20150322","J" ,"9867" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"lkjh" ,"Random" ,"" ,"s85GRHZ","" ,"" ,"J" ,"8756" ,"" ,"" ,"" ,"" ,"" ,""
,"lkjhg.Random@random.com" ,"lkjhg" ,"Random" ,"1565030","s051954","" ,"" ,"J" ,"7654" ,"" ,"" ,"" ,"" ,"" ,""
,"lkjhgf.Random@random.com" ,"lkjhgf" ,"Random" ,"" ,"" ,"" ,"" ,"J" ,"6543" ,"" ,"" ,"" ,"" ,"" ,""
,"lkjhgfd.Random@random.com" ,"lkjhgfd" ,"Random" ,"0064035","" ,"" ,"20150928","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"bnvxz.Random@random.com" ,"bnvxz" ,"Random" ,"886442" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"zxcv.Random@random.com" ,"zxcv" ,"Random" ,"" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"sgarnwxasf.Random@random.com","sgarnwxasf","Random" ,"1173175","" ,"" ,"20150801","J" ,"" ,"" ,"" ,"SRandom" ,"" ,"" ,""
,"Bob.Bob@Normal.com" ,"Bob" ,"Bob" ,"" ,"" ,"" ,"20150920","X" ,"" ,"" ,"" ,"BBob" ,"" ,"" ,""
,"" ,"John" ,"Doe" ,"" ,"" ,"" ,"20151131","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"Homer.Simpson@Simpsons.com" ,"Homer" ,"Simpson" ,"T131456","" ,"" ,"20150928","X" ,"" ,"" ,"" ,"HSimpson","" ,"" ,""
,"Peter.Griffon@familyguy.com" ,"Peter" ,"Griffon" ,"1131998","" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"John" ,"Steward" ,"1131823","" ,"" ,"20150414","J" ,"" ,"" ,"" ,"JSteward","" ,"" ,""
,"" ,"Hello" ,"World" ,"1131440","" ,"" ,"20170531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"Hello" ,"World" ,"1131440","" ,"" ,"20170531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
最终目标
我需要最终结果看起来像这样......
"column1" ,"column2" ,"column3" ,"column4" ,"column5","column6","column7","column8" ,"column9","column10","column11","column12","column13","column14","column15","column16" "13" ,"" ,"ShopKeep" ,"Store" ,"" ,"" ,"" ,"" ,"" ,"1034627" ,"" ,"" ,"" ,"" ,"" ,"" "32" ,"" ,"Test411" ,"User411" ,"0007643","" ,"" ,"" ,"" ,"1034632" ,"" ,"" ,"" ,"" ,"" ,"" "142" ,"" ,"Pinky" ,"Brain" ,"0571253","" ,"" ,"" ,"J" ,"1234" ,"" ,"" ,"" ,"" ,"" ,"" "145" ,"Leo@ninjaturtles.com" ,"Leo" ,"Turtle" ,"0661203","" ,"" ,"" ,"J" ,"2345" ,"" ,"" ,"RNixon" ,"" ,"" ,"" "264" ,"" ,"Richard" ,"Nixon" ,"0311247","" ,"" ,"" ,"J" ,"3456" ,"" ,"" ,"" ,"" ,"" ,"" "291" ,"asdfasd.Random@random.com" ,"asdfasd" ,"Random" ,"0841212","" ,"" ,"" ,"J" ,"4567" ,"" ,"" ,"aRandom" ,"" ,"" ,"" "8984207" ,"gfdgssg.Random@random.com" ,"gfdgssg" ,"Random" ,"0883461","p209557","" ,"20160531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "8984208" ,"hgdhdfd.Random@random.com" ,"hgdhdfd" ,"Random" ,"0883461","p209558","" ,"20151202","J" ,"" ,"P209558" ,"" ,"" ,"" ,"" ,"" "8984209" ,"blankity.Random@random.com" ,"" ,"Random" ,"0883461","p209559","" ,"20160531","J" ,"" ,"P209559" ,"" ,"" ,"" ,"" ,"" "8984210" ,"zxcvzvc.Random@random.com" ,"zxcvzvc" ,"Random" ,"0883461","p209581","" ,"20151202","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "231716352" ,"asdf-Random@random.com" ,"asdf" ,"Random" ,"" ,"" ,"" ,"" ,"" ,"5678" ,"" ,"" ,"" ,"" ,"" ,"" "0118969DEL20151007" ,"" ,"asdfg" ,"Random" ,"" ,"s051699","" ,"" ,"J" ,"6789" ,"" ,"" ,"" ,"" ,"" ,"" "0472549DEL20151021" ,"asdfgh.Random@random.com" ,"asdfgh" ,"Random" ,"1565030","sB597RQ","" ,"" ,"J" ,"7890" ,"" ,"" ,"" ,"" ,"" ,"" "0812310DEL111115" ,"asdfghj.Random@random.com" ,"asdfghj" ,"Random" ,"1562482","s052177","" ,"" ,"J" ,"0987" ,"" ,"" ,"" ,"" ,"" ,"" "0864549DEL20151029" ,"" ,"asdfghjs" ,"Random" ,"0561118","s051645","" ,"20150322","J" ,"9867" ,"" ,"" ,"" ,"" ,"" ,"" "0895960DEL20151007" ,"" ,"lkjh" ,"Random" ,"" ,"s85GRHZ","" ,"" ,"J" ,"8756" ,"" ,"" ,"" ,"" ,"" ,"" "0916691DEL20151021" ,"lkjhg.Random@random.com" ,"lkjhg" ,"Random" ,"1565030","s051954","" ,"" ,"J" ,"7654" ,"" ,"" ,"" ,"" ,"" ,"" "0937846DEL110920151" ,"lkjhgf.Random@random.com" ,"lkjhgf" ,"Random" ,"" ,"" ,"" ,"" ,"J" ,"6543" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8912644DEL10672015","lkjhgfd.Random@random.com" ,"lkjhgfd" ,"Random" ,"0064035","" ,"" ,"20150928","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8941949DEL106815" ,"bnvxz.Random@random.com" ,"bnvxz" ,"Random" ,"886442" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8943747DEL10692015","zxcv.Random@random.com" ,"zxcv" ,"Random" ,"" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8946048DEL20651027","sgarnwxasf.Random@random.com","sgarnwxasf","Random" ,"1173175","" ,"" ,"20150801","J" ,"" ,"" ,"" ,"SRandom" ,"" ,"" ,"" "10262013%DEL%8949542DEL106915" ,"Bob.Bob@Normal.com" ,"Bob" ,"Bob" ,"" ,"" ,"" ,"20150920","X" ,"" ,"" ,"" ,"BBob" ,"" ,"" ,"" "10262013%DEL%8953549DEL106115" ,"" ,"John" ,"Doe" ,"" ,"" ,"" ,"20151131","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8957746DEL116315" ,"Homer.Simpson@Simpsons.com" ,"Homer" ,"Simpson" ,"T131456","" ,"" ,"20150928","X" ,"" ,"" ,"" ,"HSimpson" ,"" ,"" ,"" "10262013%DEL%8961640" ,"Peter.Griffon@familyguy.com" ,"Peter" ,"Griffon" ,"1131998","" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8961742" ,"" ,"John" ,"Steward" ,"1131823","" ,"" ,"20150414","J" ,"" ,"" ,"" ,"JSteward" ,"" ,"" ,"" "10262013%DEL%8968648DEL10692015","" ,"Hello" ,"World" ,"1131440","" ,"" ,"20170531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,"" "10262013%DEL%8968649DEL10692015","" ,"Hello" ,"World" ,"1131440","" ,"" ,"20170531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""我迄今为止所做的一切......
我已经尝试将代码编辑为以下内容......
Write-Host "Removing leading zeros from column `"column1`" in C:\path\test.csv"
Import-Csv "C:\path\test.csv" |
select -Property @{n='column1';e={If(([int]$_.column1 -ne $null) -or ([int]$_.column1 -ne "")){[int]$_.column1}Else{$_.column1}}},* -Exclude 'column1' |
Export-Csv "C:\path\testtemp" -NoTypeInformation
Move-Item "C:\path\testtemp" "C:\path\test.csv" -Force
Write-Host "Complete"
但是不添加非int值的结果仍然是相同的.如果有人能帮我解释语法,我将不胜感激.
尝试使用".TrimStart()"取出零.
Import-CSV "C:\path\test.csv" | ForEach-Object{
$_.column1 = ($_.column1).TrimStart('0')
$_
} | Export-Csv "C:\path\test2.csv" -NoTypeInformation
Move-Item "C:\path\test2.csv" "C:\path\test.csv" -Force
请注意,为了使其工作,我必须重新格式化您的第一列以取出额外的空格.因此,原始csv中的标头将以:column1,column2等开头.
京公网安备 11010802040832号 | 京ICP备19059560号-6 