Solved

Using VBScript how do you open a pipe delimited txt file and save as a csv

Posted on 2013-01-21
22
2,113 Views
Last Modified: 2013-02-06
Set oExcel = CreateObject("Excel.Application")
oExcel.visible = TRUE
oExcel.Workbooks.Open("C:\Pipe_Delinited.txt")
oExcel.Workbooks(1).SaveAs "C:\Comma_Delinited.csv", 6

When I open the file this way the entire line is in one cell
0
Comment
Question by:mike_VBS
  • 9
  • 6
  • 4
  • +1
22 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 38802810
Why do you need to use Excel for this? Is it not sufficient to just rename the file with a new extension?
0
 

Author Comment

by:mike_VBS
ID: 38802970
I need to convert the file from pipe delimited to comma delimited (I need a comma delimited file for use with a different program)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803009
Assuming that the file itself is not too big:

Dim fso, ts1, ts2

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts1 = fso.OpenTextFile("c:\folder\old file.txt")
Set ts2 = fso.CreateTextFile("c:\folder\new file.txt") 'or use .csv if you wish

ts2.Write Replace(ts1.ReadAll, "|", ",")

ts2.Close
ts1.Close

Set ts1 = Nothing
Set ts2 = Nothing
Set fso = Nothing

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803011
Or to do it line by line...

Dim fso, ts1, ts2

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts1 = fso.OpenTextFile("c:\folder\old file.txt")
Set ts2 = fso.CreateTextFile("c:\folder\new file.txt") 'or use .csv if you wish

Do Until ts1.AtEndOfStream
    ts2.WriteLine Replace(ts1.ReadLine, "|", ",")
Loop

ts2.Close
ts1.Close

Set ts1 = Nothing
Set ts2 = Nothing
Set fso = Nothing

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803033
Note that I am making the following assumptions:

1) The only pipes you have are delimiters--that is, there are no values that include a pipe character

2) Your original file has no values that have commas in them

If either of these assumptions are incorrect, your answer will get much more complicated :)
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 38803040
matthewspatrick's approach works under the assumption that you have no text-qualified fields--or at least that such fields don't have any embeded pipes (not common, I would think). If any of your fields have embedded pipes, then you will need to tweak the solution.
0
 

Author Comment

by:mike_VBS
ID: 38803131
This works except when it comes to | | - and it stops reading the line
The data has empty pipes for example:

xxxx|ssss|ssss| |sssss|nnnnn

Excel is able to read the empty pipes
0
 

Author Comment

by:mike_VBS
ID: 38803173
Yes the data has commas, for example:

www|sssss|corp,inc|dddd| |sssss|ssss
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 38803177
Well the above code simply replaces any occurrences of a pipe with a comma. It seems you've got something else going on with your code. Did you use the code as-is, or did you modify it?
0
 

Author Comment

by:mike_VBS
ID: 38803232
as is

Is there away to open the pipe delimited file using excel?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803283
Assuming the only pipes are delimiters, then...



Dim fso, ts1, ts2, TheLine, arr, Counter

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts1 = fso.OpenTextFile("c:\folder\old file.txt")
Set ts2 = fso.CreateTextFile("c:\folder\new file.txt") 'or use .csv if you wish

Do Until ts1.AtEndOfStream
    TheLine = ts1.ReadLine
    If TheLine <> "" Then
        arr = Split(TheLine, "|")
        For Counter = 0 To UBound(arr)
            If InStr(1, arr(Counter), ",") > 0 Then
                arr(Counter) = """" & arr(Counter) & """"
            End If
        Next
        TheLine = Join(arr, ",")
    End If
    ts2.WriteLine TheLine
Loop

ts2.Close
ts1.Close

Set ts1 = Nothing
Set ts2 = Nothing
Set fso = Nothing

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:mike_VBS
ID: 38803400
I get an error on line 18:

Invalid procedure call or argument
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803460
Please post a sample file.

When I test it on my side, it seems to work just fine.
0
 

Author Comment

by:mike_VBS
ID: 38803603
When I open the file in Notepad and do a find "| |" & replace "||" then the code runs as expected.

I think my problem all along has been the empty |'space'| pipes.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 38803713
You are missing the point of the code. The fact that there is a space between the pipes is irrelevant. The Replace and Split functions only care about the strings you tell them to search for. In this case, that string is a single pipe character. What that means is that  either will search for every instance of a pipe character. Whereas Replace will exchange a particular instance of the string being sought with the designated replacement, Split will turn the string into an array, where everything--including nothing--between two pipes is put into a slot of the array. So in the case of Replace, a starting string of:

hello| |world

...becomes:

hello, ,world

and in the case of Split:

hello| |world

becomes the array:

[hello][ ][world] (three items)

There is something wrong with your logic or your data if you are not getting the proper result.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38803781
Concur with kaufmed: | <space> | is not going to foul things up.

If you are getting errors with my code, then you need to post a sample file that results in an error when you run it.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 38804146
Hi, this VBS code would open a pipe delimited file directly in Excel.  You will have to add your "Save" code to it.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Const xlDelimited = 1
Const xlDoubleQuote = 1
strFileName = "C:\Temp\Scripts\LongFile.txt"
objExcel.Workbooks.OpenText strFileName, ,1 ,xlDelimited ,xlDoubleQuote, False, False, False, _
False, False, True, "|", Array(1, 1)

Open in new window


Regards,

Rob.
0
 

Author Closing Comment

by:mike_VBS
ID: 38857731
That's the ticket!!!

Thank you!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 38857795
No problem. Thanks for the grade.

Rob.
0
 

Author Comment

by:mike_VBS
ID: 38857826
Do you know of any books that outline converting VBA to VBS?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 38857971
No, I don't know of any books, but there's a couple of rules you can follow

1) Type declarations are not supported, so remove them, eg As String

2) VBA Constants are not implicit when using VBS, so you must declare them in your code.  For example, in VBA, execute MsgBox xlDelimited and you will see the value 1, so in VBS, add
Const xlDelimited = 1

3) The parameter qualifiers for functions are not supported, so remove them, eg change Workbooks.Open(Filename:="C:\MyFile.doc") to Workbooks.Open("C:\MyFile.doc")
Also with function arguments, because of this, you must follow the exact parameter order as documented for that function, and for parameters you don't want to set, just leave it blank.
For example, here is the documentation for the Open method: http://msdn.microsoft.com/en-us/library/office/bb179167(v=office.12).aspx
which shows the parameter list
expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

so, the VBS equivalent to open a file readonly (with all other parameters default) is:
objExcel.workbooks.open("C:\MyDoc.doc", , True)

where the UpdateLinks parameter is left blank to accept the default.

4) Make sure that you preceed all object statements with objExcel (or whatever your object name is for the COM object you're controlling).  For example, just using
Workbooks.Open(....
as is shown in VBA, won't work, so you have to use
objExcel.Workbooks.Open(....

Similarly, for "Application" style settings in VBA, like
Application.DisplayAlerts = False

in VBS, you use
objExcel.DisplayAlerts = False

5)  With the code of a recorded macro, there's no need to keep .Activate or .Select or .Selection.<property> statements.  These make for less efficient code, and be replaced by direct references to the range you're working with, such as
objExcel.Sheets(1).Range("A1:A10").Font.Bold = True

There's probably a couple more quirks, but they're certainly the main stumping points I can think of.

Other than that, just record your macro, and modify it as per the tips above, and it should just about work ;-)

Regards,

Rob.
0
 

Author Comment

by:mike_VBS
ID: 38862195
Thanks again - I'll try and follow along
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now