MP3 Tags to Excel Not working

[giddyhead]

Hello,

I have been trying for a while now to have the MP3 Tags from the song files added to an excel spreadsheet. When I use the gettags[0] in the last for loop it only provides the information on the last song. What can be done so that it cycles through and add the appropriate Tags in the associated columns, Artist, Title, Year, etc? Thanks

import mutagen,xlrd, glob,re,openpyxl,os
#from mutagen.easyid3 import EasyID3
from os import walk
from pprint import pprint 
from tinytag import TinyTag, TinyTagException
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from mp3_tagger import MP3File
from string import ascii_uppercase

list = os.listdir('C:\\Users\\mrdrj\\Desktop\\sdf\\') # directory path of files
number_files = len(list) +1

def ExtractMP3TagtoExcel():
        
    print('Starting Program')
    
    tracks= []
    gettags =[]
    getit = []

    for root, dirs, files, in os.walk ('C:\\Users\\mrdrj\\Desktop\\sdf\\'):
        for name in files:
            if name.endswith(('.mp3','.m4a','.flac','.alac')):

                tracks.append(name) #Add Media Files

                try:

                    temp_track = TinyTag.get(root + '\\' + name)
                    mp3 = MP3File(root + '\\' + name)
                    #tags = mp3.get_tags()
                    print(root, '-',temp_track.artist, '-', temp_track.title)

                    gettags2 = [temp_track.album, temp_track.albumartist, temp_track.artist, temp_track.audio_offset,
                                temp_track.bitrate, temp_track.comment, temp_track.composer, temp_track.disc,
                                temp_track.disc_total, temp_track.duration, temp_track.filesize, temp_track.genre,
                                temp_track.samplerate, temp_track.title, temp_track.track, temp_track.track_total,
                                temp_track.year] #Add Tags to list
                    print('----' * 20)

                    for x in range(len(gettags2)):
                        gettags.append(gettags2[x]) #Loop until All Tags are Added to List
                        print(gettags2[x]) 
                except TinyTagException:
                    print('Error')
                

   
    wb = Workbook()
    os.chdir('C:\\Users\\mrdrj\\Desktop\\cqq\\CD 3\\')
    dest_filename = '11empty_book11.xlsx'
    newFile = dest_filename
    wb = openpyxl.load_workbook(filename = newFile)        
    worksheet = wb.active
    
    ws1 = wb.active
    ws = wb.active
    ws1.title = "MP3 Info" # Main Tab
    sheet = "MP3 Info"
    r = 0
       
    #print(Mp3Tagsexel)
    for col in range(1, 2): # Add how many Tabs 
     #ws1.append(range(5)) #Add values to Rows
        for row in range(1, number_files): 
            #for col in range(1, 8): # Number of colums static
                #print('------View Results------')
                              
                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                    
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = gettags[0]

                    column_cell = 'B'
                    ws1['B1'] = 'Contributing Artists'
                    ws1[column_cell + str(row + 1)] =  gettags[1]    

                    column_cell = 'C'
                    ws1['C1'] = 'Title'
                    ws1[column_cell + str(row + 1)] = gettags[2] 

                    column_cell = 'D'
                    ws1['D1'] = 'Total Number of Disk'
                    ws1[column_cell + str(row + 1)] = gettags[3] 

                    column_cell = 'E'
                    ws1['E1'] = 'Genre'
                    ws1[column_cell + str(row + 1)] =  gettags[4] 

                    column_cell = 'F'     
                    ws1['F1'] = 'Disc Number'
                    ws1[column_cell + str(row + 1)] =  gettags[0] 

                    column_cell = 'G'
                    ws1['G1'] = 'Track Duration'
                    ws1[column_cell + str(row + 1)] =  gettags[0] 

    wb.save(filename=dest_filename)
                
                    
ExtractMP3TagtoExcel()

[ibreeden]

Hi Giddyhead, It looks very impressive. I don't know the techniques you are using but I see you are adding the data to cells 'A1', 'B1', 'C1' etc. every time. I guess on the second loop the data should be added to cells 'A2', 'B2', 'C2' etc. On the third loop to 'A3' etc. Am I correct?

[giddyhead]

Hi Giddyhead, It looks very impressive. I don't know the techniques you are using but I see you are adding the data to cells 'A1', 'B1', 'C1' etc. every time. I guess on the second loop the data should be added to cells 'A2', 'B2', 'C2' etc. On the third loop to 'A3' etc. Am I correct?

Hi ibreeden,

That is correct 'A3',etc and Thanks. Don't really have a specific technique as of today as I am still learning python. As I am trying to find a way to have the data display into the cells, as I am not sure if 3 loops is much to have it completed. If it is simpler how can I make a loop that put the headers as the MP3 Tags and the data to match in the cells a possibility? Thanks

[ibreeden]

I am sorry, I was completely wrong. The problem lies somewhere else. The problem is the definition of gettags[]. This should be a two dimensional list. Each item of gettags[] should be a copy of gettags2[]. Can you change this:

                    for x in range(len(gettags2)):
                        gettags.append(gettags2[x]) #Loop until All Tags are Added to List

... to this:

                    #append slice of gettags2, containing the entire gettags2
                    gettags.append(gettags2[:])

(You must use the slice operator [:] to make a copy or else a reference will be made to gettags2.)
Then also change the following:

                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = gettags[0]
                    ...

Instead of gettags you should use r:

                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = r[0]
                    ...

... and then the same for all columns.

There is an inefficiency in your program. Why fill the header on row 1 each time again?

Let me know if it works.

[giddyhead]

I am sorry, I was completely wrong. The problem lies somewhere else. The problem is the definition of gettags[]. This should be a two dimensional list. Each item of gettags[] should be a copy of gettags2[]. Can you change this:

                    for x in range(len(gettags2)):
                        gettags.append(gettags2[x]) #Loop until All Tags are Added to List

... to this:

                    #append slice of gettags2, containing the entire gettags2
                    gettags.append(gettags2[:])

(You must use the slice operator [:] to make a copy or else a reference will be made to gettags2.)
Then also change the following:

                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = gettags[0]
                    ...

Instead of gettags you should use r:

                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = r[0]
                    ...

... and then the same for all columns.

There is an inefficiency in your program. Why fill the header on row 1 each time again?

Let me know if it works.

I have put the headers before the loop starts so it does it only once. Did not think about those changes Thanks for that. When I applied the changes unfortunately it did not apply the tags of each song it only did the last song MP3 Tags in some cells. What updates can I make to update the cells? Thanks

[giddyhead]

I am sorry, I was completely wrong. The problem lies somewhere else. The problem is the definition of gettags[]. This should be a two dimensional list. Each item of gettags[] should be a copy of gettags2[]. Can you change this:

                    for x in range(len(gettags2)):
                        gettags.append(gettags2[x]) #Loop until All Tags are Added to List

... to this:

                    #append slice of gettags2, containing the entire gettags2
                    gettags.append(gettags2[:])

(You must use the slice operator [:] to make a copy or else a reference will be made to gettags2.)
Then also change the following:

                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = gettags[0]
                    ...

Instead of gettags you should use r:

                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = r[0]
                    ...

... and then the same for all columns.

There is an inefficiency in your program. Why fill the header on row 1 each time again?

Let me know if it works.

I have put the headers before the loop starts so it does it only once. Did not think about those changes Thanks for that. When I applied the changes unfortunately it did not apply the tags of each song it only did the last song MP3 Tags in some cells. What updates can I make to update the cells? Thanks

The following is an update on the enter data excel spread sheet I have changed this..

wb = Workbook()
    os.chdir('C:\\Users\\mrdrj\\Desktop\\cqq\\CD 3\\')
    dest_filename = '11empty_book11.xlsx'
    newFile = dest_filename
    wb = openpyxl.load_workbook(filename = newFile)        
    worksheet = wb.active
     
    ws1 = wb.active
    ws = wb.active
    ws1.title = "MP3 Info" # Main Tab
    sheet = "MP3 Info"
    r = 0
        
    #print(Mp3Tagsexel)
    for col in range(1, 2): # Add how many Tabs 
     #ws1.append(range(5)) #Add values to Rows
        for row in range(1, number_files): 
            #for col in range(1, 8): # Number of colums static
                #print('------View Results------')
                               
                for r in gettags:
                    #_ = ws1.cell(column=col, row=row, value= gettags2[1]) #"{0}".format(get_column_letter(col)))
                     
                    column_cell = 'A'
                    ws1['A1'] = 'Album'
                    ws1[column_cell + str(row + 1)] = gettags[0]
 
                    column_cell = 'B'
                    ws1['B1'] = 'Contributing Artists'
                    ws1[column_cell + str(row + 1)] =  gettags[1]    
 
                    column_cell = 'C'
                    ws1['C1'] = 'Title'
                    ws1[column_cell + str(row + 1)] = gettags[2] 
 
                    column_cell = 'D'
                    ws1['D1'] = 'Total Number of Disk'
                    ws1[column_cell + str(row + 1)] = gettags[3] 
 
                    column_cell = 'E'
                    ws1['E1'] = 'Genre'
                    ws1[column_cell + str(row + 1)] =  gettags[4] 
 
                    column_cell = 'F'     
                    ws1['F1'] = 'Disc Number'
                    ws1[column_cell + str(row + 1)] =  gettags[0] 
 
                    column_cell = 'G'
                    ws1['G1'] = 'Track Duration'
                    ws1[column_cell + str(row + 1)] =  gettags[0] 
 
    wb.save(filename=dest_filename)

To this. It enters the data however it duplicates it. How can I solve the duplication prior to it being entered into the excel document. I have tried TinyTag.valid_keys.keys()to have the headers added via code but they do not appear to be no attribute 'valid_keys' what options do I have so all keys can be produce? Thanks

os.chdir('C:\\Users\\mrdrj\\Desktop\\cqq\\Brimstone CD 3\\')
                header = [u'Album', u'Artist', u'Track', u'Title', u'Duration']
                #new_data = [[u'Album Test', u'Artist Test', 1, u'Title Test',u'3.00'],
                 #[u'Album Test 2', u'Artist Test 2', 2, u'Title Test 2',u'3.45']]
                new_date = gettags
                wb = Workbook()
                new_data = gettags
                dest_filename = '11empty_book11.xlsx'
                ws1 = wb.active
                ws1.title = "MP3 Tags"
                print()
                ws1.append(header)
                for row in new_data:
                    ws1.append(row)
                    wb.save(filename=dest_filename)