EnvLog
What is it? : EnvLog is a lightweight web application that allows users to log environmental data by scanning a location name (using a QR code) or selecting a location from a menu.
I always want to learn more in my life and have many ideas to do more with the technology we have at our disposal today. I had the idea to make it easy to enter data on my phone and then store it in an online MYSQL database and then load the data into an Excel file with Power Query. You only must make the connection once with Power Query and then can refresh the data every time you want to get the latest data. So, entering the data on your phone and after that processing the data behind your desk on a laptop or desktop computer in the Excel file.
Entering on a phone is much easier in my situation because I work in horticulture and walk between the crop when I want to store for example length measurements.
I am grateful to Will Woodgate for helping me to develop my idea, his idea to make it possible to work with QR codes is also very useful. The horticultural company where I work has several separated cultivation areas so that you can hang a QR code in each cultivation area and quickly fill in the details of the relevant cultivation area after scanning. Of course, you can also use the 'Choose location' drop-down menu to choose the location.
You find the project that Will created on this GitHub page: https://github.com/willwood/EnvLog
I always want to learn more in my life and have many ideas to do more with the technology we have at our disposal today. I had the idea to make it easy to enter data on my phone and then store it in an online MYSQL database and then load the data into an Excel file with Power Query. You only must make the connection once with Power Query and then can refresh the data every time you want to get the latest data. So, entering the data on your phone and after that processing the data behind your desk on a laptop or desktop computer in the Excel file.
Entering on a phone is much easier in my situation because I work in horticulture and walk between the crop when I want to store for example length measurements.
I am grateful to Will Woodgate for helping me to develop my idea, his idea to make it possible to work with QR codes is also very useful. The horticultural company where I work has several separated cultivation areas so that you can hang a QR code in each cultivation area and quickly fill in the details of the relevant cultivation area after scanning. Of course, you can also use the 'Choose location' drop-down menu to choose the location.
You find the project that Will created on this GitHub page: https://github.com/willwood/EnvLog
If you select or scan for example cultivation area 'Vak 1' with your phone, you will see this on your phone screen and be able to fill in some information. And when you are ready you can press the ' Add Record' button to save the data into the My SQL database. Note: I changed the html in the index.php file to see the fields I want.
Make connection to the Database in Excel for Windows
Note: below this section about Win Excel I show you how to do it in Mac Excel. Open Excel for Windows and on the Data tab press the From Web button and this dialog will popup, enter the path to the export_csv.php file in this dialog and press OK. If prompted, connect using 'Anonymous' and click 'Connect'.
This dialog will popup with a few data rows of the database, press the Transform Data button now:
This dialog will popup and you have the options to change the things you want before you load the data into Excel, There is a lot possible with Power Query in Excel, there is a lot of information on the internet if you want to know more. In this example we do not change anything and press the Close & Load button on the top left now:
You now see the data of the database in Excel in a Table and the connection is created to your database. You can use the Refresh or the Refresh All buttons on the Table Design or Data tab or right click in the table and choose Refresh. Clicking on the Filter button will toggle the view of the filter arrows in the Table. You see in the screenshot that I also add the Refresh All button to my QAT bar so it is easy to press it.
You can now Save this workbook and add columns to the table with calculations and make other calculations based on the data in this table. For example if you enter in cell G2 this formula and press enter the whole table will get this formula : =ISOWEEKNUM([@Date])
And if you Refresh the data the new rows also have this formula to display the ISO Week number. Only change the header in G1 to 'Iso Week'. Tables in Excel are very powerfull, there is a lot of information on the internet to get you started.
Make connection to the Database in Mac Excel
The latest version of Office is using the same name in Windows and on the Mac(Microsoft 365) but the Mac version not have the same options as the Windows version, you pay the same but get less. The things I show you above are not working on your Mac, one option is to make the connection in Excel for Windows and copy the Excel file to your Mac or try the way I show you below.
Open Excel and click on Data in the Ribbon and then click on the first button named 'Get Data (Power Query)' and click on 'Blank Query'. Then replace the four lines of script that you see with the script below, be sure you have the correct path in the script. Then click on Next and then Close & Load on the top left in the Ribbon.