在VBScript中将txt文件转换为EXCEL [英] Convert txt file to excel in vbscript

问题描述

我正在尝试将文本文件转换为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屋！