Solved

How can I format this TSV File?

Posted on 2014-07-23
18
238 Views
Last Modified: 2014-07-25
Hi,

I have this TSV File attached, how can I format it like so:

Title - If Warning - TRUE add " (ex. warn) after Title, else don't add anything

If Mix not "" then add [Mix Content] after title (and ex. warn if applicable)

Track - Artist - Title - Genre
Track - Artist - Title (ex. warn) - Genre
Track - Artist - Title [Mix] - Genre
Track - Artist - Title (ex. warn) [Mix] - Genre
Tab-List.txt
0
Comment
Question by:Computer Guy
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40215711
Are you importing this into Excel or exporting an Excel sheet to tab-delimited file (TSV)?

If importing, you could add four columns to the side of the data to produce the new results.  For example, your data imports into seven columns (A-G), so in columns J-M you add:
J1: "Track"
K1: "Artist"
L1: "Title"
M1: "Genre"

then in row 2 add these formulas and copy down
J2: =A2
K2: =E2
L2: =D2 & IF(C2," (ex. warn)","") & IF(F2<>""," [Mix]","")
M2: =IF(G2="","",G2)

----------------------------
I added an example file with your data imported.

If I misinterpreted your question, please let me know.

Regards,
-Glenn
EE-Q-28482623.xlsx
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 40215783
Thanks! I want the final result to be one cell or line with the contents of one of the lines


"Track - Artist - Title - Genre"
or the others....
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40215807
Oh, okay, that can be resolved like so.  This will be the new formula in cell J2; just copy it down:
J1: "Track - Artist - Title - Genre"
J2: =A2&" - "&E2&" - "& D2 & IF(C2," (ex. warn)","") & IF(F2<>""," [Mix]","") & IF(G2="",""," - " &G2)

Regards,
-Glenn
EE-Q-28482623.xlsx
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 45

Expert Comment

by:aikimark
ID: 40215988
You can do this in Powershell with these four statements:
$m = import-csv tab-list.txt -delimiter "`t"
foreach ($mm in $m){$t=$mm.title;if ($mm.warning -eq 'TRUE'){$t=$t + " (ex. warn)"};if ($mm.mix -eq ""){}else{$t=$t + " [MIX]"};$mm.title=$t}
$m | select Track , Artist , title , genre | export-csv -delimiter "`t" NewTabFile.txt -notypeinformation
(get-content newtabfile.txt) | foreach {$_ -replace '"',""} | out-file -filepath newtabfile.txt -force

Open in new window

0
 
LVL 3

Author Comment

by:Computer Guy
ID: 40216368
Hi! Glenn, I love your solution! Thanks!

Quikc question, how do I get the contents of the Mix cell where [Mix] is

so [Dance Mix]

Thanks!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40216448
To include any possible Mix content in brackets:
J2: =A2&" - "&E2&" - "& D2 & IF(C2," (ex. warn)","") & IF(F2<>""," [" & F2 & "]","") & IF(G2="",""," - " &G2

-Glenn
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40216536
This script will include the actual content of the MIX column.
$m = import-csv tab-list.txt -delimiter "`t"
foreach ($mm in $m){$t=$mm.title;if ($mm.warning -eq 'TRUE'){$t=$t + " (ex. warn)"};if ($mm.mix -eq ""){}else{$t=$t + " [" + $mm.mix + "]"};$mm.title=$t}
$m | select Track , Artist , title , genre | export-csv -delimiter "`t" NewTabFile.txt -notypeinformation
(get-content newtabfile.txt) | foreach {$_ -replace '"',""} | out-file -filepath newtabfile.txt -force

Open in new window

0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40216621
Taking over the idea of aikimark to use PowerShell, generating a file only containing the combined requested info:
Import-CSV tab-list.txt -delimiter "`t" | % {
  "$($_.Track) - $($_.Artist) - $($_.Title)" +
  ' (ex. warn)' * ($_.Warning -eq 'TRUE') +
  " [$($_.Mix)]" * ($_.Mix -ne '') +
  " - $($_.Genre)" * ($_.Genre -ne '')
} | Out-File newtabfile.txt -force

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40216744
@Qlemo

I think the hyphen characters are meant to be inter-column tab characters.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40216913
Mark,

Glenn used hyphen literally, so I don't think it should be a tab.

"String" * 2   replicates the string (= "StringString"). $true is 1, $false is 0, and "lklklkl" * 0 is "".
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40216946
Yes, my solution is for Excel, so hyphens are correct.  I assume the questioner wanted an Excel solution.

-Glenn
Sent from my Windows Phone
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40216950
That's a clever trick.  I'll add it to my toolbox.  I'm used to True = -1.  I need to add this difference to my internal language translation.  I still think in VB/VBA/VBScript and translate it.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40216958
@Glenn

I assumed that the user wanted to transform the data and that Excel (or VBScript) was his tool of choice.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40216974
According to http:#a40216368 Glenn is probably correct, and "VB Script" reads as "VBA", keeping it inside of Excel.
On the other hand - having a tab delimited file, importing that in Excel, pasting formulas, ... sounds ineffective ;-).
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40217012
sounds ineffective
An import routine (VBA) would probably be nice to eliminate all that messiness
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40217304
@aikimark,
Yes, if I had to repetitively import and re-parse this data, I'd create a VBA routine to read each line and convert.  Ineffective, notsomuch then.

-Glenn
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40217333
While you could read and parse this line-by-line, you can also read the entire file in one I/O operation and then parse the results with the Split() function for the lines (vbCrLf delimiter) and the fields (vbTab delimiter).  You can push entire rows of data in single operations.

You can also use a regular expression to parse all the data with a single pattern.  The resulting 2D array can be pushed into Excel in a single operation.
Pattern: (.*)\t(.*)\t(.*)\t(.*)\t(.*)\t(.*)\t(.*)\r\n

I just answered this question (http:Q_28481317.html ) and showed code for both row-by-row and entire block population of Excel worksheet.

My article on the subject:

Fast Data Push to Excel:  http:A_2253.html
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40217345
The posted pattern parses the TSV file as follows:
Match 0 Start(0) Length(46) 

SubMatch 0: Track
SubMatch 1: Release
SubMatch 2: Warning
SubMatch 3: Title
SubMatch 4: Artist
SubMatch 5: Mix
SubMatch 6: Genre

Match 1 Start(46) Length(60) 

SubMatch 0: 1
SubMatch 1: Mix CD 5
SubMatch 2: FALSE
SubMatch 3: Tribal Dance
SubMatch 4: 2 Unlimited
SubMatch 5: Techno Jam
SubMatch 6: Dance

Match 2 Start(106) Length(33) 

SubMatch 0: 2
SubMatch 1: Mix CD 6
SubMatch 2: TRUE
SubMatch 3: Numb
SubMatch 4: U2
SubMatch 5: 
SubMatch 6: Top 40

Match 3 Start(139) Length(52) 

SubMatch 0: 3
SubMatch 1: Mix CD 7
SubMatch 2: TRUE
SubMatch 3: What Is Love?
SubMatch 4: Haddaway
SubMatch 5: "12"" Mix"
SubMatch 6: 

Match 4 Start(191) Length(57) 

SubMatch 0: 4
SubMatch 1: Mix CD 8
SubMatch 2: TRUE
SubMatch 3: Insane In The Brain
SubMatch 4: Cypress Hill
SubMatch 5: 
SubMatch 6: Urban

Match 5 Start(248) Length(55) 

SubMatch 0: 5
SubMatch 1: Mix CD 9
SubMatch 2: TRUE
SubMatch 3: Saturday Night
SubMatch 4: Ned's Atomic Dustbin
SubMatch 5: 
SubMatch 6: 

Match 6 Start(303) Length(41) 

SubMatch 0: 6
SubMatch 1: Mix CD 10
SubMatch 2: TRUE
SubMatch 3: Chains
SubMatch 4: His Boy Elroy
SubMatch 5: 
SubMatch 6: 

Match 7 Start(344) Length(53) 

SubMatch 0: 7
SubMatch 1: Mix CD 11
SubMatch 2: TRUE
SubMatch 3: I'm Free
SubMatch 4: Jon Secada f./Marc Coen
SubMatch 5: 
SubMatch 6: 

Match 8 Start(397) Length(59) 

SubMatch 0: 8
SubMatch 1: Mix CD 12
SubMatch 2: TRUE
SubMatch 3: Somebody's Baby
SubMatch 4: Pat Benatar
SubMatch 5: Ubeat Tempo
SubMatch 6: 

Match 9 Start(456) Length(60) 

SubMatch 0: 9
SubMatch 1: Mix CD 13
SubMatch 2: TRUE
SubMatch 3: When I Fall In Love
SubMatch 4: Celine Dion
SubMatch 5: 
SubMatch 6: Adult AC

Match 10 Start(516) Length(46) 

SubMatch 0: 10
SubMatch 1: Mix CD 14
SubMatch 2: TRUE
SubMatch 3: Ways Of The Wind
SubMatch 4: PM Dawn
SubMatch 5: 
SubMatch 6: 

Match 11 Start(562) Length(60) 

SubMatch 0: 11
SubMatch 1: Mix CD 15
SubMatch 2: TRUE
SubMatch 3: Love For Love
SubMatch 4: Robin S. & Haddaway
SubMatch 5: 
SubMatch 6: Dance

Match 12 Start(622) Length(36) 

SubMatch 0: 12
SubMatch 1: Mix CD 16
SubMatch 2: TRUE
SubMatch 3: Slam
SubMatch 4: Onyx
SubMatch 5: 
SubMatch 6: Urban

Match 13 Start(658) Length(84) 

SubMatch 0: 13
SubMatch 1: Mix CD 17
SubMatch 2: FALSE
SubMatch 3: Boom! Shake The Room
SubMatch 4: DJ Jazzy Jeff And The Fresh Prince
SubMatch 5: 
SubMatch 6: Top 40

Match 14 Start(742) Length(68) 

SubMatch 0: 14
SubMatch 1: Mix CD 18
SubMatch 2: TRUE
SubMatch 3: Killer/Papa Was A Rolling Stone
SubMatch 4: George Michael
SubMatch 5: 
SubMatch 6: 

Match 15 Start(810) Length(54) 

SubMatch 0: 15
SubMatch 1: Mix CD 19
SubMatch 2: TRUE
SubMatch 3: It's Alright
SubMatch 4: Kris Kross
SubMatch 5: Dance Mix
SubMatch 6: 

Match 16 Start(864) Length(44) 

SubMatch 0: 16
SubMatch 1: Mix CD 20
SubMatch 2: TRUE
SubMatch 3: Step It Up
SubMatch 4: Stereo MC's
SubMatch 5: 
SubMatch 6: 

Match 17 Start(908) Length(47) 

SubMatch 0: 17
SubMatch 1: Mix CD 21
SubMatch 2: TRUE
SubMatch 3: In My Nature
SubMatch 4: Nuttin' Nyce
SubMatch 5: 
SubMatch 6: 

Match 18 Start(955) Length(61) 

SubMatch 0: 18
SubMatch 1: Mix CD 22
SubMatch 2: TRUE
SubMatch 3: Only With You
SubMatch 4: Captain Hollywood Project
SubMatch 5: 
SubMatch 6: 

Match 19 Start(1016) Length(42) 

SubMatch 0: 19
SubMatch 1: Mix CD 23
SubMatch 2: TRUE
SubMatch 3: Booty Mack
SubMatch 4: Krash Man
SubMatch 5: 
SubMatch 6: 

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question