Subtitles of the Movie

So speaking about importing data into Access, let's actually import a database. This is a flat file database. It has many, many records - 5461. And because it's a flat text file, it's very difficult to deal with. It's very unwieldy - it's hard to find things, it's hard to edit them. I am not entirely sure that it's a well formed database. I am sure that in here somewhere, there is errant data like improperly formatted dates, or the word color is missing on some of them, and it's just very difficult to deal with. So I would like to bring it into Access, I could sort on them better, and I can change the values of some of these fields to sort of shrink the file a little bit - like this is redundant. I know that this is the minute's column. I could take this out. Of course, in my existing Perl implementation of this site, you know I can just as easily know that I am pulling out the minutes and change that. But it's easier to do things in Cold Fusion and Access, so we will just, we'll give that a shot. Let's change some of the values in here in preparation for bringing it in. I am going to replace all instances of space min pipe (' min|'), as you could see this is a pipe delimited file, to just have the number of minutes show up by itself. So I am in text pad by the way, which is an incredible, incredible text editor. I highly, highly recommend it if you ever edit large text files, or any text files. It's got a number of capabilities. For instance, one of my favorites is being able to change the highlighter, the marquee, or the text marker not only from a line by line as it is here, but I could (you know) that's the normal selector. But if you hit control b, now you can also edit by columns. How cool is that? I can then grab this, I can do control c and grab this text and move it anywhere, I can grab this column and do things, and in fact while we are on the subject, I am going to, I am going to select this whole column and delete it. That was pretty easy. Right now I am going to do a search and replace, hit F8. I am going to change space min pipe (' min|') to just pipe ('|') and I will get rid of all that, replace all. Notice also I could save all documents as the scope. If I had many documents open, I could do this replace on all of them, and we are done. Now I am also going to do replace on color, and replace that with a one, I am going to make the color or black and white field the format be a yes/no and then pull that out later in Cold Fusion, if it's yes then it's color, if it's no then it's black and white. Save a little bit of space in the database, replace all. And then as well for black and white ('|B&W|'), I am going to make that equal to zero ('|0|'). Boom - pretty quick. So, I believe that it's been minimized and normalized to the extent that we can do here in the text editor. I am going to save it and now go into Access. By the way, you should register it if you ever use it. I have registered it, but just not on this machine. Import - let's go look for our file. It's a text file called films, and here is our database. It is delimited but it's not sure what the delimiter character is. Fixed width would mean you know this is one field, this is one field based on the column number, that's what fixed width. But I am going to do, because it is delimited, and I separate it by the pipe character, you notice now it recognizes all the different fields. I am going to put it into a new table, and I am now going to edit my fields here right now. This is the movie number. I am going to make sure that it's not a long integer, it is text. Because if I were to leave this field as long integer, it would remove the leading zeroes, and I need to have those in there. I could always replace them later, but I rather have this data not lost right now. And this is the title, and this is the length, which is ok to have as long integer. And this is whether or not it's color. I am going to declare this as a yes/no field. This is the year. I could leave it as a long integer, but I'd rather have it as text. This is the distributor, definitely text. This is the description, and way out over here is the category. So this is category. Now I am going to make my number indexed and no duplicates. I want to make sure that my database does not have any duplicates for the number, but I am not going to make this my primary key. I'd rather have a separate number for that. Next, here is my primary key. This way I can always add movies later if I want to, and I will have an auto numbering number there. So Access will do this for me and I will call it 'films' with lower case f - zoom, it's done. I had some errors. I am sure that's true. Only 108 - that's not bad out of 5000, how many did we have? If we go all the way down to the bottom, 5461 records. How many do we have in films? 5461. Not bad, so let's take a quick look at our data here to make sure that everything came in ok. I am going to go into design and color is a yes/no. I would like to set the format to be yes or no, and now color not true/false - yes/no. And there's the color yes/no. And let's sort on a couple of these columns, only do this a couple of times. Let's just make sure that they all have some data in here. If you sort it by, then you can group them all if anything fall out of the sort, yes or no, it will be at one end of the range. And it appears that all our movies do have the color information. Let's sort on the length. Ah, look at this, one right now. What's this movie? 'Sour death balls', that sounds like fun. But we don't know what the length of the movie is, so we are going to go look for it. Sour death balls in here - it's sorted alphabetically, so we should be able to find it pretty easily, almost there. Sour death balls - see, this is what the time was set at - it was 5 minutes, so I will set this to be 5 minutes. Let's check out the year, this will be the last one. 'Olympus, the mountain' does not have a year here. We will also check the other end, but let's take a look at 'Olympus, the mountain'. This will be the last one I do, Olympus, there we go. 'Olympus, the mountain of Gods' - so we don't know, we know that it's color, but we don't know what the year is, its totally blank. So I will put in, because this is a text field, I will put in 'unk', unknown. I'll just go down to the end here and see. Ah, look a whole batch of bad years, and it looks like the Bl here is supposed to be a part of Blackhawk, which I know is a distributor. So I will fix that, but you could see that bringing your data into access allows you to quickly normalize your data and have a better database. So we will use this later on in the site as well. could see that bringing your data into access allows you to quickly normalize your data and have a better database. So we will use this later on in the site as well.