excel - PowerShell - Import Excel then Export CSV without using Excel or COM
问题描述
I am developing a PowerShell script to import an Excel file and output the data to a flat file. The code that I have below works fine except that it fails to preserve leading zeros; when the CSV file is opened in a text editor, the leading zeros are not present. (Leading zeros are necessary for certain ID numbers, and the ID numbers are stored in Excel using a custom format.) Does anyone have any thoughts on how to get the ImportExcel module to preserve the leading zeros, or, perhaps another way of getting to the same goal? I would like to do this without using the COM object and without having to install Excel on the server; that's why I've been trying to make the ImportExcel module work.
$dataIn = filename.xlsx ; $dataOut = filename.csv
Import-Excel -Path $dataIn | Export-Csv -Path $dataOut
解决方案
I presume you're using the ImportExcel module?
I just did this and it worked. I created a spreadsheet like:
Name ID1 ID2
Steven 00012345 00012346
I gave them a custom number format of 00000000 then ran:
Import-Excel .\Book1.xlsx | Export-Csv .\book1.csv
When looking at the csv file I have both ID numbers as quoted strings:
"Name","ID1","ID2" "Steven","00012345","00012346"
Is there anything else I need to do to reproduce this? Can you give the specifics of the custom number format?
Also withstanding your answer to above. You can modify the properties of each incoming object by converting them to strings. Assuming there's a fixed number of digits you can use the string format with the .ToString() method like:
(12345).ToString( "00000000" )
This will return "00012345"...
So redoing my test with regular numbers (no custom format):
$Input = @(Import-Excel \\nynastech1\adm_only\ExAdm\Temp\Book1.xlsx)
$Input |
ForEach{
$_.ID1 = $_.ID1.ToString( "00000000" )
$_.ID2 = $_.ID2.ToString( "00000000" )
}
This will convert ID1 & ID2 into "00012345" & "00012345" respectively.
You can also use Select-Object, but you might need to rename the properties. If you are interested I can demo that approach.
Note: the @() wrapping in my example is because I only have the 1 object, and is partly force of habit.
Let me know how it goes.
推荐阅读
- powershell - 如何替换 powershell 字符串中第一次出现的句点?
- javascript - 如何在滑块 Swiper.js 之间设置控件?
- c++ - 在使用 CQtDeployer 部署时,如何将依赖的可执行文件也复制到主可执行文件所在的同一文件夹中?
- postgresql - Terraform、RDS (Postgres) 和生产
- linux - Q: 为什么有些linux下wtmp的ut_line是":1"
- javascript - 我如何获取 HTML 复选框表单值并检查 ID 的值是否与 User_id 相同以自动增加投票
- mysql - 用sql将连续日期期间的记录添加到一条记录中
- php - 从控制器新建/更新 laravel 命令的计划?拉拉维尔 8
- c - 创建了两个子进程。父进程应该一直执行到一个子进程终止。我如何用c编写这个程序?
- java - 生成 GUI 源时找不到类 java.lang.NoClassDefFoundError: com/sun/xml/bind/v2/model/annotation/AnnotationReader [Codenameone]