VB Script to open a delimited text file in Microsoft Excel

Hello Experts!

I am trying to do exactly what the title says.... Take File.txt which has columns separated/delimited by the character "|" .

When using excel manually, I have to open excel and then browse to File.txt, then choose "Text" as the file type then since I'm opening a text document with Excel the "Text Import Wizard" comes up... I choose 'Delimited' as the 'original data type' , accept all other defaults, then on step 2 of the wizard I select 'Other' as the delimiter and | in the 'Other:' field. The next page I leave default as General, and click finish.

Now my text file data is open in Excel.....

There must a way to automate this!!

cscript ThanksExperts.vbs!!
LVL 1
zequestionerAsked:
Who is Participating?
 
Bill PrewCommented:
Okay, this works for me here now.

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
  WScript.Echo "Input file does not exist."
  WScript.Quit
End If

' Build the full paths to the input and output files
strInPath = objFSO.GetAbsolutePathName(strInFile)
strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
objExcel.Workbooks.OpenText strInPath,,,xlDelimited,,,,,,,True,"|"
Set objWorkbook = objExcel.ActiveWorkBook
                                            
' Save as an Excel file, close Excel                                            
objWorkbook.SaveAs strOutPath, 51
objWorkbook.Close False
objExcel.Quit

Open in new window

~bp
0
 
Bill PrewCommented:
Here's a small VBS script that should do the job. Save as a VBS file, then run like below from a command line, or just drag the txt file onto the VBS in Explorer.

cscript EE28314307.vbs in.txt

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
  WScript.Echo "Input file does not exist."
  WScript.Quit
End If

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
objExcel.Workbooks.OpenText objFSO.GetAbsolutePathName(strInFile),,,xlDelimited,,,,,,,True,"|"

Open in new window

~bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Robberbaron (robr)Commented:
do you want this to run externally to Excel ?  if so, then BillP's routine is the way to go.
note that the fie.txt is coded into the script.

but if this is to run with Excel already open (or from a master workbook) and then do some further processing, then you want to use VBA, which is slightly different.  Try starting the macro recorder before doing your manual process.
0
 
zequestionerAuthor Commented:
ouch, the text output is coming from a java app, which dumps a huge text file w/ the above mentioned 'Column1 |Column2 | Etc.' format.

This is for a scheduled job, so i was hoping I could do this without opening excel, I just want to import the txt data to excel, save the file, then my plan was to automatically email this out via blat. I don't want to touch the data at all if possible.
0
 
zequestionerAuthor Commented:
this doesn't seem to be working... at least on my office 2013, i will try office 2010 and can go back to 2007 if needed.... running this script opens excel, but none of the imported data is there... it's just a blank worksheet.

It would also be great to save and close the excel file so I can email it off with an smtp tool.
0
 
Robberbaron (robr)Commented:
have to ask... what is the advantage of creating an excel file of just the text data if not doing any processing ?

BillP method seems like the most appropriate as a starting point but I dont know why it would not be loading the data correctly.

myinputfile = objFSO.GetAbsolutePathName(strInFile)
outputxlsfilename = "c:\ee\Test1.xlsx"

wscript.echo "1>>" & myinputfile & "<< "

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True  <<<comment out this when working

' Open delimited file
objExcel.Workbooks.OpenText myinputfile,,,xlDelimited,,,,,,,True,"|"


objExcel.ActiveWorkbook.SaveAs outputxlsfilename, 51

objExcel.ActiveWorkbook.Close False

Open in new window


are you able to supply a test text file with say top 20 lines of real data ?  So we can check that it is processing correctly.
0
 
zequestionerAuthor Commented:
My apologies, there was an issue with the source txt file i was testing with. BillP's method works perfectly. The last example however, which should save the file to c:\ee\test1.xlsx throws an error:

1, 1) Microsoft VBScript runtime error: Object required: 'objFSO'

I updated the script to this:

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

myinputfile = myinputfile = objFSO.GetAbsolutePathName(strInFile)
outputxlsfilename = "C:\ee\Test1.xlsx"

wscript.echo "1>>" & myinputfile & "<< "

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")

'objExcel.Visible = True  <<<comment out this when working

' Open delimited file
objExcel.Workbooks.OpenText myinputfile,,,xlDelimited,,,,,,,True,"|"


objExcel.ActiveWorkbook.SaveAs outputxlsfilename, 51

objExcel.ActiveWorkbook.Close False

Open in new window


But now it gives this error:

1>>False<<
C:\ee\convert.vbs(15, 1) Microsoft Excel: OpenText method of
Workbooks class failed
0
 
Bill PrewCommented:
It worked under 2010 here, at least on a test file I built.  Let me know what you find out relative to 2013, I haven't made the jump there yet.

Yes, we can save the file.

~bp
0
 
zequestionerAuthor Commented:
same with office 2010, are you x86 or x64?
0
 
Bill PrewCommented:
I'm x86.

~bp
0
 
Bill PrewCommented:
Give this a try.

' Define constants
Const xlDelimited = 1

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
  WScript.Echo "Input file does not exist."
  WScript.Quit
End If

' Build the full paths to the input and output files
strInPath = objFSO.GetAbsolutePathName(strInFile)
strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & GetBaseName(strInPath) & ".xlsx"

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

' Open delimited file
Set objWorkbook = objExcel.Workbooks.OpenText(strInPath,,,xlDelimited,,,,,,,True,"|")
                                            
' Save as an Excel file, close Excel                                            
objWorkbook.SaveAs strOutPath, 51
objWorkbook.Close False
objExcel.Quit

Open in new window

~bp
0
 
Robberbaron (robr)Commented:
bills script should fix the error.

which was
myinputfile = myinputfile = objFSO.GetAbsolutePathName(strInFile)

should have been
myinputfile = objFSO.GetAbsolutePathName(strInFile)
0
 
zequestionerAuthor Commented:
Office 2010/2013 are both generating the same error:

c:\ee\convert3.vbs(23, 1) Microsoft VBScript runtime error: Ty
pe mismatch: 'GetBaseName'
0
 
Robberbaron (robr)Commented:
change line 23...

strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"
0
 
Bill PrewCommented:
Thanks 'rob'.

~bp
0
 
zequestionerAuthor Commented:
Weird, in both versions it now opens the excel spreadsheet with the text delimited correctly, but it does not save and close excel. The command window shows this:

Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

C:\ee\convert3.vbs(30, 1) Microsoft VBScript runtime error: Ob
ject required: 'objExcel.Workbooks.OpenText(...)'

i think that means line 30 is a little off.
0
 
Robberbaron (robr)Commented:
try changing
' Open delimited file
Set objWorkbook = objExcel.Workbooks.OpenText(strInPath,,,xlDelimited,,,,,,,True,"|")

Open in new window

to
' Open delimited file
objExcel.Workbooks.OpenText(strInPath,,,xlDelimited,,,,,,,True,"|")
set objWorkbook = objExcel.Activeworkbook

Open in new window



My reading of MSDN is that OpenText does not return a object (where Open does) which is why i used Activeworkbook when doing the saving.
0
 
Bill PrewCommented:
Great minds thinking alike there?

:-)
0
 
zequestionerAuthor Commented:
Thanks guys!!!!!!!!!!! Works perfectly in Excel 2010 as well as 2013!

Thanks again!
0
 
zequestionerAuthor Commented:
also for you to knowI  have another open question here:

http://www.experts-exchange.com/Programming/Misc/Q_28316758.html

I know it is not best practice to ask multiple questions on a single thread.. plus you get extra points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.