bat读取文本内指定列数据并保存excel里

其实很简单 · 发表于 2021-6-3 23:57:09

本帖最后由其实很简单于 2021-6-3 23:58 编辑

有若干txt文件，文件内容格式均一致，需读取指定列数据并新建同名文件保存至excel里。
文件格式如下：

VERSION:1
1 2021-05-19 20:17:52:000 30.14725478517 N 120.27266472550 E 9.972 M 5
3 2021-05-19 20:17:55:796 30.14725439400 N 120.27266557500 E 9.766 M 5
6 2021-05-19 20:17:56:000 30.14725330000 N 120.27266571933 E 9.764 M 5
9 2021-05-19 20:17:56:203 30.14725187517 N 120.27266571733 E 9.775 M 5
14 2021-05-19 20:17:56:406 30.14725011617 N 120.27266591450 E 9.785 M 5
19 2021-05-19 20:17:56:593 30.14724799233 N 120.27266614883 E 9.771 M 5
25 2021-05-19 20:17:56:796 30.14724538767 N 120.27266642133 E 9.793 M 5
32 2021-05-19 20:17:57:000 30.14724218717 N 120.27266678900 E 9.841 M 5
39 2021-05-19 20:17:57:203 30.14723871400 N 120.27266719333 E 9.837 M 5

需读取第1列、第4列、第6列、第8列数据至excel里，读取后格式如下：
1 30.14725479 120.2726647 9.972
3 30.14725439 120.2726656 9.766
6 30.1472533 120.2726657 9.764
9 30.14725188 120.2726657 9.775
14 30.14725012 120.2726659 9.785
19 30.14724799 120.2726661 9.771
25 30.14724539 120.2726664 9.793
32 30.14724219 120.2726668 9.841
39 30.14723871 120.2726672 9.837

备注：第一行数据不要读取是从第二行开始读取才对

想请问bat怎么写？

newswan · 发表于 2021-6-4 17:56:45

本帖最后由 newswan 于 2021-6-4 20:21 编辑

$fileTxt = "1.txt"
$fileExcel = "1.xlsx"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.displayAlerts = $true
$wb = $excel.Workbooks.add()
$filec = Get-Content -Path $fileTxt
$wb.sheets(1).columns(1).numberformatlocal= "general"
$c = 0,1,4,6,8
for ( $i = 1 ; $i -lt $filec.Count ; $i++ )
{
$s = $fileC[$i] -replace "\s+",","
$sa = ("," + $s).Split(",")
for ( $j = 1 ; $j -lt $c.count ; $j++ )
{
$wb.sheets(1).cells($i,$j)=$sa[$c[$j]]
}
}
$wb.Saveas($fileExcel)
$excel.Quit()
$excel = $null
[GC]::Collect()

复制代码

powershell 还是比较方便，vba 做这个也方便
excel 行列从 1 开始，数组是从 0 开始，麻烦

其实很简单 · 发表于 2021-6-5 09:11:20

回复 2# newswan

你好请问怎么动态获取当前目录下的txt并读取至excel里因为我文件很多

newswan · 发表于 2021-6-5 11:04:33

$sourPath = "D:\1"
$destPath = "D:\2"
$sourText = get-childitem -path $sourPath "*.txt"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.displayAlerts = $true
$c = 0,1,4,6,8
foreach ( $f in $sourText )
{
$filec = Get-Content -Path $f.fullname
$wb = $excel.Workbooks.add()
$wb.sheets(1).Columns("A:D").numberformatlocal= "general"
for ( $i = 1 ; $i -lt $filec.Count ; $i++ )
{
$s = $fileC[$i] -replace "\s+",","
$sa = ("," + $s).Split(",")
for ( $j = 1 ; $j -lt $c.count ; $j++ )
{
$wb.sheets(1).cells($i,$j)=$sa[$c[$j]]
}
}
$wb.Saveas($sourPath + $f.basename + ".xlsx")
$excel.Quit()
}
$excel = $null
[GC]::Collect()

复制代码

未测试

其实很简单 · 发表于 2021-6-5 15:52:18

回复 4# newswan

对这个powershell不懂代码运行报错谢谢大佬！

		自动登录	找回密码
密码			注册

[新手上路]批处理新手入门导读	[视频教程]批处理基础视频教程	[视频教程]VBS基础视频教程	[批处理精品]批处理版照片整理器
[批处理精品]纯批处理备份&还原驱动	[批处理精品]CMD命令50条不能说的秘密	[在线下载]第三方命令行工具	[在线帮助]VBScript / JScript 在线参考

[文本处理] bat读取文本内指定列数据并保存excel里

评分