Удалить начальные нули в CSV-файле только из значений int
У меня есть этот 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 ,[email protected] ,Leo ,Turtle ,0661203, , , ,J ,2345 , , ,RNixon , , ,
0000264 , ,Richard ,Nixon ,0311247, , , ,J ,3456 , , , , , ,
0000291 ,[email protected] ,asdfasd ,Random ,0841212, , , ,J ,4567 , , ,aRandom , , ,
8984207 ,[email protected] ,gfdgssg ,Random ,0883461,p209557, ,20160531,J , , , , , , ,
8984208 ,[email protected] ,hgdhdfd ,Random ,0883461,p209558, ,20151202,J , ,P209558 , , , , ,
8984209 ,[email protected] , ,Random ,0883461,p209559, ,20160531,J , ,P209559 , , , , ,
8984210 ,[email protected] ,zxcvzvc ,Random ,0883461,p209561, ,20151202,J , , , , , , ,
231716352 ,[email protected] ,asdf ,Random , , , , , ,5678 , , , , , ,
0118969DEL20151007 , ,asdfg ,Random , ,s051699, , ,J ,6789 , , , , , ,
0472549DEL20151021 ,[email protected] ,asdfgh ,Random ,1565030,sB597RQ, , ,J ,7890 , , , , , ,
0812310DEL111115 ,[email protected] ,asdfghj ,Random ,1562482,s052177, , ,J ,0987 , , , , , ,
0864549DEL20151029 , ,asdfghjs ,Random ,0561118,s051645, ,20150322,J ,9867 , , , , , ,
0895960DEL20151007 , ,lkjh ,Random , ,s85GRHZ, , ,J ,8756 , , , , , ,
0916691DEL20151021 ,[email protected] ,lkjhg ,Random ,1565030,s051954, , ,J ,7654 , , , , , ,
0937846DEL110920151 ,[email protected] ,lkjhgf ,Random , , , , ,J ,6543 , , , , , ,
10262013%DEL%8912644DEL10672015,[email protected] ,lkjhgfd ,Random ,0064035, , ,20150928,J , , , , , , ,
10262013%DEL%8941949DEL106815 ,[email protected] ,bnvxz ,Random ,886442 , , ,20151030,J , , , , , , ,
10262013%DEL%8943747DEL10692015,[email protected] ,zxcv ,Random , , , ,20151030,J , , , , , , ,
10262013%DEL%8946048DEL20651027,[email protected] ,sgarnwxasf,Random ,1173175, , ,20150801,J , , , ,SRandom , , ,
10262013%DEL%8949542DEL106915 ,[email protected] ,Bob ,Bob , , , ,20150920,X , , , ,BBob , , ,
10262013%DEL%8953549DEL106115 , ,John ,Doe , , , ,20151131,J , , , , , , ,
10262013%DEL%8957746DEL116315 ,[email protected] ,Homer ,Simpson ,T131456, , ,20150928,X , , , ,HSimpson , , ,
10262013%DEL%8961640 ,[email protected] ,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"
Проблема в том, что строки (с альфа и специальными символами) не могут быть преобразованы в [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" ,"[email protected]" ,"Leo" ,"Turtle" ,"0661203","" ,"" ,"" ,"J" ,"2345" ,"" ,"" ,"RNixon" ,"" ,"" ,""
"264" ,"" ,"Richard" ,"Nixon" ,"0311247","" ,"" ,"" ,"J" ,"3456" ,"" ,"" ,"" ,"" ,"" ,""
"291" ,"[email protected]" ,"asdfasd" ,"Random" ,"0841212","" ,"" ,"" ,"J" ,"4567" ,"" ,"" ,"aRandom" ,"" ,"" ,""
"8984207" ,"[email protected]" ,"gfdgssg" ,"Random" ,"0883461","p209557","" ,"20160531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"8984208" ,"[email protected]" ,"hgdhdfd" ,"Random" ,"0883461","p209558","" ,"20151202","J" ,"" ,"P209558" ,"" ,"" ,"" ,"" ,""
"8984209" ,"[email protected]" ,"" ,"Random" ,"0883461","p209559","" ,"20160531","J" ,"" ,"P209559" ,"" ,"" ,"" ,"" ,""
"8984210" ,"[email protected]" ,"zxcvzvc" ,"Random" ,"0883461","p209581","" ,"20151202","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"231716352","[email protected]" ,"asdf" ,"Random" ,"" ,"" ,"" ,"" ,"" ,"5678" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"asdfg" ,"Random" ,"" ,"s051699","" ,"" ,"J" ,"6789" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"asdfgh" ,"Random" ,"1565030","sB597RQ","" ,"" ,"J" ,"7890" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"asdfghj" ,"Random" ,"1562482","s052177","" ,"" ,"J" ,"0987" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"asdfghjs" ,"Random" ,"0561118","s051645","" ,"20150322","J" ,"9867" ,"" ,"" ,"" ,"" ,"" ,""
,"" ,"lkjh" ,"Random" ,"" ,"s85GRHZ","" ,"" ,"J" ,"8756" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"lkjhg" ,"Random" ,"1565030","s051954","" ,"" ,"J" ,"7654" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"lkjhgf" ,"Random" ,"" ,"" ,"" ,"" ,"J" ,"6543" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"lkjhgfd" ,"Random" ,"0064035","" ,"" ,"20150928","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"bnvxz" ,"Random" ,"886442" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"zxcv" ,"Random" ,"" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]","sgarnwxasf","Random" ,"1173175","" ,"" ,"20150801","J" ,"" ,"" ,"" ,"SRandom" ,"" ,"" ,""
,"[email protected]" ,"Bob" ,"Bob" ,"" ,"" ,"" ,"20150920","X" ,"" ,"" ,"" ,"BBob" ,"" ,"" ,""
,"" ,"John" ,"Doe" ,"" ,"" ,"" ,"20151131","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
,"[email protected]" ,"Homer" ,"Simpson" ,"T131456","" ,"" ,"20150928","X" ,"" ,"" ,"" ,"HSimpson","" ,"" ,""
,"[email protected]" ,"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" ,"[email protected]" ,"Leo" ,"Turtle" ,"0661203","" ,"" ,"" ,"J" ,"2345" ,"" ,"" ,"RNixon" ,"" ,"" ,""
"264" ,"" ,"Richard" ,"Nixon" ,"0311247","" ,"" ,"" ,"J" ,"3456" ,"" ,"" ,"" ,"" ,"" ,""
"291" ,"[email protected]" ,"asdfasd" ,"Random" ,"0841212","" ,"" ,"" ,"J" ,"4567" ,"" ,"" ,"aRandom" ,"" ,"" ,""
"8984207" ,"[email protected]" ,"gfdgssg" ,"Random" ,"0883461","p209557","" ,"20160531","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"8984208" ,"[email protected]" ,"hgdhdfd" ,"Random" ,"0883461","p209558","" ,"20151202","J" ,"" ,"P209558" ,"" ,"" ,"" ,"" ,""
"8984209" ,"[email protected]" ,"" ,"Random" ,"0883461","p209559","" ,"20160531","J" ,"" ,"P209559" ,"" ,"" ,"" ,"" ,""
"8984210" ,"[email protected]" ,"zxcvzvc" ,"Random" ,"0883461","p209581","" ,"20151202","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"231716352" ,"[email protected]" ,"asdf" ,"Random" ,"" ,"" ,"" ,"" ,"" ,"5678" ,"" ,"" ,"" ,"" ,"" ,""
"0118969DEL20151007" ,"" ,"asdfg" ,"Random" ,"" ,"s051699","" ,"" ,"J" ,"6789" ,"" ,"" ,"" ,"" ,"" ,""
"0472549DEL20151021" ,"[email protected]" ,"asdfgh" ,"Random" ,"1565030","sB597RQ","" ,"" ,"J" ,"7890" ,"" ,"" ,"" ,"" ,"" ,""
"0812310DEL111115" ,"[email protected]" ,"asdfghj" ,"Random" ,"1562482","s052177","" ,"" ,"J" ,"0987" ,"" ,"" ,"" ,"" ,"" ,""
"0864549DEL20151029" ,"" ,"asdfghjs" ,"Random" ,"0561118","s051645","" ,"20150322","J" ,"9867" ,"" ,"" ,"" ,"" ,"" ,""
"0895960DEL20151007" ,"" ,"lkjh" ,"Random" ,"" ,"s85GRHZ","" ,"" ,"J" ,"8756" ,"" ,"" ,"" ,"" ,"" ,""
"0916691DEL20151021" ,"[email protected]" ,"lkjhg" ,"Random" ,"1565030","s051954","" ,"" ,"J" ,"7654" ,"" ,"" ,"" ,"" ,"" ,""
"0937846DEL110920151" ,"[email protected]" ,"lkjhgf" ,"Random" ,"" ,"" ,"" ,"" ,"J" ,"6543" ,"" ,"" ,"" ,"" ,"" ,""
"10262013%DEL%8912644DEL10672015","[email protected]" ,"lkjhgfd" ,"Random" ,"0064035","" ,"" ,"20150928","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"10262013%DEL%8941949DEL106815" ,"[email protected]" ,"bnvxz" ,"Random" ,"886442" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"10262013%DEL%8943747DEL10692015","[email protected]" ,"zxcv" ,"Random" ,"" ,"" ,"" ,"20151030","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"10262013%DEL%8946048DEL20651027","[email protected]","sgarnwxasf","Random" ,"1173175","" ,"" ,"20150801","J" ,"" ,"" ,"" ,"SRandom" ,"" ,"" ,""
"10262013%DEL%8949542DEL106915" ,"[email protected]" ,"Bob" ,"Bob" ,"" ,"" ,"" ,"20150920","X" ,"" ,"" ,"" ,"BBob" ,"" ,"" ,""
"10262013%DEL%8953549DEL106115" ,"" ,"John" ,"Doe" ,"" ,"" ,"" ,"20151131","J" ,"" ,"" ,"" ,"" ,"" ,"" ,""
"10262013%DEL%8957746DEL116315" ,"[email protected]" ,"Homer" ,"Simpson" ,"T131456","" ,"" ,"20150928","X" ,"" ,"" ,"" ,"HSimpson" ,"" ,"" ,""
"10262013%DEL%8961640" ,"[email protected]" ,"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 остается тем же. Если бы кто-нибудь мог помочь мне с синтаксисом, я был бы благодарен.