Input: text file input.txt. Each line has multiple values (columns), delimiter is TAB character
Output: output.xls
We will code a VBScript text2excel.vbs This script will scan all txt files in the current directory, then convert them to excel files.
You can download source code VBS from here, or copy following code:
Const ForReading = 1
Const ForWriting = 2
Const xlDelimited = 1
Const xlWorkbookNormal = -4143
Const xlOpenXMLWorkbook = 51 ' XLSX
Const xlExclusive = 3
Const xlLocalSessionChanges = 2
Const xlGeneralFormat = 1
Const xlTextFormat = 2
sFolder = "."
Set oFSO = CreateObject("Scripting.FileSystemObject")
For Each oFile In oFSO.GetFolder(sFolder).Files
If UCase(oFSO.GetExtensionName(oFile.Name)) = "TXT" Then
Wscript.Echo oFile.Name & " => " & Replace(oFile.Name, "txt", "xlsx")
ProcessFiles oFSO, oFile
End if
Next
Set oFSO = Nothing
Sub ProcessFiles(FSO, File)
Dim sFileXlsx, strCurDir
Set WshShell = CreateObject("WScript.Shell")
strCurDir = WshShell.CurrentDirectory
sFileXlsx = Replace(File.Name, "txt", "xlsx")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = NOT True
objExcel.Workbooks.OpenText File.Path, 65001, , xlDelimited, , , , , , , True, "~", Array(Array(1, xlTextFormat),Array(2, xlTextFormat),Array(3, xlTextFormat),Array(4, xlTextFormat),Array(5, xlTextFormat),Array(6, xlTextFormat))
objExcel.Workbooks(File.Name).Application.DisplayAlerts = false
'objExcel.Workbooks(File.Name).Sheets(1).Name = Replace(File.Name, ".txt", "")
objExcel.Workbooks(File.Name).Sheets(1).Name = "Sheet1"
objExcel.Workbooks(File.Name).SaveAs strCurDir & "\" & sFileXlsx, xlOpenXMLWorkbook, , , , , xlExclusive, xlLocalSessionChanges
objExcel.Workbooks(sFileXlsx).Close
objExcel.Quit
end sub
