Link to home
Start Free TrialLog in
Avatar of Computer Guy
Computer Guy

asked on

How can I format this TSV File?

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
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

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
Avatar of Computer Guy
Computer Guy

ASKER

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....
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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!
To include any possible Mix content in brackets:
J2: =A2&" - "&E2&" - "& D2 & IF(C2," (ex. warn)","") & IF(F2<>""," [" & F2 & "]","") & IF(G2="",""," - " &G2

-Glenn
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

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

@Qlemo

I think the hyphen characters are meant to be inter-column tab characters.
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 "".
Yes, my solution is for Excel, so hyphens are correct.  I assume the questioner wanted an Excel solution.

-Glenn
Sent from my Windows Phone
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.
@Glenn

I assumed that the user wanted to transform the data and that Excel (or VBScript) was his tool of choice.
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 ;-).
sounds ineffective
An import routine (VBA) would probably be nice to eliminate all that messiness
@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
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
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