The Automating Hydroclimatic Data Processing: A VBA Module for Efficient Handling of Large Datasets in Microsoft Access

Gláucio Ferreira  Loureiro; Carlos Rogério de Mello

Gláucio Ferreira Loureiro Universidade Federal dos Vales do Jequitinhonha e Mucuri (UFVJM) https://orcid.org/0009-0009-9563-2097
Carlos Rogério de Mello Universidade Federal de Lavras (UFLA) https://orcid.org/0000-0002-6033-5342

Resumo

Hydroclimatic data plays a crucial role in the study of water resources and the surrounding environment. These data are typically obtained from the National Water Agency and the state agencies. However, limited data series are available, and the data may contain undesirable characteristics. Data processing is usually performed using modules developed in spreadsheets; however, this method is impractical when dealing with large volumes of data. This led to the development of a Visual Basic for Applications (VBA) module in Microsoft Office Access to handle the extensive datasets. The application was designed to automatically process empty fields, clean invalid characters, and group data when large gaps appeared at the end of some tables. Finally, it calculated the monthly, daily, and annual averages. The application has proven to be highly intuitive because users only need to follow the sequence of buttons displayed on the screen.

Referências

Almeida, C.N., 2006. Personalização de Planilhas Excel® para Programação de Modelos – O Caso da Simulação da Operação de Reservatórios. In: Simpósio de Recursos Hídricos Do Nordeste, 7. João Pessoa: Universidade Federal da Paraíba.

Aragão, R., Cruz, M.A.S., Amorim, J.R.A., Mendonça, L.C., Figueiredo, E.E. and Srinivasan, V.S., 2013. Análise de sensibilidade dos parâmetros do modelo SWAT e simulação dos processos hidrossedimentológicos em uma bacia no agreste nordestino. Revista Brasileira de Ciência do Solo, 37(4), pp. 1091-1102. https://doi.org/10.1590/S0100-06832013000400026

Billah, M.M., Goodall, J.L., Narayan, U., Essawy, B.T., Lakshmi, V., Rajasekar, A. and Moore, R.W., 2016. Using a data grid to automate data preparation pipelines required for regional-scale hydrologic modeling. Environmental Modelling & Software, 78, pp. 31-39. https://doi.org/10.1016/j.envsoft.2015.12.010

Carleton, C.J., Dahlgren, R.A. and Tate, K.W., 2005. A relational database for the monitoringand analysis of watershed hydrologic functions: I. Database design and pertinent queries. Computers & Geosciences, 31(4), pp. 393-402. https://doi.org/10.1016/j.cageo.2004.10.007

Cirilo, J.A, Baltar, A.M., Rolim Junior, A.L., Torres Filho, C.O. and Azevedo, J.R.G., 1997. Processamento integrado de dados para análise hidrológica. Revista Brasileira de Recursos Hídricos, 2(2), pp. 21-44. http://dx.doi.org/10.21168/rbrh.v2n1.p15-28

Gan, T. Tarboton, D.G., Dash, P., Gichamo, T.Z. and Horsburgh, J.S., 2020. Integrating hydrologic modeling web services with online data sharing to prepare, store, and execute hydrologic models. Environmental Modelling & Software, 130, pp. 104731. https://doi.org/10.1016/j.envsoft.2020.104731

Kalteh, A.M. and Hjorth, P., 2009. Imputation of Missing values in precipitation-runoff process database. Journal of Hydrology Research, 40(4), pp. 420-432. https://doi.org/10.2166/nh.2009.001

Paiva, J.B.D. and Paiva, E.C.D. Hidrologia aplicada à gestão de pequenas bacias – Porto Alegre: ABRH, 2001.

Silberschatz, A., Korth, H.F. and Sudarshan, S., 2006. Sistema de Banco de Dados. Rio de Janeiro: Elsevier, 2006.

Tucci, C.E.M. Hidrologia: ciência e aplicação – Porto Alegre: Ed. Universidade/UFRGS: ABRH, 2001.