How can I convert this tsv file into something readable?

I've got a TSV file that looks like this when I bring the data into Notepad:

id      first_name      last_name      title      company      annual_ad_budget      current_agency      industry      address_line1      address_line2      city      state      zip      phone      fax      email      contact_log      last_contact      notes      press 2715      Sue      Markowicz      Global Advertising Agency Manager       ARTICLE TOOLS Email This Article E-Mail This Article P       Manufacturing       AK       new      10/19/03       2639      Ted      Block       $20MM Japane       Manufacturing      454 Las Gallinas Ave, #280       San Rafael      CA      94909      415-492-1975      415-499-0500      7/1 faxed Adweek chart, 6/24 sent FedEx per Dick, Represents $20MM Japanese tech client re: Review, Cell: 415-407-1347      07/02/03       2776       Manufacturing       AK       '0'       2777       Manufacturing       AK       '0'       2780       Manufacturing       AK       '0'       1      Kevin      McCallum      Vice President, Marketing      1-800-Contacts       Manufacturing      66 E. Wadsworth Park Drive      3rd Floor      Draper      UT      84020      801-924-9800      801-924-9900       7/29 letter, LP-no calls      07/29/03       2      Mark      Eddington      Director of Marketing      1-800-Contacts       Manufacturing      66 E. Wadsworth Park Drive      3rd Floor      Draper      UT      84020      801-924-9800      801-924-9900      7/29 intro e-m      07/29/03       3

I recognize the "tab" dynamic, but how can I convert that into something legible - columns and rows?

Bruce GustPHP DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Assuming Excel 2007...

1) Click the Data tab on the ribbon

2) In Get External Data, click From Text

3) Browse for and select your file (you may have to change the file filter to All Files

4) Use the wizard to process this as tab delimited
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
I can't tell exactly, but it looks like it isn't delimited, but may be using fixed spaces. So using the method above, try using the options of fixed-width fields.
Bruce GustPHP DeveloperAuthor Commented:
Excel 2000, actually.

Does that make a difference?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Bruce GustPHP DeveloperAuthor Commented:

I was able to open up the file which I've saved as a .txt and Excel recognizes it as a tab delimited format, but it only gives me one row.

Make sense?

So, I'lll get this:

id      first_name      last_name      title      company      annual_ad_budget      current_agency      industry      address_line1      address_line2      city      state      zip      phone      fax      email      contact_log      last_contact      notes      press 2715
But then instead of the rest of the data being "filed" under the column titles, I get one continuous row, which ultimately exceeds Excel's ability to process and I'll get an error that says, "File Not Loaded Completely."

How can I compel the data to be processed as columns and rows rather than just one continuous column?
Patrick MatthewsCommented:
>>Excel 2000, actually.
>>Does that make a difference?

No, but you use the menu instead of the ribbon to initiate.  Should be under Data in the menu, either Get External Data or Open Text File (I do not have Excel 2000 any more, so I cannot check it myself).
Bruce GustPHP DeveloperAuthor Commented:
OK, I want to believe I'm poised on the threshold of great things but I only get one row. I can never get the data to import correctly. Instead of columns and rows, I only get one row. Is there anything else that I can try to get this thing to read correctly.

Just so you know, the data that I'm working with is located here:

Perhaps there's another way to import this mess into something readable. Thoughts?
Patrick MatthewsCommented:

That file is going to be...difficult.  Not only are there no clean column delimiters, there are no clean row delimiters either, so just getting the rows delineated is a chore.

The code below seems to do a very good (albeit not perfect) job of at least figuring out where the linebreaks should go; from there, it will simply be impossible to sort out the columns correctly.  The code is VBScript.  To use it:

1) Save your data file to, say, c:\Input.txt
2) Open Notepad
3) Paste in the code below
4) Save the file as c:\Test.vbs
5) Close Notepad
6) Doubleclick the VBS file, and it will write the results to c:\Output.txt

Good luck in parsing the results!

The code uses Regular Expressions to do its thing.  For more info on how to use RegExp in VBScript, please see my article here:

Dim fso, tsIn, tsOut, WholeThing, RegX

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\Input.txt")
WholeThing = tsIn.ReadAll
Set tsIn = Nothing

Set RegX = New RegExp
With RegX
    .Pattern = "(\d\d/\d\d/\d\d)( )(\d{2,})( )"
    .Global = True
    WholeThing = .Replace(WholeThing, "$1" & Chr(13) & Chr(10) & "$3$4")
    .Pattern = "( )(\d{2,})"
    .Global = False
    WholeThing = .Replace(WholeThing, Chr(13) & Chr(10) & "$2")
End With

Set tsOut = fso.CreateTextFile("c:\Output.txt", True)
tsOut.Write WholeThing
Set tsOut = Nothing
Set fso = Nothing
Set RegX = Nothing

MsgBox "Done"

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:

Glad to help.  BTW, if you have not done so already, I would appreciate it if you could go to that article I linked to in the answer, and vote 'Yes' for 'Was this helpful'.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.