#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Need .vbs code to swap columns in .txt files


    hi all, sorry for i am a newbie in .vbs. here is my problem:

    i have a text file consisting of more than 20k lines, and with a fixed column width. example of data is below:

    Column 1 - 36 ----Column 37 - 72----- -Column 73 - 91----
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805
    Abcd Efdrsdfg 123456678901234567890 540040001610082805

    now, what i want to do is, swap column 37-72 to column 73-91. with the same .txt file. how can i do this using .vbs script to make is automatic.

    thank you in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    I suspect what you have supplied is not real data, as the lines are all identical. If what you are after is to sort the file starting at column 73, the easiest and fastest way is to use the type command, and pipe the results to the sort command, and output to a new file.

    type file1.txt|sort /+73>file1A.txt

    J.A. Coutts
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    data is only a representation to show what my problem is. since the real data contains confidential files. can you provide me of a script?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    18
    Rep Power
    0

    not worth bothering with VB


    Cut and paste text into column A of an excel spreadsheet
    cell B1 = =left(a1,36)&mid(a1,73,19)&mid(a1,37,36)
    drag the formula down to the end of your data
    cut and paste back to your doc

    use Table>Convert>Table to text if you need it as text rather than a table

    takes approx 30 secs total
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    Thank you even if nobody is able to help me, anyways i did find the solution to my problem. The code is below in case others will meet the same problem as mine, this might help.

    Const ForReading = 1
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set outPut = objFSO.CreateTextFile("C:\test1.txt", true)
    Set objTextFile = objFSO.OpenTextFile("C:\test.txt", ForReading)

    Do Until objTextFile.AtEndOfStream
    strLine = objTextFile.Readline
    col1 = Mid(strLine, 1, 13)
    col2 = Mid(strLine, 37, 20)
    col3 = Mid(strLine, 73, 18)
    outPut.WriteLine(col1 & " " & col3 & " " & col2)
    Loop

    outPut.Close()
    Set objFSO = Nothing
    Set outPut = Nothing

IMN logo majestic logo threadwatch logo seochat tools logo