Converting Unfomatted Text into Formatted Text

[Papa_Don]

Hello Group!

I am in need of converting a "text" file created by a UNIX based program (it is only semi-formatted) and put it into a more-formatted text (.txt) file. Put a different way, this needs to be converted such that it can be opened in Notepad and it be formatted correctly (and with no quote marks).

I've used some VBA language to open the file in EXCEL, apply the 1 delimiter at the end of the row and then save it as a EXCEL type of text file (xlText). Unfortunately it is adding beginning and ending quotes to every line. I cannot have these in there as I have other code that is then parsing the individual lines for use in another spreadsheet.

I'm pondering doing this conversion outside of EXCEL using Visual Basic as I have will have many of these to do each day and I think it will be much easier and faster.

Any thoughts or ideas?

Thanks for your assistance.

Don

[Minimalist]

Have look at this link:
http://stackoverflow.com/questions/11501531/saving-a-excel-file-into-txt-format-without-quotes
If it doesn't work I shall post some code to strip the the text.

[Papa_Don]

Minimalist, Keep in mind that this won't be a comma delimited file. I've attached two text files to show you how it comes across and what it needs to look like. Note: the "look like" file has quotes at the beginning and ending of the first group line. These cannot be there.

Again, thanks for your assistance!

Don

[Minimalist]

Sorry, in the 19kb file I cant see any quotes and the 213 file has only adate in it.

[Papa_Don]

Minimalist, my apologies. The second file had been altered and I forgot about that. I looked at the 19KB file and it appears to be formatted correctly.... Which surprises me. When I view it through Notepad, it not formatted. I'm not sure why it looks correct here. I'm going to try to attach a picture of what it looks like in Notepad. You can then look at the 19KB file again and see it as though it was delimited correctly.

[Papa_Don]

I meant to attach the file called 213Restran in the last note. I'm doing it here. I think now you'll see the quotes on various lines.

[Reverend Jim]

Try saving this code into convert.vbs

set fso = CreateObject("Scripting.FileSystemObject")
set arg = Wscript.Arguments

if arg.Count <> 2 Then
    Wscript.Echo "Convert inputfile outputfile"
    Wscript.Echo "Creates outputfile from inputfile by replacing"
    Wscript.Echo "unix line-ending (vblf) to Windows format (vbcrlf)"
    Wscript.Quit
End If

text = fso.OpenTextFile(arg(0)).ReadAll
text = Replace(text,vblf,vbcrlf)
set tso = fso.OpenTextFile(arg(1), 2, True)
tso.Write text
tso.Close

From the command line you can run it by

cscript convert.vbs inputfile.txt outputfile.txt

Or if you do this once

cscript //h:cscript //x:nologo //s

It will set cscript as the default script engine. Then you can run it by

convert inputfile.txt outputfile.txt

[Minimalist]

O.K I can see it now. Try Jim's suggestion and if it doesn't work I shall post some code to strip the quotes.

[Reverend Jim]

If the quotes are being added by Excel on conversion and you use my script to convert instead of Excel then there should be no quotes to be concerned with. However, if you do have quotes then use the following:

set fso = CreateObject("Scripting.FileSystemObject")
set arg = Wscript.Arguments

if arg.Count <> 2 Then
    Wscript.Echo "Convert inputfile outputfile"
    Wscript.Echo "Creates outputfile from iniputfile by replacing"
    Wscript.Echo "unix line-ending (vblf)) to Windows format (vbcrlf)"
    Wscript.Quit
End If

text = fso.OpenTextFile(arg(0)).ReadAll
text = Replace(text,vblf,vbcrlf)
text = Replace(text,vbcr & vbcr, vbcr)
text = Replace(text,"""","")
set tso = fso.OpenTextFile(arg(1), 2, True)
tso.Write text
tso.Close

Note one additional change - I added

text = Replace(text,vbcr & vbcr, vbcr)

Your original input file has lines terminated with CR. The original script, if you fed it a normal Windows text file, would end up with double spaced lines because CRLF would be changed to CRCRLF. The extra line does cleanup by changing CRCR to CR. This ensures it will work on both Unix and Windows delimited files.

[Papa_Don]

Rev. Jim,

As always, thanks again for the help. However I want to make sure you understand that I don't want to use EXCEL at all.

I'm thinking that there has to be a way to read the file as you see it in Notepad_Picture.pdf. I'm assuming that this file is exported by the UNIX program as 1 line of text with all the spaces but without carriage returns, etc. Assuming that's correct (you may know something I don't as I've not seen this kind of output before), I'm thinking it may be best to parse this very long line and chop it up into a fixed number of characters and turn that into a line, then move to the next set of characters and make that a second line, and so forth and so on.

So my code would be something to the effect of

1) import the file and read it
2) parse the first 130 characters and move them to a new text file as line 1
3) Repeat this process until the "End of File"

Hopefully I'm making sense.

You're code above may do exactly what I'm thinking. But being a novice, I'm not sure how or what it's doing. This may be a good teaching moment.

FYI: The reason I want to abandon EXCEL to help with the conversion is that I've got multiple files to convert (right now, upwards of 130). So I thought I'd write a program in Visual Basic to allow the user to select a group of 10 or 15 files (or their choice) to convert and let the program do each of them 1 at a time (I'll set up a loop to do each one).

Thank you again. I appreciate everything you've done!

Don

[Reverend Jim]

My program doesn't require Excel. Let's clear one thing up first. Is your input file a text file or is it a PDF file? As for the script, I thouught it was simple enough to not require comments.

'vbscript uses helper objects. fso is a FileSystem object that has methods to
'perform file type functions. Wscript.Arguments allows access to command line
'arguments. This script requires two parameters, an input file and an output 
'file. If you don't enter two parameters you get some brief help and the
'script exits.

set fso = CreateObject("Scripting.FileSystemObject")
set arg = Wscript.Arguments

if arg.Count <> 2 Then
    Wscript.Echo "Convert inputfile outputfile"
    Wscript.Echo "Creates outputfile from iniputfile by replacing"
    Wscript.Echo "unix line-ending (vblf)) to Windows format (vbcrlf)"
    Wscript.Quit
End If

'ReadAll reads the entire file into a string variable. The first Replace
'converts a linefeed character into a linefeed and carriage return. If the
'input file was already in Windows format (CRLF) then the first Replace
'would have changed it to CRCRLF in which case the second Replace fixes
'that by changing CRCRLF to CRLF. The third Replace removes all double
'quotes.

text = fso.OpenTextFile(arg(0)).ReadAll
text = Replace(text,vblf,vbcrlf)
text = Replace(text,vbcr & vbcr, vbcr)
text = Replace(text,"""","")

'OpenTextFile returns a textstream object which is used to
'output the new data. The second parameter (2) indicates the file
'is opened for writing. The third parameter (True) says to create
'the file if it doesn't already exist.

set tso = fso.OpenTextFile(arg(1), 2, True)
tso.Write text
tso.Close

[Papa_Don]

Rev. Jim,

Thanks for the above. To answer you first question, the file is indeed a text file (this is a presumption as the file is created by a UNIX based program. It does open in Notepad but it appears to have no carriage returns. To clarify, the .pdf file I attached was actually a picture of the actual file opened in Notepad.

I'm attempting to enter the code you supplied. However I'm getting some error messages. The first is "fso". Should this be defined as a "Object" or a "File" (I suppose it could be something else. If so, what)? It wouldn't allow me to write "set" as the first word in that line.

Also, what is "arg"? Is it a string? How should it be defined? Further, what about "Wscript"? It seems to want to be defined as well. Again, is this a string?

I appologize for having to ask these questions. All of this is new. This wasn't taught in the classes on Visual Basic I've taken.

As always, thank you for help and knowledge base.

Don

[Reverend Jim]

Just to clarify, this isn't a vb or vb.net program. It is a stand alone vbscript (like a super batch file). Just copy and paste the code into notepad and save it as convert.vbs. You run it from the command line (what you might call a DOS shell).

[Papa_Don]

Rev. Jim, I don't see anything in your code that will direct it to where the original file will be found or where the new, "Save As" file will go. I've got to believe there are some lines of code that need to be inserted to tell it what to do.

For what it's worth, I've got about 150 of these kinds of files to convert. Thus the reason I wanted to write a script to do it. To this point, we've been screen scraping the data, then pasting that into a Notepad file. I finally convinced someone that the UNIX system should be able to generate the report for us. Now it's doing that, but I've got to "format" it to make it legible and useable.

I'm sure I could add to this to create a list of the individual files to run and let it loop through it over and over until it's completed.

As always, thanks for your help.

Don

[Minimalist]

O.K. here is a vb script file that will do what you want. Again, you need to copy the code into Notepad and save the Notepad file as convert.vbs . You also need to set the Encoding to ANSI in the Notapad "Save As" Window. The vbs file needs to be run within the directory where your files to be converted are located. You run the vbs file by double clicking on it. It will convert all text files.
I have used some of Jim's code. The new file names will start with a C at the beginning of the old file name.

set fso = CreateObject("Scripting.FileSystemObject")
dim CurrentDirectory, Fil,Fso,text,stringname,str1, fcount
dim stringfolder
    CurrentDirectory = fso.GetAbsolutePathName(".")
    stringfolder = currentDirectory
    set FLD = FSO.GetFolder(stringfolder)
    fcount=0
        For Each Fil In FLD.Files
        fcount=fcount+1
         if strcomp(right(Fil.Name,4),".txt",1)=0 then
         Filename = stringfolder & "\" & Fil.Name
              str1=Right(Fil.Name,4)
text = fso.OpenTextFile(Filename).ReadAll
    text = Replace(text,vblf,vbcrlf)
    text = Replace(text,vbcr & vbcr, vbcr)
    text = Replace(text,"""","")
newfilename = "C" & fil.name &  str1 
 set tso = fso.OpenTextFile(newfilename, 2, True)
 tso.write text
tso.Close
        end if
Next

In line 17 the new file name is set.

[Papa_Don]

Minimalist, I've not run a VBscript in this manner before (in fact, only recently did I learn there was such a thing as VBscript). Does this mean that a copy of this program will need to be in every folder where the text file exists? In other words, I have 150 different files to convert. Each file will be in a separate folder. Will I need to place a copy of this program in each folder?

I certainly don't mind doing this. I only want to clarify.

I can't thank all of you enough for your help on this. I have researched and tried to do this on my own. I simply don't know enough to do this. But I'm learning with each step!

Don

[Papa_Don]

Minimalist, be sure to see the questions above.

I've copied your script into Notepad and saved it as "convert.vbs". I double clicked it to run. Nothing happened. I tried inserting "Sub Main" and "End Sub" at the top and bottom. Again, nothing happened. Do I have something wrong? I've not added any paths to where a file to convert may be. I'm assuming that this is meant to convert any text file within the folder.

I look forward to hearing from you. I've got to believe this is a step in the right direction!

Don

[Minimalist]

Nooo! Don't add any code to it. It is a script file. Does the icon of Notepad looks like the one I added. Add a new line under line 17 and insert msgbox newfilename
so we can see if it runs through

[Papa_Don]

Minimalist, Yes, the icon in the folder looks identical. I did save it in the actual folder where it is to work as "convert.vbs". "ANSI" was selected.

I've removed "Sub Main" and "End Sub" that I wrote in. I added the message box code to line 18. I've attempted to run it again with no results. Even the message box did not pop up. I even tried to "Open with command propmt" with no success.

Thank you again!

[Minimalist]

You have 150 folders with one file in it? Why? And yes, the script is meant to run within the folder the files you like to vonvert are located. Now, just to check the script is running insert a newline under line 5 and we try to bring up a msgbox with the folder name. So type into the newline
msgbox stringfolder
Now if the msgbox pops up and nothing else happens you need to show what the complete filename is we are trying to process.

[Reverend Jim]

Does this mean that a copy of this program will need to be in every folder where the text file exists?

No. Just put convert.vbs in a folder that is in your %PATH%. If you don't know how to do that then after opening a command shell just do

set %PATH%=%PATH%;myfolder

where myfolder is the name of the folder containing convert.vbs. If you don't want to do that then just run it by

D:\myfolder\convert infile outfile

I don't see anything in your code that will direct it to where the original file will be found or where the new, "Save As" file will go. I've got to believe there are some lines of code that need to be inserted to tell it what to do.

The script is complete and does not require any Sub Main or other code. As I stated, the script is run from the command prompt, not by double clicking an icon. The format is

convert inputfile outputfile

The input and output files are specified on the command line.

If the files are in the current folder then just specify the file name as in

convert infile.txt outfile.text

You can qualify the file name(s) with a path if necessary. If you want to convert a lot of files then you can create a Windows batch (.cmd) file containing

convert ifile1.txt ofile1.txt
convert ifile2.txt ofile2.txt
convert ifile3.txt ofile3.txt
etc.

There's no need to complicate this.

[Reverend Jim]

And, for the record, when running a script from the command line, the output command is not msgbox. You should use wscript.echo as in

wscript.echo "Hello, world"

That will write the output to the console window. Msgbox, on the other hand, pops up a message box and waits for you to close it. If you have a lot of output you will get very tired of closing message boxes.

[Papa_Don]

Minimalist, I've inserted "msgbox stringfolder" as line 6 in the script.

Yes, I really have 150 separate folders where this program will be inserted. I know that sounds odd. However I'm in the Hotel industry. We have 150 different properties in our group. As part of each hotels night audit, they generate this text file that is stored in the hotels specific folder. It's all on one server, but in their individual folder. This part of the reason I would have preferred to have written the code in VB.net as I had started a program that do the conversion after pushing a button on the users desktop.

I've attempted to run the script. The message box comes up and it says, "O:\IPSDATA\PMS00213" which is the correct path to the file in that folder. The file to be converted is specifically called "20141210.142601". However it doesn't appear to have done anything.

Any thoughts?

[Minimalist]

O.K try to run this script in a file's directory:

set fso = CreateObject("Scripting.FileSystemObject")
dim CurrentDirectory, Fil,Fso,text,stringname,str1, fcount
dim stringfolder
    CurrentDirectory = fso.GetAbsolutePathName(".")
    stringfolder = currentDirectory
    set FLD = FSO.GetFolder(stringfolder)
    fcount=0
        For Each Fil In FLD.Files
if Fil.Name <> "Convert.vbs"  then
        fcount=fcount+1
         Filename = stringfolder & "\" & Fil.Name
            text = fso.OpenTextFile(Filename).ReadAll
text = Replace(text,vblf,vbcrlf)
text = Replace(text,vbcr & vbcr, vbcr)
text = Replace(text,"""","")
newfilename = "C" & fil.name 
 set tso = fso.OpenTextFile(newfilename, 2, True)
 tso.write text
end if
tso.Close
   Next
msgbox "Number of Files concerted  " & fcount

Name the script exactly as Convert.vbs and run it once. It should create a copy of the file with a C in front.

[cgeier]

I think that you are going about this the wrong way.

Why are you set in using Notepad to view your file? Have you considered using Wordpad (or Word) to view it? They will display the file correctly if the lines are terminated with CR (or VbCr).

Note: You can change your file association so that .txt files open with Wordpad (or Word).

Why don't you just have the IT person generate the reports in the format that you need them? Surely, it is cheaper to pay the IT person 1 hr (if that) to create the proper reports than to pay you 10 hrs to write your scripts--unless you're doing this on your personal time as an educational experience.

If you still believe that you need to create a script/program to help you modify your reports, I think that you need to start from the beginning. State/show the report that you have and what you would like the end result to be.

There are some questions that need to be answered in order for anyone to be able to provide useful answers.

-What is the folder structure (ie: what are the folder names)? Provide 2-3 fully qualified folder names so one can get an idea of the folder structure.

-What are the file names? (You already provided an answer for this)

-Where do you want to store the converted files? In the original folders?

-What do you want the converted files to look like?

-What do you want the converted filenames to be?

-What does the data look like now and what do you want the data to look like?

[Papa_Don]

Minimalist, the code works perfectly! All is good! Excellent! Thank you, thank you, thank you!

Happy Holidays!

Don

[Reverend Jim]

We have 150 different properties in our group. As part of each hotels night audit, they generate this text file that is stored in the hotels specific folder. It's all on one server, but in their individual folder. This part of the reason I would have preferred to have written the code in VB.net as I had started a program that do the conversion after pushing a button on the users desktop.

It might have been a good idea to have stated that at the very start. Your original request was that you wanted to convert "a" text file. A great many projects have been derailed because the user requirements were not properly specified up front.

[Papa_Don]

Rev. Jim, you're correct and that is my error.

Since this program is written in VBscript, I've looked for a way to run this "Convert.vbs" through a VB.net. I've tried

Dim fileStart As String = "C:\Restran Conversion\IPSDATA\PM00213A\Convert.vbs"
' 1st try
Process.Start("C:\Windows\System32\cscript.exe", fileStart)
' 2nd try
System.Diagnostics.Process.Start("C:\Windows\System32\wscript.exe", fileStart)

neither one of these will work as I get a an error message with the first one and nothing happens with the second. I've tried using wscript.exe in place of cscript.exe.

Again, Convert.vbs works perfectly when I double click it. Isn't there some way to emulate that via VB.net?

Don

[Reverend Jim]

You can effectively run the same code in vb.net with minor modifications. Normally I would try the code before posting but my regular laptop is toast and I m using n old clunker (without vb.net instlled nd with a flkey "A" key). Essentially, wht you would do is read the text in by

My.Computer.FileSystem.ReadAllText(filename)

then use the Replace function as previously noted. I'd be more specific but I hve no way to write/test the code at the moment.

[Papa_Don]

Rev. Jim, I started to do something like that, but I didn't have much luck with "StreamReader". I'll give your code a whirl.

Thanks again!