Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB Script to open a delimited text file in Microsoft Excel

Posted on 2013-12-09
21
Medium Priority
?
5,845 Views
Last Modified: 2013-12-12
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!!
0
Comment
Question by:zequestioner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 5
21 Comments
 
LVL 58

Expert Comment

by:Bill Prew
ID: 39707513
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39707564
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:zequestioner
ID: 39707740
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
 
LVL 1

Author Comment

by:zequestioner
ID: 39707866
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39707893
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
 
LVL 1

Author Comment

by:zequestioner
ID: 39709164
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
 
LVL 58

Expert Comment

by:Bill Prew
ID: 39709191
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
 
LVL 1

Author Comment

by:zequestioner
ID: 39710366
same with office 2010, are you x86 or x64?
0
 
LVL 58

Expert Comment

by:Bill Prew
ID: 39710377
I'm x86.

~bp
0
 
LVL 58

Expert Comment

by:Bill Prew
ID: 39710423
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
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39710559
bills script should fix the error.

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

should have been
myinputfile = objFSO.GetAbsolutePathName(strInFile)
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39710643
Office 2010/2013 are both generating the same error:

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

Expert Comment

by:Robberbaron (robr)
ID: 39710690
change line 23...

strOutPath = objFSO.GetParentFolderName(strInPath) & "\" & objFSO.GetBaseName(strInPath) & ".xlsx"
0
 
LVL 58

Expert Comment

by:Bill Prew
ID: 39712516
Thanks 'rob'.

~bp
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39712540
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
 
LVL 58

Accepted Solution

by:
Bill Prew earned 1400 total points
ID: 39713134
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
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 600 total points
ID: 39713139
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
 
LVL 58

Expert Comment

by:Bill Prew
ID: 39713167
Great minds thinking alike there?

:-)
0
 
LVL 1

Author Closing Comment

by:zequestioner
ID: 39714542
Thanks guys!!!!!!!!!!! Works perfectly in Excel 2010 as well as 2013!

Thanks again!
0
 
LVL 1

Author Comment

by:zequestioner
ID: 39714579
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question