Converting delimited text files to excel files


Automatically and silently converts a delimited text file into an excel file.

Download the .vbs file:

or Copy code:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' 'this code converts a "comma separated values" into a xls file 'usage: cscript cvs2xls.vbs source_full_path target_directory delimiter Option Explicit Dim strXlsFile,objExl,strSrcFullPath,strSrcFileName,objTmpArray,strDelimiter,strTargetDir,strExtension Const xlDelimited = 1 Const xlTextQualifierDoubleQuote = 1 'Double quotation mark ("). Const xlTextQualifierNone = -4142 'No delimiter. Const xlTextQualifierSingleQuote = 2 'Single quotation mark ('). If( WScript.Arguments.Count < 3 ) Then WScript.Echo( vbCrLf &" usage: cscript cvs2xls.vbs source_full_path target_directory delimiter") WScript.Echo( vbCrLf &" example: " &vbCrLf &" cscript csv2xls.vbs c:\tmp\cvsfiles\file001.csv c:\temp\xlsfiles @") WScript.Quit() End If strSrcFullPath = WScript.Arguments.Item(0) strTargetDir = WScript.Arguments.Item(1) strDelimiter = WScript.Arguments.Item(2) objTmpArray = Split(strSrcFullPath,"\") strSrcFileName = objTmpArray(UBound(objTmpArray)) If( Right(strTargetDir,1) <> "\" ) Then strTargetDir = strTargetDir & "\" End If objTmpArray = Split(strSrcFileName,".") strExtension = objTmpArray(UBound(objTmpArray)) strSrcFileName = Replace(strSrcFileName,"."&strExtension,"") strXlsFile = strTargetDir &strSrcFileName &".xls" Set objExl = CreateObject("Excel.Application") objExl.Visible = False objExl.CutCopyMode = False objExl.Workbooks.OpenText strSrcFullPath,,,xlDelimited,xlTextQualifierDoubleQuote,,,,,,True,strDelimiter objExl.Cells.Select objExl.Selection.Columns.AutoFit objExl.Range("A1").Select objExl.ActiveWorkbook.SaveAs strXlsFile, -4143 objExl.ActiveWorkbook.Close True objExl.Application.Quit Set objExl = Nothing ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


1. Save the code in a text file and name it csv2xls.vbs (or any other name) 2. Place the file in a folder that is registered in the Path, like C:\WINDOWS\system32 or run csv2xls.vbs from its location. 3. Done! Snapshot: Shortcut configuration


If you need to process a large amount of files you could do the following from the command prompt. Assuming you have the source files in c:\cvsfiles then the command will look like this: for /r c:\cvsfiles\ %i in (*.csv) do @csv2xls "%i" c:\xlsfiles\ @
© 2006 Javier Hernandez. All rights reserved. Terms of Use