Thread: how to populate a database.

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?

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.

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?

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.

Originally Posted by MacGyver1968

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

Originally Posted by John Galt

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.

Originally Posted by Harold14370

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.

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.

Originally Posted by seagypsy

Originally Posted by John Galt

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.

Originally Posted by seagypsy

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.

In Access, you can just run a find duplicates query.

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.

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.

Originally Posted by Darkhorse

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

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.

Originally Posted by Darkhorse

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

Originally Posted by seagypsy

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 ?

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.

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.

You may need to insert a large amount of data when first populating a database.