excel - 在vbscript中将txt文件转换为excel

Question

我正在尝试将文本文件转换为 Excel 工作表。这就是格式的样子。

我尝试编写一个脚本，但目前它所做的只是覆盖我当前的文本文件，添加我的列标题。它不会添加我的文本文件中的任何数据。谁能帮助我理解我做错了什么。

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

strInput=InputBox("Enter name of File in     C:\Users\spencerr\Desktop\MyProject\bin\")

'ask user for file name
Set wb = objExcel.Workbooks.Open("C:\Users\bob\Desktop\MyProject\bin\" & 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

Answer 1

我会使用正则表达式将数据转换为 CSV 格式:

Set fso = CreateObject("Scripting.FileSystemObject")

Set inFile  = fso.OpenTextFile("C:\path\to\input.txt")
Set outFile = fso.OpenTextFile("C:\path\to\output.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 文件并将其另存为工作簿。