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
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
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....
"Track - Artist - Title - Genre"
or the others....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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!
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
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
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
@Qlemo
I think the hyphen characters are meant to be inter-column tab characters.
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 "".
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
-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.
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 ;-).
On the other hand - having a tab delimited file, importing that in Excel, pasting formulas, ... sounds ineffective ;-).
sounds ineffectiveAn 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
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.
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(.
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:
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