Notices
Results 1 to 20 of 20
Like Tree2Likes
  • 2 Post By MacGyver1968

Thread: how to populate a database.

  1. #1 how to populate a database. 
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    I'm just starting to try and teach myself about database programming. I am fluent with setting up a new database in access but have come to understand that ms is doing away with access so I am now trying to learn sql.

    My question is, is there a way to populate a database once it is designed, with data automatically? More specifically. I want to create a database of all the karaoke files I have on my system. I know there must be a way, because a couple of karaoke host softwares I have used in the past automatically created a database of an entire directory that you designate. AS it is I already have an existing database created by one of those programs but it is on my old system and is not compatible with windows 8.

    I also don't like how the files were entered and would like to edit them to clean them up. In some cases it seems, the files were not tagged correctly and the artist is listed under title and the genre under artist and stuff like that. So when the software accesses the database using a query, sometimes the results are not what they should be.

    Will I have to manually populate my database in order to have it all entered correctly since many of the files (i have over 150k song files) were not tagged right?


    Reply With Quote  
     

  2.  
     

  3. #2  
    Cooking Something Good MacGyver1968's Avatar
    Join Date
    Aug 2006
    Location
    Dallas, Texas
    Posts
    2,051
    The best way to populate a database is to put all of the male databases and female databases together in the same room, and get them drunk. Then let nature take it's course. In about 9 months, you will have a fully populated database.



    Sorry SG, I couldn't resist.



    cosmictraveler and John Galt like this.
    Fixin' shit that ain't broke.
    Reply With Quote  
     

  4. #3  
    Universal Mind John Galt's Avatar
    Join Date
    Jul 2005
    Posts
    14,169
    What format is your current information in? It rather sounds as if you have what is a flat form database - I'm sure I'm using the wrong term, but basically you have your data in a single pool with rows and columns. i.e. it looks damned like an Excel sheet. Your wishing to work in a Relational Database, and why not. You could generate some neat reports that way.

    Anyway, if I'm correct in my understanding then
    a) wouldn't it be best to dump it to Excel and edit it there, then
    b) Import it into Acess from Excel.

    Or am I totally missing the point?
    Reply With Quote  
     

  5. #4  
    Suspended
    Join Date
    Apr 2007
    Location
    Pennsylvania
    Posts
    8,822
    You can import data to excel in several different forms using File, Get External Data, Import. You can also do update queries to update many records at a time, but you would need some systematic way to recognize when the data is in the wrong field.
    Reply With Quote  
     

  6. #5  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    Quote Originally Posted by MacGyver1968 View Post
    The best way to populate a database is to put all of the male databases and female databases together in the same room, and get them drunk. Then let nature take it's course. In about 9 months, you will have a fully populated database.



    Sorry SG, I couldn't resist.

    Lmao, I totally had that coming for the drawbridge answer I gave you on logical gates lol
    Reply With Quote  
     

  7. #6  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    Quote Originally Posted by John Galt View Post
    What format is your current information in? It rather sounds as if you have what is a flat form database - I'm sure I'm using the wrong term, but basically you have your data in a single pool with rows and columns. i.e. it looks damned like an Excel sheet. Your wishing to work in a Relational Database, and why not. You could generate some neat reports that way.

    Anyway, if I'm correct in my understanding then
    a) wouldn't it be best to dump it to Excel and edit it there, then
    b) Import it into Acess from Excel.

    Or am I totally missing the point?
    Well I need to get away from access all together because microsoft is dumping access. I need to move it to an sql database. I can't remember what the file extension is that the database has right now but I think it did open in access 2000 or something like that. but the last time I tried to open it, after a bunch of vista updates, I couldn't open the database except in the program that created it which was "just karoake" by tricerasoft.
    Reply With Quote  
     

  8. #7  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    Quote Originally Posted by Harold14370 View Post
    You can import data to excel in several different forms using File, Get External Data, Import. You can also do update queries to update many records at a time, but you would need some systematic way to recognize when the data is in the wrong field.
    Well for now I used cmd prompt methods to create a text file listing all files in the directory so that I can print it and possibly manually input the data if necessary but with over 150k files and some being duplicates, it will be a mind numbing task.

    The main reason I want to do it is because I am taking asp.net and ado.net classes and they work with putting databases into websites so that you can create shopping carts and stuff like that. the course work I am doing is using premade sql databases but I would like to know how to create the databases themselves so that I can use it to make my own website. I am hoping once I have my database accessible on a website I can add javascript or something to make the files playable from the web.

    or at least create my own application for hosting the karaoke because I haven't yet found a karaoke hosting software that I like.
    Reply With Quote  
     

  9. #8  
    Forum Junior TridentBlue's Avatar
    Join Date
    Jan 2013
    Posts
    207
    Use a scripting language like python, which can make automatic calls to the database:
    MySQL Python tutorial
    You can use that same language to get the data you want to put in out of text files, excel, etc.
    Reply With Quote  
     

  10. #9  
    Universal Mind John Galt's Avatar
    Join Date
    Jul 2005
    Posts
    14,169
    Quote Originally Posted by seagypsy View Post
    Quote Originally Posted by John Galt View Post
    What format is your current information in? It rather sounds as if you have what is a flat form database - I'm sure I'm using the wrong term, but basically you have your data in a single pool with rows and columns. i.e. it looks damned like an Excel sheet. Your wishing to work in a Relational Database, and why not. You could generate some neat reports that way.

    Anyway, if I'm correct in my understanding then
    a) wouldn't it be best to dump it to Excel and edit it there, then
    b) Import it into Acess from Excel.

    Or am I totally missing the point?
    Well I need to get away from access all together because microsoft is dumping access. I need to move it to an sql database. I can't remember what the file extension is that the database has right now but I think it did open in access 2000 or something like that. but the last time I tried to open it, after a bunch of vista updates, I couldn't open the database except in the program that created it which was "just karoake" by tricerasoft.
    I'm just running this the way I would do it. I'd carry out corrections in Excel because I very comfortable there. I'd import it into Access, so I could do some more elaborate things with it, then when I'd (re)learnt SQL I'd import it into whatever platform from Access.
    Reply With Quote  
     

  11. #10  
    Genius Duck Moderator Dywyddyr's Avatar
    Join Date
    Jan 2013
    Location
    Scunthorpe, UK
    Posts
    10,827
    Quote Originally Posted by seagypsy View Post
    Well for now I used cmd prompt methods to create a text file listing all files in the directory so that I can print it and possibly manually input the data if necessary but with over 150k files and some being duplicates, it will be a mind numbing task.
    If you write a batch file to create the text listing you can edit (CTRL-H is useful!) that and "turn it into" a CSV-workable file, which can then be imported directly into Excel. Once in Excel it's (relatively) easy to find duplicates.
    "[Dywyddyr] makes a grumpy bastard like me seem like a happy go lucky scamp" - PhDemon
    Reply With Quote  
     

  12. #11  
    Suspended
    Join Date
    Apr 2007
    Location
    Pennsylvania
    Posts
    8,822
    In Access, you can just run a find duplicates query.
    Reply With Quote  
     

  13. #12  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    I'm gonna talk to my asp.net and ado.net teacher about it. He is head of the IT comp science department at my school and designs all the degree programs. If anyone should know he would.
    Reply With Quote  
     

  14. #13  
    Iuvenis ducis Darkhorse's Avatar
    Join Date
    Mar 2011
    Posts
    105
    MSSQL Server has a management console application that allows your to manage completely your database. You can create new databases, schema's, tables, add columns to tables, etc etc etc. One of the functions of the management tool is to import data easily, including only take a subset of the data. Usually I export/import cvs files since I can view the data easily in excel and use perl or a text editor to manipulate (clean up) the data before importing.Other databases like PostgreSQL have a cmd line interface you can use that allows you to dump or load bulk data into the database. Some DB's allow you to do this right from the SQL statement, others have a standalone app.
    Ignorance more frequently begets confidence, than it does knowledge. [Charles Darwin]
    Physical laws are kinda like Pringles. It is hard to break just one law. [Dr. Rocket]
    Reply With Quote  
     

  15. #14  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    Quote Originally Posted by Darkhorse View Post
    MSSQL Server has a management console application that allows your to manage completely your database. You can create new databases, schema's, tables, add columns to tables, etc etc etc. One of the functions of the management tool is to import data easily, including only take a subset of the data. Usually I export/import cvs files since I can view the data easily in excel and use perl or a text editor to manipulate (clean up) the data before importing.Other databases like PostgreSQL have a cmd line interface you can use that allows you to dump or load bulk data into the database. Some DB's allow you to do this right from the SQL statement, others have a standalone app.
    I know about mssql server management but what I need it to do is read an entire directory on my hard drive and read the file tags associated with each file, and record the location of each file. i know it can be done because any karaoke hosting software I have ever used does this. I just don't know the code they used to facilitate it. I also want the database accessible for editing. but in the karaoke hosting software you cannot edit the fields. Something that i desperately need to do because I didn't tag these files. someone else did and they did a piss poor inconsistant job of doing so. htey have artists under title and title under artist, genre was completely ignored. and artist names go back and forth between first name, last name to last name, first name, and in some cases the title of hte track is simple a cd number and track number. its a horrible mess
    Reply With Quote  
     

  16. #15  
    Iuvenis ducis Darkhorse's Avatar
    Join Date
    Mar 2011
    Posts
    105
    You could do this as part of the application that will interface with your DB or you could do this as part of a clean up step before you import. My preference is always to import clean data to a database, but that is just a personal preference.

    Personally for a clean up job like this I would use Perl, but that is only because it is my goto language for quick and an dirty programs/scripts. I am sure there are other languages that you can use to search a directory path, read the tags and write a csv file from the data that can then be imported into a db. For Perl you would need the File::Find module and the MP3::Tag module. A quick search on PerlMonks - The Monastery Gates lists several scripts that people use to categorize mp3's and write or read tags from the files.

    It doesn't list anything however for dealings mp3-G files which it appears just karaoke produces, so if the files were saved as that your mileage may vary.
    Last edited by Darkhorse; February 3rd, 2013 at 02:55 AM. Reason: Formatting
    Ignorance more frequently begets confidence, than it does knowledge. [Charles Darwin]
    Physical laws are kinda like Pringles. It is hard to break just one law. [Dr. Rocket]
    Reply With Quote  
     

  17. #16  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    Quote Originally Posted by Darkhorse View Post
    You could do this as part of the application that will interface with your DB or you could do this as part of a clean up step before you import. My preference is always to import clean data to a database, but that is just a personal preference.

    Personally for a clean up job like this I would use Perl, but that is only because it is my goto language for quick and an dirty programs/scripts. I am sure there are other languages that you can use to search a directory path, read the tags and write a csv file from the data that can then be imported into a db. For Perl you would need the File::Find module and the MP3::Tag module. A quick search on PerlMonks - The Monastery Gates lists several scripts that people use to categorize mp3's and write or read tags from the files.

    It doesn't list anything however for dealings mp3-G files which it appears just karaoke produces, so if the files were saved as that your mileage may vary.
    Just karaoke actually reads zipped files. inside the zipped folder there are two files. one is a cdg file and the other is an mp3. apparently the hosting software plays both files simultaneously without unzipping the folder or extracting the files
    Reply With Quote  
     

  18. #17  
    WYSIWYG Moderator marnixR's Avatar
    Join Date
    Apr 2007
    Location
    Cardiff, Wales
    Posts
    5,760
    Quote Originally Posted by seagypsy View Post
    I know about mssql server management but what I need it to do is read an entire directory on my hard drive and read the file tags associated with each file, and record the location of each file.
    couldn't that be done by interrogating sysobjects in Visual Studio's server explorer, e.g. by writing a stored procedure to do the job for you ?
    "Reality is that which, when you stop believing in it, doesn't go away." (Philip K. Dick)
    Reply With Quote  
     

  19. #18  
    has lost interest seagypsy's Avatar
    Join Date
    Nov 2012
    Posts
    3,107
    ok i may have misspoke. I know of mssql and visual studio, but i am still a beginner in using them. I used visual studio 2010 to write and compile c++ console programs but never did anything too fancy. This set of classes has bumped us up to vs 2012 and introduced us to mssql server management studio


    I am hoping by the end of the semester that I will be able to answer my own questions. but mainly just want to know that if it will be possible using the software I am learning to use right now.
    Reply With Quote  
     

  20. #19  
    Iuvenis ducis Darkhorse's Avatar
    Join Date
    Mar 2011
    Posts
    105
    Yup perfectly possible. :-)You can get a list of files with IO.Directory.GetFiles. System.IO.Compression.ZipArchive will read and write zip files. Reading the mp3 tags will probably require a 3rd party library, the other two are part of .Net 4.5.
    Ignorance more frequently begets confidence, than it does knowledge. [Charles Darwin]
    Physical laws are kinda like Pringles. It is hard to break just one law. [Dr. Rocket]
    Reply With Quote  
     

  21. #20  
    New Member
    Join Date
    Feb 2013
    Posts
    2
    You may need to insert a large amount of data when first populating a database.
    Reply With Quote  
     

Similar Threads

  1. Database for listing one's papers
    By curlgrad in forum Physics
    Replies: 2
    Last Post: July 27th, 2011, 06:31 PM
  2. Automatic date in database
    By MichaelSoe in forum Computer Science
    Replies: 2
    Last Post: May 31st, 2011, 03:41 AM
  3. Biological classification database
    By gs99 in forum Biology
    Replies: 1
    Last Post: March 7th, 2011, 07:33 PM
  4. Database move
    By (In)Sanity in forum Site Feedback
    Replies: 2
    Last Post: October 27th, 2007, 11:11 AM
  5. QPCR Oligo Database of Pathogens
    By stanleygeorge in forum Biology
    Replies: 0
    Last Post: June 13th, 2007, 07:53 AM
Bookmarks
Bookmarks
Posting Permissions
  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •