[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2665
  • Last Modified:

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

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
mike_VBS
Asked:
mike_VBS
  • 9
  • 6
  • 4
  • +1
1 Solution
 
käµfm³d 👽Commented:
Why do you need to use Excel for this? Is it not sufficient to just rename the file with a new extension?
0
 
mike_VBSAuthor Commented:
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
 
Patrick MatthewsCommented:
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
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.

 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
käµfm³d 👽Commented:
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
 
mike_VBSAuthor Commented:
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
 
mike_VBSAuthor Commented:
Yes the data has commas, for example:

www|sssss|corp,inc|dddd| |sssss|ssss
0
 
käµfm³d 👽Commented:
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
 
mike_VBSAuthor Commented:
as is

Is there away to open the pipe delimited file using excel?
0
 
Patrick MatthewsCommented:
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
 
mike_VBSAuthor Commented:
I get an error on line 18:

Invalid procedure call or argument
0
 
Patrick MatthewsCommented:
Please post a sample file.

When I test it on my side, it seems to work just fine.
0
 
mike_VBSAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
Patrick MatthewsCommented:
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
 
RobSampsonCommented:
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
 
mike_VBSAuthor Commented:
That's the ticket!!!

Thank you!
0
 
RobSampsonCommented:
No problem. Thanks for the grade.

Rob.
0
 
mike_VBSAuthor Commented:
Do you know of any books that outline converting VBA to VBS?
0
 
RobSampsonCommented:
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
 
mike_VBSAuthor Commented:
Thanks again - I'll try and follow along
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now