Karaoke Forums

I seen in another topic that you could make excel hyperlink songs to your karaoke program (hosting), I would to know how to do this.



thanks
tim

The way I did it was as follows: (not exactly easy)
create a text file of the directory

go into dos and type "DIR > songlist.txt" in the appropriate directory

this will create a text file of the songs as listed

open that with excel, remove the top/bottom few rows (other non filename lists)

then you will have a list of all the files

use "text to collumns" command in the (under DATA).

Click on fixed width, and leave a line to only split at the start of the filename after the file size/date info.  Now you have a file of the songnames only.

This is an easy way to help create your songbooks as well.

Important note, make sure that the first record has the longest title name, otherwise excel may add an additional column of the end of some of the longer song names.

Now you have a list of all the songs in one collumn with both the .cdg and .mp3 files.

get rid of all of the .wav file names from the list (for simplicity).
in the collumn next to the filenames write "=right(B1,4)" (assuming the names are in the B collumn. That should give the last four letters to the right of thename (.cdg or .mp3).

Drag the formula that you just typed in down so it fills in for all of the songs.

sort by this column and delete all of the rows with .wav files.


No you have a list with all of the file names, and with a bit of editing you can create your songlist for a book.

To do that:
Make a copy of the filename collumn
Usually what you will have to do is use text to columns a second time this time with "delimited" select the box with "other" and type in "-"

this will allow you to have two new columns with artist and song title
(if your song names were in the Artist-title-CD format).

If there are extra "-"s in the file names you have to edit them out manually for example retype B-52s as B52s etc.

Finally remove the .cdg from the ends of your song names by using serch and replace all.


Getting back to the hyperlinks
Type in a new formula
="File:///C:karaokesongs\"&B1

where C:karaokesongs is the drive and folder where the songs are.
B1 is again the cell and column where your file complte filename is. (with .cdg extensions).

fill the formula by dragging it down so that the formula changes to B1, B2, Etc, but the actual text should read the songs

Copy that column and paste special as text.

The cells should be in the format to be hyperlinks but unfortunately excel will not recognize them as such yet.

Now the annoying step. click on a cell like you were editing the text, go to the end of the text and hit return.

It should now appear as a blue hyperlink

click on it and if .cdg is linked to your karaoke software it will play (after an annoying warning from Microsoft about opening files due to viruses that you CAN NOT DISABLE).

Unfortunately you have to do the click and return for each file, but it goes relatively fast if you just have click with the mouse at the end of the edit box, and it sends you to the next row each time. Sure with 8000 songs it is a pain, but it does work.



Yes I spend most of my days working on Excel, so it is a bit difficult to do if you are not very familiar with the program.

As a side note though, you are going to have to do most of the work anyway to make up a songlist.

Great advice Dr. Fred.  I would suggest however the the original command line entry should be "dir *.cdg /s /b>cdg.txt"  (/s means include subdirectories and /b is for brief ) this will list the path and name of all the cdg files on his computer if he started on the root directory but without the date etc. information.  Just a little less to mess with.

_________________
Okay, who took my pants?

This would be great for me to implement. However, my karaoke files are laid out as such....

A
B
C
D
E

Directories with sub-directories....

A
Alice in Chains
Alison Krauss
etc...

One example of a file just under the main directory #

F:\KARAOKE\#\12 Gauge - Dunkie Butt.CDG

Another example of a file in a sub-directory of a main lettered directory....

F:\KARAOKE\R\Rage Against the Machine\Rage Against Machine - Renegades Of Funk.cdg

As you can see, Rage gets a subdirectory under main directory of R.

When I use the text to column, I cannot (because I am using sub-directories) set an apropriate begining cutoff for the column. Help?!!?!

As exweed pointed out the command "dir /b /s >songlist.txt" solves that problem


Then you can get away from the annoyance of splitting text to columns, and you also have the songlist directory structure with the song name.

Sample result for a couple songs from the above command.

Then all you have to do is append the file:/// in front of the .cdg files as I discus before. (and then manually make them all links).

(my songs are grouped by  in subdirectories by Manufacturer, so I can keep track of things when I am ripping them from cd, these are from Dangerous Karaoke set)

C:\Documents and Settings\Owner.YOUR-755641B986\Desktop\karaoke\dangerous\Wonder, Stevie - Don't You Worry 'Bout A Thing - DG10-06.cdg
C:\Documents and Settings\Owner.YOUR-755641B986\Desktop\karaoke\dangerous\Wonder, Stevie - Don't You Worry 'Bout A Thing - DG10-06.wav
C:\Documents and Settings\Owner.YOUR-755641B986\Desktop\karaoke\dangerous\Young, Neil - Down By The River - DG10-07.cdg
C:\Documents and Settings\Owner.YOUR-755641B986\Desktop\karaoke\dangerous\Young, Neil - Down By The River - DG10-07.wav

Dr. Fred,

I just want to point out that for those of us who only have a basic knowledge of excel that spreadsheets are very good programs to make your songlist.  Some times a copy and paste from the manufactures song list in their online cataloge can save much of the typing. I create separate files for each disk or collection then copy it to my two masterlists and resort alphabetically one list is by artist the other by song. I have done it this way from the begining never bought song book software. Although actually the spreadsheet program I use is a Quatro pro which came with my computer. I use Excel at work and Quatro Pro seems to be about the same.

Thanks,

Jim_n_TN

I agree that excel can be easy to use but it is also intimidating to some who may not be familiar with a spreadsheet. Also for a beginning user they may not be familiar with all of its advantages.

I work with excel basically every day, often manipulating large amounts of data (I am a geneticist). Because I knew that this board had a wide range of experience with the program, I tried to keep my explanation and instructions at a very basic level. I had no intention of being condescending so please don't take offense.

DR. Fred,

No offense taken and hopfully none given. Excel can be a little intimidating. I don't have my songs on a harddrive so I thought people should know about using it for songlists without their songs being on a hard drive. You did lose me a bit.  Spreadsheets have many everyday simple uses.  I kept plant production records material waste records making weekly, monthly and year end reports.  This was all simple stuff.  At home besides songlists I do our family budget on it. Quatro Pro came with my 6 year old computer and I used it to avoid buying a songlist program.
People that don't have a spreadsheet program or have never used one would probably want to buy a songlist program.


Jim_n_TN

I give up. I cannot follow these instructions at all.

I have learned a quicker way to catalog new additions to my collections using the dir command and dumping it to excel (using find and replace to edit). Thanks for that.