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?

brucegustPHP DeveloperAsked:
Who is Participating?
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

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.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

brucegustPHP DeveloperAuthor Commented:
Excel 2000, actually.

Does that make a difference?
brucegustPHP 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).
brucegustPHP 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:

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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.