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, 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

Attachments
Four Points YVR                                    Reservation Transaction Report                              Page Number:    1
Starwood Technology Ctr                                    For 10-DEC-2014                                 10-DEC-2014 02:26 PM
(res.restran)                                    Ordered By Arrive Date, Guest Name

Arrival / ------------Guest Information------------  ------Room Information------ Persons   -----Credit Information-----
Departure Status Type Guest Name                     Rm Tp Rate Sched -Room Rate- AD YO CH  Type Credit Card Number       Exp Date

10-DEC-14 MOD    C    Bryant, Douglas                KGN   SETSP           134.10  1  0  0  MC   xxxxx8540                xx-xx
11-DEC-14 Group:        CFTID: 348415   Source: AA   Market Seg: TVL  IATA #1: 60734822     AR Account:          Agent #: ALEXC

10-DEC-14 MOD    T    Burrell, Lori                  KCN   BAR             129.00  1  0  0  AX   xxxxx1000                xx-xx
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCR  IATA #1:              AR Account:          Agent #: KITTYH

10-DEC-14 MOD    T    Coombs, Michael                KCN   DAILYDI         127.20  1  0  0  MC   xxxxx0910                xx-xx
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TSR  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    T    Coughlin, Brian                DDN   DAILY10         107.10  1  0  0  VI   xxxxx2882                xx-xx
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TSR  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    T    Denys, Valerie                 DDN   AUTOV           121.50  1  0  0  MC   xxxxx0416                xx-xx
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TQD  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 NEW    T    Eggar, Jack                    DDN   EXPRM           104.13  1  0  0  CA                            xx-xx
11-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: CRS

10-DEC-14 MOD    W    Feng, Ying-Chen                DDN   EVAAIR          100.87  1  0  0  CA                            xx-xx
13-DEC-14 Group:        CFTID:          Source: FAX  Market Seg: OTR  IATA #1:              AR Account: 7945     Agent #: ALEXC

10-DEC-14 MOD    T    Gray, Kate                     KGN   AUTO1P          126.10  2  0  0  AX   xxxxx1017                xx-xx
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TLP  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    W    Hung, Chia-Hsiang              DDN   EVAAIR          100.87  1  0  0  CA                            xx-xx
13-DEC-14 Group:        CFTID:          Source: FAX  Market Seg: OTR  IATA #1:              AR Account: 7945     Agent #: ALEXC

10-DEC-14 MOD    T    Keeney, Alex                   KCN   BAR             149.00  1  0  0  MC   xxxxx0535                xx-xx
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCR  IATA #1:              AR Account:          Agent #: KITTYH

10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             134.00  1  0  0  MC   xxxxx4585                xx-xx
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             134.00  1  0  0  MC   xxxxx4585                xx-xx
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             149.00  2  0  0  MC   xxxxx4585                xx-xx
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             149.00  2  0  0  MC   xxxxx4585                xx-xx
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

10-DEC-14 MOD    C    Rorison, James                 KCN   SETSP           134.10  1  0  0  AX   xxxxx1004                xx-xx
11-DEC-14 Group:        CFTID: 7585     Source: AA   Market Seg: TVL  IATA #1: 3519950      AR Account:          Agent #: ALEXC

10-DEC-14 MOD    W    Shih, Pin-Wang                 DDN   EVAAIR          100.87  1  0  0  CA                            xx-xx
13-DEC-14 Group:        CFTID:          Source: FAX  Market Seg: OTR  IATA #1:              AR Account: 7945     Agent #: ALEXC

10-DEC-14 MOD    T    Wu, Bin                        KGN   EXPRM           121.63  2  0  0  CA                            xx-xx
11-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: KITTYH

10-DEC-14 MOD    T    Yen, Hao                       DDN   SPG3             43.48  3  0  0  AX   xxxxx1008                xx-xx
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: SPG  IATA #1:              AR Account: 8254     Agent #: ALEXC
Four Points YVR                                    Reservation Transaction Report                              Page Number:    2
Starwood Technology Ctr                                    For 10-DEC-2014                                 10-DEC-2014 02:26 PM
(res.restran)                                    Ordered By Arrive Date, Guest Name

Arrival / ------------Guest Information------------  ------Room Information------ Persons   -----Credit Information-----
Departure Status Type Guest Name                     Rm Tp Rate Sched -Room Rate- AD YO CH  Type Credit Card Number       Exp Date

11-DEC-14 NEW    T    Apruzzese, Ennio               KGN   BAR             149.00  1  0  0  VI   xxxxx7911                xx-xx
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCR  IATA #1:              AR Account:          Agent #: STARUSER

11-DEC-14 NEW    T    Barnard, Rod                   DDN   BAR             129.00  1  0  0  MC   xxxxx6243                xx-xx
12-DEC-14 Group:        CFTID:          Source: PH   Market Seg: TCR  IATA #1:              AR Account:          Agent #: IRIS

11-DEC-14 MOD    T    Dysart, Bruce                  DDN   HTWIRE15         49.00  2  0  0  CA                            xx-xx
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TOP  IATA #1: 10564912     AR Account:          Agent #: STARUSER

11-DEC-14 MOD    T    Fieldhouse, Ron                KCN   SETAUTOF        116.10  2  0  0  AX   xxxxx1009                xx-xx
12-DEC-14 Group:        CFTID: 298940   Source: NTF  Market Seg: TQD  IATA #1:              AR Account:          Agent #: KITTYH

11-DEC-14 CXL    T    Fieldhouse, Ron                KGN   SPG3             43.48  2  0  0  AX   xxxxx1009                xx-xx
12-DEC-14 Group:        CFTID: 298940   Source: NTF  Market Seg: SPG  IATA #1:              AR Account:          Agent #: CRS

11-DEC-14 NEW    C    Haidar, Ahmad                  DDN   SETGPC          129.00  1  0  0  MC   xxxxx5653                xx-xx
12-DEC-14 Group:        CFTID: 254247   Source: AA   Market Seg: TVL  IATA #1: 67818424     AR Account:          Agent #: STARUSER

11-DEC-14 NEW    T    Peterson, Mike                 DDN   BAR             129.00  1  0  0  MC   xxxxx6243                xx-xx
12-DEC-14 Group:        CFTID:          Source: PH   Market Seg: TCR  IATA #1:              AR Account:          Agent #: IRIS

11-DEC-14 NEW    T    Rottmair, Jens                 KHN   EXPDI            98.53  1  0  0  CA                            xx-xx
12-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: STARUSER

11-DEC-14 NEW    T    Ryzowski, Keith                KHN   GOC             119.00  1  0  0  VI   xxxxx0963                xx-xx
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCG  IATA #1:              AR Account:          Agent #: STARUSER

11-DEC-14 NEW    T    Schilling, Brian               DDN   DAILY           119.00  1  0  0  AX   xxxxx1002                xx-xx
12-DEC-14 Group:        CFTID:          Source: 1P   Market Seg: TSR  IATA #1: 34543390     AR Account:          Agent #: CRS

11-DEC-14 NEW    T    Spindler, Stefan               KGN   EXPDI            98.53  1  0  0  CA                            xx-xx
12-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: STARUSER

12-DEC-14 NEW    G    Ang, Jeanette                  DDN   GCB109R          54.50  1  0  0  CA                            xx-xx
13-DEC-14 Group: TSSL12 CFTID:          Source: SAL  Market Seg: GCB  IATA #1:              AR Account:          Agent #: 9999

12-DEC-14 NEW    G    Ang, Jeanette                  DDN   GCB109R          54.50  1  0  0  CA                            xx-xx
13-DEC-14 Group: TSSL12 CFTID:          Source: SAL  Market Seg: GCB  IATA #1:              AR Account:          Agent #: 9999

12-DEC-14 NEW    T    Callender, Paula               DDN   AERORED3         95.66  1  0  0  CA                            xx-xx
13-DEC-14 Group:        CFTID: 359421   Source: WEB  Market Seg: TQD  IATA #1:              AR Account: 8414     Agent #: CRS

12-DEC-14 NEW    T    Fung, Patrick P                DDN   EXPRM15          82.94  1  0  0  CA                            xx-xx
15-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: CRS

12-DEC-14 NEW    T    Lau, Kerry                     DDN   EXPRM15          82.94  1  0  0  CA                            xx-xx
15-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: CRS

13-DEC-14 MOD    T    Chou, Muhsiang                 DDN   SPGDI             0.00  1  0  0  AX   xxxxx1003                xx-xx
14-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TSR  IATA #1:              AR Account:          Agent #: IRIS

13-DEC-14 MOD    T    Yang, David
10/8/2014

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.

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.

Attachments
Four Points YVR                                    Reservation Transaction Report                              Page Number:    1
Starwood Technology Ctr                                    For 10-DEC-2014                                 10-DEC-2014 02:26 PM
"(res.restran)                                    Ordered By Arrive Date, Guest Name"

Arrival / ------------Guest Information------------  ------Room Information------ Persons   -----Credit Information-----
Departure Status Type Guest Name                     Rm Tp Rate Sched -Room Rate- AD YO CH  Type Credit Card Number       Exp Date

"10-DEC-14 MOD    C    Bryant, Douglas                KGN   SETSP           134.10  1  0  0  MC   xxxxx8540                xx-xx"
11-DEC-14 Group:        CFTID: 348415   Source: AA   Market Seg: TVL  IATA #1: 60734822     AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    T    Burrell, Lori                  KCN   BAR             129.00  1  0  0  AX   xxxxx1000                xx-xx"
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCR  IATA #1:              AR Account:          Agent #: KITTYH

"10-DEC-14 MOD    T    Coombs, Michael                KCN   DAILYDI         127.20  1  0  0  MC   xxxxx0910                xx-xx"
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TSR  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    T    Coughlin, Brian                DDN   DAILY10         107.10  1  0  0  VI   xxxxx2882                xx-xx"
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TSR  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    T    Denys, Valerie                 DDN   AUTOV           121.50  1  0  0  MC   xxxxx0416                xx-xx"
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TQD  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 NEW    T    Eggar, Jack                    DDN   EXPRM           104.13  1  0  0  CA                            xx-xx"
11-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: CRS

"10-DEC-14 MOD    W    Feng, Ying-Chen                DDN   EVAAIR          100.87  1  0  0  CA                            xx-xx"
13-DEC-14 Group:        CFTID:          Source: FAX  Market Seg: OTR  IATA #1:              AR Account: 7945     Agent #: ALEXC

"10-DEC-14 MOD    T    Gray, Kate                     KGN   AUTO1P          126.10  2  0  0  AX   xxxxx1017                xx-xx"
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TLP  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    W    Hung, Chia-Hsiang              DDN   EVAAIR          100.87  1  0  0  CA                            xx-xx"
13-DEC-14 Group:        CFTID:          Source: FAX  Market Seg: OTR  IATA #1:              AR Account: 7945     Agent #: ALEXC

"10-DEC-14 MOD    T    Keeney, Alex                   KCN   BAR             149.00  1  0  0  MC   xxxxx0535                xx-xx"
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCR  IATA #1:              AR Account:          Agent #: KITTYH

"10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             134.00  1  0  0  MC   xxxxx4585                xx-xx"
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             134.00  1  0  0  MC   xxxxx4585                xx-xx"
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             149.00  2  0  0  MC   xxxxx4585                xx-xx"
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    T    Liu, Henry                     DDN   GOB             149.00  2  0  0  MC   xxxxx4585                xx-xx"
11-DEC-14 Group:        CFTID:          Source: RPT  Market Seg: TCG  IATA #1:              AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    C    Rorison, James                 KCN   SETSP           134.10  1  0  0  AX   xxxxx1004                xx-xx"
11-DEC-14 Group:        CFTID: 7585     Source: AA   Market Seg: TVL  IATA #1: 3519950      AR Account:          Agent #: ALEXC

"10-DEC-14 MOD    W    Shih, Pin-Wang                 DDN   EVAAIR          100.87  1  0  0  CA                            xx-xx"
13-DEC-14 Group:        CFTID:          Source: FAX  Market Seg: OTR  IATA #1:              AR Account: 7945     Agent #: ALEXC

"10-DEC-14 MOD    T    Wu, Bin                        KGN   EXPRM           121.63  2  0  0  CA                            xx-xx"
11-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: KITTYH

"10-DEC-14 MOD    T    Yen, Hao                       DDN   SPG3             43.48  3  0  0  AX   xxxxx1008                xx-xx"
11-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: SPG  IATA #1:              AR Account: 8254     Agent #: ALEXC
Four Points YVR                                    Reservation Transaction Report                              Page Number:    2
Starwood Technology Ctr                                    For 10-DEC-2014                                 10-DEC-2014 02:26 PM
"(res.restran)                                    Ordered By Arrive Date, Guest Name"

Arrival / ------------Guest Information------------  ------Room Information------ Persons   -----Credit Information-----
Departure Status Type Guest Name                     Rm Tp Rate Sched -Room Rate- AD YO CH  Type Credit Card Number       Exp Date

"11-DEC-14 NEW    T    Apruzzese, Ennio               KGN   BAR             149.00  1  0  0  VI   xxxxx7911                xx-xx"
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCR  IATA #1:              AR Account:          Agent #: STARUSER

"11-DEC-14 NEW    T    Barnard, Rod                   DDN   BAR             129.00  1  0  0  MC   xxxxx6243                xx-xx"
12-DEC-14 Group:        CFTID:          Source: PH   Market Seg: TCR  IATA #1:              AR Account:          Agent #: IRIS

"11-DEC-14 MOD    T    Dysart, Bruce                  DDN   HTWIRE15         49.00  2  0  0  CA                            xx-xx"
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TOP  IATA #1: 10564912     AR Account:          Agent #: STARUSER

"11-DEC-14 MOD    T    Fieldhouse, Ron                KCN   SETAUTOF        116.10  2  0  0  AX   xxxxx1009                xx-xx"
12-DEC-14 Group:        CFTID: 298940   Source: NTF  Market Seg: TQD  IATA #1:              AR Account:          Agent #: KITTYH

"11-DEC-14 CXL    T    Fieldhouse, Ron                KGN   SPG3             43.48  2  0  0  AX   xxxxx1009                xx-xx"
12-DEC-14 Group:        CFTID: 298940   Source: NTF  Market Seg: SPG  IATA #1:              AR Account:          Agent #: CRS

"11-DEC-14 NEW    C    Haidar, Ahmad                  DDN   SETGPC          129.00  1  0  0  MC   xxxxx5653                xx-xx"
12-DEC-14 Group:        CFTID: 254247   Source: AA   Market Seg: TVL  IATA #1: 67818424     AR Account:          Agent #: STARUSER

"11-DEC-14 NEW    T    Peterson, Mike                 DDN   BAR             129.00  1  0  0  MC   xxxxx6243                xx-xx"
12-DEC-14 Group:        CFTID:          Source: PH   Market Seg: TCR  IATA #1:              AR Account:          Agent #: IRIS

"11-DEC-14 NEW    T    Rottmair, Jens                 KHN   EXPDI            98.53  1  0  0  CA                            xx-xx"
12-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: STARUSER

"11-DEC-14 NEW    T    Ryzowski, Keith                KHN   GOC             119.00  1  0  0  VI   xxxxx0963                xx-xx"
12-DEC-14 Group:        CFTID:          Source: WEB  Market Seg: TCG  IATA #1:              AR Account:          Agent #: STARUSER

"11-DEC-14 NEW    T    Schilling, Brian               DDN   DAILY           119.00  1  0  0  AX   xxxxx1002                xx-xx"
12-DEC-14 Group:        CFTID:          Source: 1P   Market Seg: TSR  IATA #1: 34543390     AR Account:          Agent #: CRS

"11-DEC-14 NEW    T    Spindler, Stefan               KGN   EXPDI            98.53  1  0  0  CA                            xx-xx"
12-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: STARUSER

"12-DEC-14 NEW    G    Ang, Jeanette                  DDN   GCB109R          54.50  1  0  0  CA                            xx-xx"
13-DEC-14 Group: TSSL12 CFTID:          Source: SAL  Market Seg: GCB  IATA #1:              AR Account:          Agent #: 9999

"12-DEC-14 NEW    G    Ang, Jeanette                  DDN   GCB109R          54.50  1  0  0  CA                            xx-xx"
13-DEC-14 Group: TSSL12 CFTID:          Source: SAL  Market Seg: GCB  IATA #1:              AR Account:          Agent #: 9999

"12-DEC-14 NEW    T    Callender, Paula               DDN   AERORED3         95.66  1  0  0  CA                            xx-xx"
13-DEC-14 Group:        CFTID: 359421   Source: WEB  Market Seg: TQD  IATA #1:              AR Account: 8414     Agent #: CRS

"12-DEC-14 NEW    T    Fung, Patrick P                DDN   EXPRM15          82.94  1  0  0  CA                            xx-xx"
15-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: CRS

"12-DEC-14 NEW    T    Lau, Kerry                     DDN   EXPRM15          82.94  1  0  0  CA                            xx-xx"
15-DEC-14 Group:        CFTID: 10002659 Source: WEB  Market Seg: TRS  IATA #1:              AR Account: 7902     Agent #: CRS

"13-DEC-14 MOD    T    Chou, Muhsiang                 DDN   SPGDI             0.00  1  0  0  AX   xxxxx1003

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

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.

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.

Edited 1 Year Ago by Reverend Jim

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

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

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

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).

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

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.

Edited 1 Year Ago by Minimalist

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

Edited 1 Year Ago by doncwilson_1

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

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

Attachments 3.jpg 0.77 KB

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!

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.

Edited 1 Year Ago by Minimalist

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.

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.

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?

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.

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?

Edited 1 Year Ago by cgeier

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

Happy Holidays!

Don

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.

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

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.

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!

This question has already been answered. Start a new discussion instead.