Excel – Convert txt file to excel in vbscript

excelvbscript

I am trying to convert a text file into an excel sheet. This is what the format looks like.

I have tried writing a script but currently all it does is overwrites my current text file adding my column headers. It does not add any of the data from my text file. Could anyone help me understand what I am doing wrong.

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

Best Answer

I'd use a regular expression to transform the data into CSV format:

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

Then you can open the CSV file with Excel and save it as a workbook.

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.

You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.

There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.

Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License

Here some example code for ExcelLibrary:

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

C# – How to properly clean up Excel interop objects

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

Best Answer

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

C# – How to properly clean up Excel interop objects

Related Topic