We help IT Professionals succeed at work.

# VB Script to open a delimited text file in Microsoft Excel

on
8,467 Views
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!!
Comment
Watch Question

## View Solutions Only

Commented:
I tried to use this but couldn't get it right...

http://social.technet.microsoft.com/Forums/scriptcenter/en-US/92102c07-9892-42e4-91c3-7544044d7204/how-to-use-vbscript-to-save-txt-file-as-excel-file?forum=ITCG
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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,"|"

~bp
CERTIFIED EXPERT

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.

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.

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.
CERTIFIED EXPERT

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


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.

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


But now it gives this error:

1>>False<<
C:\ee\convert.vbs(15, 1) Microsoft Excel: OpenText method of
Workbooks class failed
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

Commented:
same with office 2010, are you x86 or x64?
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
I'm x86.

~bp
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

~bp
CERTIFIED EXPERT

Commented:
bills script should fix the error.

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

should have been
myinputfile = objFSO.GetAbsolutePathName(strInFile)

Commented:
Office 2010/2013 are both generating the same error:

c:\ee\convert3.vbs(23, 1) Microsoft VBScript runtime error: Ty
pe mismatch: 'GetBaseName'
CERTIFIED EXPERT

Commented:
change line 23...

strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Thanks 'rob'.

~bp

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

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.
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Great minds thinking alike there?

:-)

Commented:
Thanks guys!!!!!!!!!!! Works perfectly in Excel 2010 as well as 2013!

Thanks again!

Commented:
also for you to knowI  have another open question here:

https://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!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
###### Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Connect with Certified Experts to gain insight and support on specific technology challenges including:

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.