LibreOffice Base Split Database Tutorial

in a previous video we analyzed rainfall data 104 000 odd rows using excel and the advanced filter what we're now going to do is perform the same operation using a libreoffice space split database i have some videos earlier on where i've spoken about split databases check the description notes below for links to those videos step one if you've never created a spit database before is go to mediafire.net and download the split database wizard i've already downloaded it here so what i'm going to now do is launch it i've created a folder for this database called rainfall i've placed the split database into it and i've placed an excel file containing these 104 916 rows of data into it what we can do is we can either open this excel file using libreoffice cal or microsoft excel and then create a text file csv from it i'm going to do it with both just to show you how to do that but let's first of all now create our split database so i'm going to double click on the wizard that was downloaded earlier and when we do that the database opens and you get this dialog box it's basically telling me that i can name the sub database of the split database anything i want and you can just go with the default mydb which for simplicity i'm going to do now and it's just telling me a new database has been created and you can see the driver folder has been created and so this is our database so our split database has been created but first of all we've got to do some work on the excel file to make it ready to go into our libreoffice space database and that means converting it to a text csv file which the libreoffice base database will then link to let's do that now so first of all i'm going to open the file in excel so here's the text file open in excel what we're going to do is we're going to save this as a csv file given that libreoffice space will be connecting to it i don't want complicated column names which are going to be difficult to type so i'm just going to call rain rate just rate i'm going to call this daily then weekly then monthly then yearly because we know what it stands for now let's uh i'm going to call this field simply id and what we're going to do now is we're going to save this as a text file so when i click save as i can choose the data file type now in this instance when we're linking to libreoffice space it's going to want a csv and we're going to use the format utf hyphen 8 and you can see it's available within excel's drop down you also have a csv comma delimited file which doesn't say utf-8 just go with utf-8 and i'm just going to call this 2019 xl that's csv and let's make sure we've got it in the right place rainfall so as you can see the message here is telling us that it's only saving the active sheet that's fine that's all i want and done now if you don't happen to have excel and you only have libreoffice well let's do it in libreoffice now so next file open will open the excel spreadsheet in libreoffice cal let's just change the column headers now we do file save as and we choose csv and then we click i just call this 2019 csv cal use text csv format and then it's suggesting unicode utf-8 again and if you click the drop down you see you've got a lot of other choices as well but we're going to go with unicode utf-8 and it gives us the same warning only the active sheet so the next step in the pr of the process within base is that we create a text table to link against this csv file so the way we do that is we click on tools sql to get our sql command window up then i type create text table table rain live and i'm just going to put in a number of quotes to make this a lot easier to type and i want id and its data type is going to be int for integer time and its data type would be varchar20 rate flows daily data type will be flows weekly data type again is flows which is equivalent to double or in other words it's not an integer that has decimal places monthly again data type float yearly and the data type again is float and then we finish it off by closing the parenthesis and now we execute the sql and command successfully executed so if we close the window we can then you can see that at the moment no table is showing within the database but that's because we haven't refreshed it click view refresh tables and the table appears and if we double click on the table you will notice that there's no data here yet and that's because the table needs to be linked or associated with the text file so we can do that now if i go to tools sql again then i put set table open quotes table rain live which is the one we've just created and i've got to say source equals and this is going to be the database table source so set table source equals and let's just double check the source name it's that i'm just going to copy it to a control c source equals open quotes file name semicolon ignore first equals true underscore first equals true and encoding equals utf hyphen 8 and those quotes and if we execute that it gives me unexpected token and that's because we're not supposed to put an equals it's just source space and execute command successfully executed so hit close and now if i double click on table rain live it will now be connected to this text file and there you go now the next stage of this process is i want to isolate the year month day from the time because what we want to do is we want for each day we want the last or the latest time that a data point has been recorded so what that means is we've got over 104 thousand rows but for the year-long data but what we want is just 365 rows for the year that being one data point one record recorded for each day and we get that by getting the last recorded record and every day which is going to be something like four minutes to midnight so what we're going to do is i'm going to create a static table with another field just for the day and another field that holds time and then we can do an sql query on that just to get one vote per day being the day with the highest time let's create the table now so here we go i'm going to click create table in design view and let's build out this table so what i'm going to do is i'm going to be lazy and i'm not going to put in the id field initially i'm going to let the system prompt me to put that in so what i'm going to want is time and that's going to be text varchar and i'm going to take the rate and that's going to be text file again then daily weekly monthly and yearly i'm going to change these data types to double and i also want ymd which is going to be an integer which is your month day integer and i'm also going to want new time which is going to be time and if i save this and close table has been changed yes so that it's going to be table rainfall and yes i'm going to want a primary key and if i go into table rainfall now you'll see it's created a primary key for me if i right click on it and do edit you'll see the primary key key put against the field so the next step that i want to do is i want to populate table rainfall from table rainfall live so in order to do that i click on tools sql get the sql command window again and i'm just going to paste in this sql and what it says is insert into table rainfall the field names id time rate daily weekly monthly yearly and the data we want to insert you get it from this select statement which is select id time year rate daily weekly monthly yearly from table rainfall underscore live of course the thing about doing multiple takes here is in order to keep it exciting for you guys we sometimes make mistakes between takes and in this instance i've made two mistakes on the sql first of all we're selecting from table rain live as opposed to rainfall live so let's fix that first so let's lose the fall out of the rainfall and secondly i have a typo so i'm selecting from monte yeah monte it should be monthly but i actually made the mistake and typoed it to monty so let's see now how things work so if i do execute and command successfully executed so that should mean that we now have these data records loaded into an actual base table as opposed to linking to the text file reason i'm doing that is for speed of execution when i'm processing this data so let's uh double check it now so i'm going to close the libreoffice base table and open it and yes we now have data inside the database in addition to having it linked in the text file so we're going to get the year month and day from the time column and put it into the ymd column giving us a single day and trouble with the time column is each of these records is different we want to be able to isolate the day so let's do that now so tools sql paste in the sql so let me talk you through this update table rainfall then set ymd which is this column equals to concat now i've nested a concat for concatenating we use this to stick to fields together the reason is the um i've got a concat nested here well let me just take it through it from the inside concat substring time seven four so so seven four is position seven and then four characters is going to give us the year then i'm going to substring time for two so that's going to give us the month so the month is in position for going for two characters the left two characters gives us the day so in this instance it would be 31. the thing is the documentation says concat can concatenate multiple elements together however in practice it will only concat two items together so what i had to do first of all was concatenate these two items together and then do a separate concat where i took the result of the first two concats and then concatenated it with time to which is the date so documentation says multiple concatenations reality says you can have two items concatenated so let's just execute that now and i love it when it pauses because it means it's working i'm easily pleased so if i just close this close this table and open it up again you should see that the ymd column has been updated and there you go the ymd column has in fact been updated now it's time to populate the time into the new time column this is because we want to get the five minute or four minute to midnight time so we can isolate those 366 days from the 104 000 odd days so let's do that now tools sql i'm going to paste in the sql again so update table rainfall set new time equals to equals concat and just to make it easier for you to read concat substring time 12 15. so what we're going to be grabbing is we're grabbing the these characters here because new time has a libreoffice time format for it to work we've also got to concat these zero zero seconds into it otherwise the time format it won't work the time format is very precise as to what it wants but uh this is how you do it and it took me quite a while to find out how to do this but concat substring your time and you gotta add in the seconds as well colon seconds let's execute that love it when a plan comes together close and let's just close that you want to save changes no i do not want to save changes but i do want to see the new time and so there we go if i highlight this row 49 minutes and this is 134 so there's 134 and everything's got zero seconds so let's paste in some sql what i'm saying here is select year month day which is that column layer and max new time so the maximum time for any given day and we're going to call the column time from table rainfall and group it by year month day which means we're only going to get the maximum time for each of maximum time for each of these days let's run this first and just see what we get so there we go you're getting for each day you're getting the maximum time which happens to be happening at 23.59 now what we're going to do is we're going to do a join so we're going to join this we're going to now get all the columns from this table and the way we're going to do it is we're going to join this table on itself so what we're effectively going to say is with this particular table here only give me the rows that are in this particular query here sounds good now the way we're going to do that is we're going to put juventus's around this particular query we're going to effectively make this query a virtual table okay and i'm going to call this table s so what i'm going to do now is i'm going to say select r dot star and wait for r is going to be the other table r is going to represent this big table on the right select all that star from this virtual table which is s inner join table rainfall and give me my quotes and what are we joining table rainfall on s dot year month day so it's kind of like a look up again like a vlookup if you're familiar with that from excel s dot y m d is equal to r dot y and d and s dot time again in quotes is equal to r dot new time and new time has to be in quotes and let us run this query and fingers crossed they say never do maths live or in front of people well never do sql queries live and in front of people but it might just work so looking is still doing so the fact that it's taking so long is actually getting a bit worrying man this is taking time but it's got a lot of work to do and there we go it's retrieved 365 records so what we're going to do now is i'm going to write these 365 records into another table that we can in turn re-export out of the database into a spreadsheet if you want it now the beauty of this particular method is besides the fact that you can write some simple macros to execute this sql the beauty of it is you have a system set up that has less moving parts than shall we say a vba advanced filter to do this so the trick is really to have a number of different ways to crack the same nut as it were okay so let's create the output table now so i'm going to do tools sql i'm going to use the field names that i saved earlier so create text table and it's going to be table rain underscore out and the fields are i'm just going to copy and paste these in what we're also going to want to need is ymd and that's going to be an integer and we also want new time which should be wrapped in quotes and that's going to be a time data type and we close the parenthesis mr quote here and now let's click execute and the command successfully executed and if we do view refresh tables table rain out exists now all that remains is to populate the table okay so i'm going to populate the rain out table now so first of all before we do that we need to switch it on basically so i will do set table table rain underscore out and i'm going to say source equals rain house dot csv semicolon encoding equals utf hyphen 8 execute and when i've executed that we see that the table appears but there's no data there yet so now if i get the sql we created earlier and i put it into here tools sql and i paste it in and then i execute and we let it do its thing have a little thing for a while and command successfully executed so if i hit close on that and i double click on laying out you'll see the data we've got if i just go right down to the end we now have 365 rows of data but even more so if i close that and i click on layout.csv the text file and i could have to open it as read-only in excel but we've created a text file that can be read by another program be it notepad calc or excel or anything that can read csv files so if i open it edit it with a notepad plus plus you can see that it is a comma separated text file and that's it we have converted rainfall data using libreoffice space thanks for watching

Channel: Sean Johnson

Views: 838

Rating: 5 out of 5

Keywords: libreoffice base, database tutorial, open office database tutorial, libreoffice base split database, open office, libreoffice, libreoffice database, free database, libreoffice base text table, Microsoft Access alternative, tutorial, database, libreoffice base tutorial, libreoffice base create database, openoffice base, libre office, base, libreoffice database examples, libreoffice database tutorial, open office base, open office base tutorial, base split database, free data base

Id: BwrWoP0Wr7w

Length: 18min 7sec (1087 seconds)

Published: Tue Apr 06 2021