在VBScript中将txt文件转换为EXCEL [英] Convert txt file to excel in vbscript
本文介绍了在VBScript中将txt文件转换为EXCEL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将文本文件转换为Excel工作表。这就是格式的样子。
我尝试过编写脚本,但目前它所做的只是覆盖我当前的文本文件,添加我的列标题。它不会添加我的文本文件中的任何数据。有人能帮我弄明白我做错了什么吗?
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
strInput=InputBox("Enter name of File in C:UsersspencerrDesktopMyProjectin")
'ask user for file name
Set wb = objExcel.Workbooks.Open("C:UsersobDesktopMyProjectin" & strInput)
'Delete labels in log
For i = 1 To 5
Set objRange = objExcel.Cells(1, i).EntireColumn
objRange.Delete
Next
Set activeCell = objExcel.Cells(1, 2)
Dim intVal
Dim comVal
Dim primeRow
Dim largestRow
Dim largestDec
Dim row
primeRow = 0
'filter out one measurement per second
Do Until IsEmpty(activeCell)
primeRow = primeRow + 1
'get base integer of first value by chopping off decimal
intVal = Fix(activeCell.Value)
comVal = intVal
'get all consecutive rows that have same base integer
Do While intVal = comVal
row = activeCell.Row
Set activeCell = objExcel.Cells((row + 1), 2)
comVal = Fix(activeCell.Value)
Loop
'highest row number that contains the base integer
largestRow = row
'delete all the rows up to the largest row
j = primeRow
Do While j < largestRow
Set deleteRow = objExcel.Cells(primeRow, 2).EntireRow
deleteRow.Delete
j = j + 1
Loop
'compare the value right below the exact second and the value right above to see
'which is closer to the exact second
Set activeCell = objExcel.Cells(primeRow, 2)
largestDec = activeCell.Value
Set activeCell = objExcel.Cells((primeRow + 1), 2)
comVal = activeCell.Value
if (((intVal + 1) - largestDec) > (comVal - (intVal + 1))) Then
objExcel.Cells(primeRow, 2).EntireRow.Delete
End If
Loop
'round all the seconds that are left to the nearesr second
Set activeCell = objExcel.Cells(1, 2)
Do Until IsEmpty(ActiveCell)
row = activeCell.row
objExcel.Cells(row, 2) = Round(activeCell.Value)
Set activeCell = objExcel.Cells(row + 1, 2)
Loop
'add labels for KML conversion
objExcel.Cells(1,1).EntireRow.Insert
objExcel.Cells(1, 2).Value = "Description"
objExcel.Cells(1, 3).Value = "Latitude"
objExcel.Cells(1, 4). Value = "Longitude"
wb.Save
wb.Close
objExcel.Quit
推荐答案
我将使用正则表达式将数据转换为csv格式:
Set fso = CreateObject("Scripting.FileSystemObject")
Set inFile = fso.OpenTextFile("C:path oinput.txt")
Set outFile = fso.OpenTextFile("C:path ooutput.csv", 2, True)
Set re = New RegExp
re.Pattern = "^week: (d+) seconds: (d+.d+) x: (d+.d+) " & _
"y: (-d+.d+) heading: (d+)$"
re.IgnoreCase = True
outFile.WriteLine "Week,Seconds,X,Y,Heading"
Do Until inFile.AtEndOfStream
For Each m In re.Execute(inFile.ReadLine)
outFile.WriteLine m.Submatches(0) & "," & m.Submatches(1) & "," & _
m.Submatches(2) & "," & m.Submatches(3) & "," & m.Submatches(4)
Next
Loop
inFile.Close
outFile.Close
然后您可以使用Excel打开CSV文件并将其另存为工作簿。
这篇关于在VBScript中将txt文件转换为EXCEL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文