1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Text File Conversion (Need Help)

Discussion in 'Business Applications' started by Fivecorners, Aug 29, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Fivecorners

    Fivecorners Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    8
    Hello Everyone,
    I'm trying to convert text files with an unfamiliar format into something more useful like .XLS or CSV, etc. Here's a sample:

    * * * * * * * (original data) * * * * * * *

    @X=51.36,[email protected][email protected]@
    @X=51.36,[email protected]@[email protected]
    @X=51.36,[email protected]@[email protected]
    @X=51.36,[email protected]@[email protected]
    @X=111.42,[email protected]@[email protected]
    @X=111.42,[email protected]@[email protected]
    @X=111.42,[email protected]@[email protected]
    @X=111.42,[email protected]@[email protected]
    @X=183.78,[email protected]@[email protected]
    @X=183.78,[email protected]@[email protected]
    @X=183.78,[email protected]@[email protected]
    @X=183.78,[email protected]@[email protected]

    * * * * * * * (end result) * * * * * * *

    PART_NUMBER<delimiter>DESCRIPTION<delimiter>STOCKROOM<delimiter>QUANTITY
    PARTNUM001<delimiter>DESCRIP001<delimiter>A<delimiter>47
    PARTNUM002<delimiter>DESCRIP002<delimiter>B<delimiter>562

    (The delimiter could be a tab, comma, Excel column, whatever.)

    I suppose I could plug away at writing some kind of program, but maybe there's an existing method I can use without "re-inventing the wheel." Any ideas? Thanks in advance!
     
  2. jo15765

    jo15765

    Joined:
    Oct 11, 2011
    Messages:
    307
    What if you open the file with Excel, and then choose the column (I am assuming it would put all of the data in A1, A2, A3 etc, then choose (this is in Excel 2007) Data --> Text To Columns --> then choose Delimited from the prompt then choose the delimiter?
     
  3. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    jo15765 is correct for simply parsing it out. When selecting deliimeters use the comma and in "other" put the @ sign and it will parse.

    I look at this the other day. I think you will need a simple vb script to arrange this in a meaningful flat file. I couldn't get a grasp of what the X Y business was supposed to be...X = part number? Y = what?
     
  4. Fivecorners

    Fivecorners Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    8
    Thanks for responding, jo15765 and Dan. I've already done this type of procedure to parse the text into Excel so that I could fiddle around with it. I guess I forgot to explain that the X and Y numbers are not part of the data--those numbers simply indicate the horizontal and vertical starting point of the text string on a page. I don't care about the actual numbers themselves; I just want to use them to arrange the data into a logical structure, whether it is records/fields, rows/columns, lines/whitespace or whatever.
    I was hoping that somebody would recognize the data format and tell me it's the so-and-so format. Then I would search for a so-and-so to csv converter or a so-and-so to Excel converter or something. I suppose I could use something like Parse-o-matic, but I was trying to avoid having to learn another language. Or I might have to write a program from scratch.
    Any ideas about how to approach this?
     
  5. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Do any VB script? Here's a quick version. I did a test on your data and it worked!
    Copy and paste to note pad, save as SomeName.vbs and edit the InputFile and Outfile values

    '***********************************
    'draceplace
    '09/07/2012
    ''***********************************
    '*******File handling Constants*****
    Const ForReading = 1
    Const ForWriting = 2
    Const ForAppending = 8
    '******Counters********************
    cntRead = 0
    cntRead4 = 0
    cntWrite = 0
    '*******File Definitions**********************
    InputFile = "H:\Ascripts\5Corners01.txt"
    outFile = "H:\Ascripts\List5.csv"
    '**********************************************
    Msg1 = "Read 4 lines then Write1" & vbCrlf & _
    "------------------------------------------------------------------"
    '**Start of Program****************************
    selMistake = MsgBox(Msg1,VbOkCancel,"Texas Schools Byte")
    If selMistake = vbOK then '---Big IF will cancel whole process-----

    '****************************************************************
    '********open input file*****************************
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTextFile = objFSO.OpenTextFile(InputFile, ForReading)
    Set objOutFile = objFSO.OpenTextFile(outFile, ForWriting, True)

    'skipping the header'
    'strLine = objtextFile.ReadLine
    '****************************************************************
    '********Loop til end of input csv file******************************
    Do While objTextFile.AtEndOfStream <> True
    strLine = objtextFile.ReadLine
    cntRead = cntRead + 1
    cntRead4 = cntRead4 + 1
    If inStr(strLine,",")> 0 then
    strLine = Replace(strLine,"@",",")

    csvF1 = split(strLine, ",")
    ' Wscript.Echo cntRead4 & vbCrLf & "Input: " & strline
    'set up for using input file

    Select Case (cntRead4)

    Case 1
    strF1 = csvF1(3)
    Case 2
    strF2 = csvF1(3)
    Case 3
    strF3 = csvF1(3)
    Case 4
    strF4 = csvF1(3)
    strOut = strF1 & "," & strF2 & "," & strF3 & "," & strF4
    objOutFile.WriteLine strOut
    cntWrite = cntWrite + 1
    cntRead4 = 0
    End Select
    End If 'make sure there's a comma
    Loop

    '****End of Job Routine ***********************************************
    End If
    msgEOJ = vbCrLf & "Number Read: " & cntRead & _
    vbCrLf & "Records output: " & cntWrite
    selEOJ = MsgBox(msgEOJ,VbOk,"Arkansas Rules!")

    '***********Close Files if open.
    If selMistake = vbOK then
    objTextFile.Close
    objOutFile.Close
    Set objFSO = Nothing
    Set objTextFile = Nothing
    Set objOutFile = Nothing

    Else
    End If
     
  6. Fivecorners

    Fivecorners Thread Starter

    Joined:
    Mar 30, 2010
    Messages:
    8
    Woohoo! Fantastic! Many thanks, Dan! I was not expecting somebody to actually write a conversion program. This will do the trick. Looks like I'm gonna hafta brush up on VB, so I can be prepared if the data format changes.
     
  7. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Your welcome 5corners, pass it on and help someone else when you can.

    This is really a good sampler of vb. Opens/closes, read, write, Ifs, Case, split, replace, loops...all kinds of fun stuff. I do a lot of text file reformats and it was quick change to some existing code I had. Replaceing the "@" with a comma then let 'split' do the work was key to easy!
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1066972

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice