Solved

How can I format this TSV File?

Posted on 2014-07-23
18
236 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
 
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 68

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 68

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 68

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 49
Convert .PDF 6 43
Dynamic Excel Countdown Graphic 21 23
3rd level dependant list 4 33
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now