Advertisement

TabbyXL: Rule-Based Spreadsheet Data Extraction and Transformation

  • Alexey ShigarovEmail author
  • Vasiliy Khristyuk
  • Andrey Mikhailov
  • Viacheslav Paramonov
Conference paper
Part of the Communications in Computer and Information Science book series (CCIS, volume 1078)

Abstract

This paper presents an approach to rule-based spreadsheet data extraction and transformation. We determine a table object model and domain-specific language of table analysis and interpretation rules. In contrast to the existing data transformation languages, we draw up this process as consecutive steps: role analysis, structural analysis, and interpretation. To the best of our knowledge, there are no languages for expressing rules for transforming tabular data into the relational form in terms of the table understanding. We also consider a tool for transforming spreadsheet data from arbitrary to relational tables. The performance evaluation has been done automatically for both (role and structural) stages of table analysis with the prepared ground-truth data. It shows high F-score from 95.82% to 99.04% for different recovered items in the existing dataset of 200 arbitrary tables of the same genre (government statistics).

Keywords

Data extraction Data transformation Table analysis Rule-based programming Spreadsheet 

Notes

Acknowledgment

This work is supported by the Russian Science Foundation under Grant No.: 18-71-10001.

References

  1. 1.
    Astrakhantsev, N., Turdakov, D., Vassilieva, N.: Semi-automatic data extraction from tables. In: Selected Papers of the 15th All-Russian Scientific Conference on Digital Libraries: Advanced Methods and Technologies, Digital Collections, pp. 14–20 (2013)Google Scholar
  2. 2.
    Barik, T., Lubick, K., Smith, J., Slankas, J., Murphy-Hill, E.: Fuse: a reproducible, extendable, internet-scale corpus of spreadsheets. In: Proceedings of the 12th Working Conference on Mining Software Repositories, pp. 486–489. IEEE Press (2015).  https://doi.org/10.1109/MSR.2015.70
  3. 3.
    Barowy, D.W., Gulwani, S., Hart, T., Zorn, B.: FlashRelate: extracting relational data from semi-structured spreadsheets using examples. SIGPLAN Not. 50(6), 218–228 (2015).  https://doi.org/10.1145/2813885.2737952CrossRefGoogle Scholar
  4. 4.
    Cao, T.D., Manolescu, I., Tannier, X.: Extracting linked data from statistic spreadsheets. In: Proceedings of the International Workshop on Semantic Big Data, pp. 5:1–5:5 (2017).  https://doi.org/10.1145/3066911.3066914
  5. 5.
    Chen, Z.: Information extraction on para-relational data. Ph.D. thesis, University of Michigan, US (2016)Google Scholar
  6. 6.
    Chen, Z., Cafarella, M.: Automatic web spreadsheet data extraction. In: Proceedings of the 3rd International Workshop on Semantic Search Over the Web, pp. 1:1–1:8 (2013).  https://doi.org/10.1145/2509908.2509909
  7. 7.
    Chen, Z., et al.: Spreadsheet property detection with rule-assisted active learning. Technical report CSE-TR-601-16 (2016). https://www.cse.umich.edu/techreports/cse/2016/CSE-TR-601-16.pdf
  8. 8.
    Cunha, J., Erwig, M., Mendes, J., Saraiva, J.: Model inference for spreadsheets. Autom. Softw. Eng. 23(3), 361–392 (2016).  https://doi.org/10.1007/s10515-014-0167-xCrossRefGoogle Scholar
  9. 9.
    Cunha, J., Fernandes, J.P., Mendes, J., Saraiva, J.: Spreadsheet engineering. In: Zsók, V., Horváth, Z., Csató, L. (eds.) CEFP 2013. LNCS, vol. 8606, pp. 246–299. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-15940-9_6CrossRefGoogle Scholar
  10. 10.
    Cunha, J., Saraiva, J.a., Visser, J.: From spreadsheets to relational databases and back. In: Proceedings of the ACM SIGPLAN Workshop Partial Evaluation and Program Manipulation, pp. 179–188 (2009).  https://doi.org/10.1145/1480945.1480972
  11. 11.
    Dou, W., Xu, C., Cheung, S.C., Wei, J.: CACheck: detecting and repairing cell arrays in spreadsheets. IEEE Trans. Software Eng. 43(3), 226–251 (2017).  https://doi.org/10.1109/TSE.2016.2584059CrossRefGoogle Scholar
  12. 12.
    Eberius, J., Werner, C., Thiele, M., Braunschweig, K., Dannecker, L., Lehner, W.: DeExcelerator: a framework for extracting relational data from partially structured documents. In: Proceedings of the 22nd ACM International Conference on Information & Knowledge Management, pp. 2477–2480 (2013).  https://doi.org/10.1145/2505515.2508210. http://doi.acm.org/10.1145/2505515.2508210
  13. 13.
    Embley, D.W., Krishnamoorthy, M.S., Nagy, G., Seth, S.: Converting heterogeneous statistical tables on the web to searchable databases. IJDAR 19(2), 119–138 (2016).  https://doi.org/10.1007/s10032-016-0259-1CrossRefGoogle Scholar
  14. 14.
    Ermilov, I., Ngomo, A.-C.N.: TAIPAN: automatic property mapping for tabular data. In: Blomqvist, E., Ciancarini, P., Poggi, F., Vitali, F. (eds.) EKAW 2016. LNCS (LNAI), vol. 10024, pp. 163–179. Springer, Cham (2016).  https://doi.org/10.1007/978-3-319-49004-5_11CrossRefGoogle Scholar
  15. 15.
    Fiorelli, M., Lorenzetti, T., Pazienza, M.T., Stellato, A., Turbati, A.: Sheet2RDF: a flexible and dynamic spreadsheet import&lifting framework for RDF. In: Ali, M., Kwon, Y., Lee, C.H., Kim, J., Kim, Y. (eds.) IEA/AIE 2015. LNCS, vol. 9101, pp. 131–140. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-19066-2_13CrossRefGoogle Scholar
  16. 16.
    Galkin, M., Mouromtsev, D., Auer, S.: Identifying web tables: supporting a neglected type of content on the web. In: Klinov, P., Mouromtsev, D. (eds.) KESW 2015. CCIS, vol. 518, pp. 48–62. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-24543-0_4CrossRefGoogle Scholar
  17. 17.
    Gulwani, S., Harris, W.R., Singh, R.: Spreadsheet data manipulation using examples. Commun. ACM 55(8), 97–105 (2012).  https://doi.org/10.1145/2240236.2240260CrossRefGoogle Scholar
  18. 18.
    Han, L., Finin, T., Parr, C., Sachs, J., Joshi, A.: RDF123: from spreadsheets to RDF. In: Sheth, A., et al. (eds.) ISWC 2008. LNCS, vol. 5318, pp. 451–466. Springer, Heidelberg (2008).  https://doi.org/10.1007/978-3-540-88564-1_29CrossRefGoogle Scholar
  19. 19.
    Harris, W.R., Gulwani, S.: Spreadsheet table transformations from examples. SIGPLAN Not. 46(6), 317–328 (2011).  https://doi.org/10.1145/1993316.1993536CrossRefGoogle Scholar
  20. 20.
    Hung, V., Benatallah, B., Saint-Paul, R.: Spreadsheet-based complex data transformation. In: Proceedings of the 20th ACM International Conference on Information and Knowledge Management, pp. 1749–1754 (2011).  https://doi.org/10.1145/2063576.2063829
  21. 21.
    Hurst, M.: Layout and language: challenges for table understanding on the web. In: Proceedings of the 1st International Workshop on Web Document Analysis, pp. 27–30 (2001)Google Scholar
  22. 22.
    Jin, Z., Anderson, M.R., Cafarella, M., Jagadish, H.V.: Foofah: transforming data by example. In: Proceedings of the ACM International Conference on Management of Data, pp. 683–698 (2017).  https://doi.org/10.1145/3035918.3064034
  23. 23.
    Koci, E., Thiele, M., Lehner, W., Romero, O.: Table recognition in spreadsheets via a graph representation. In: 13th IAPR International Workshop on Document Analysis Systems, pp. 139–144 (2018).  https://doi.org/10.1109/DAS.2018.48
  24. 24.
    Koci, E., Thiele, M., Romero, O., Lehner, W.: A machine learning approach for layout inference in spreadsheets. In: Proceedings of the 8th International Joint Conference on Knowledge Discovery, Knowledge Engineering and Knowledge Management, pp. 77–88 (2016).  https://doi.org/10.5220/0006052200770088
  25. 25.
    Koci, E., Thiele, M., Romero, O., Lehner, W.: Table identification and reconstruction in spreadsheets. In: Dubois, E., Pohl, K. (eds.) CAiSE 2017. LNCS, vol. 10253, pp. 527–541. Springer, Cham (2017).  https://doi.org/10.1007/978-3-319-59536-8_33CrossRefGoogle Scholar
  26. 26.
    Kolb, S., Paramonov, S., Guns, T., De Raedt, L.: Learning constraints in spreadsheets and tabular data. Mach. Learn. 106(9), 1441–1468 (2017).  https://doi.org/10.1007/s10994-017-5640-xMathSciNetCrossRefzbMATHGoogle Scholar
  27. 27.
    Langegger, A., Wöß, W.: XLWrap – querying and integrating arbitrary spreadsheets with SPARQL. In: Bernstein, A., Karger, D.R., Heath, T., Feigenbaum, L., Maynard, D., Motta, E., Thirunarayan, K. (eds.) ISWC 2009. LNCS, vol. 5823, pp. 359–374. Springer, Heidelberg (2009).  https://doi.org/10.1007/978-3-642-04930-9_23CrossRefGoogle Scholar
  28. 28.
    Mitlöhner, J., Neumaier, S., Umbrich, J., Polleres, A.: Characteristics of open data CSV files. In: 2nd International Conference on Open and Big Data, pp. 72–79 (2016).  https://doi.org/10.1109/OBD.2016.18
  29. 29.
    Mulwad, V., Finin, T., Joshi, A.: A domain independent framework for extracting linked semantic data from tables. In: Ceri, S., Brambilla, M. (eds.) Search Computing. LNCS, vol. 7538, pp. 16–33. Springer, Heidelberg (2012).  https://doi.org/10.1007/978-3-642-34213-4_2CrossRefGoogle Scholar
  30. 30.
    Nagy, G.: TANGO-DocLab web tables from international statistical sites (Troy\(\_\)200), 1, ID: Troy\(\_\)200\(\_\)1 (2016). http://tc11.cvc.uab.es/datasets/Troy_200_1
  31. 31.
    O’Connor, M.J., Halaschek-Wiener, C., Musen, M.A.: Mapping master: a flexible approach for mapping spreadsheets to OWL. In: Patel-Schneider, P.F., et al. (eds.) ISWC 2010. LNCS, vol. 6497, pp. 194–208. Springer, Heidelberg (2010).  https://doi.org/10.1007/978-3-642-17749-1_13CrossRefGoogle Scholar
  32. 32.
    Shigarov, A., Altaev, A., Mikhailov, A., Paramonov, V., Cherkashin, E.: TabbyPDF: web-based system for PDF table extraction. In: Damaševičius, R., Vasiljevienė, G. (eds.) ICIST 2018. CCIS, vol. 920, pp. 257–269. Springer, Cham (2018).  https://doi.org/10.1007/978-3-319-99972-2_20CrossRefGoogle Scholar
  33. 33.
    Shigarov, A.: Rule-based table analysis and interpretation. In: Dregvaite, G., Damasevicius, R. (eds.) ICIST 2015. CCIS, vol. 538, pp. 175–186. Springer, Cham (2015).  https://doi.org/10.1007/978-3-319-24770-0_16CrossRefGoogle Scholar
  34. 34.
    Shigarov, A.: Table understanding using a rule engine. Expert Syst. Appl. 42(2), 929–937 (2015).  https://doi.org/10.1016/j.eswa.2014.08.045CrossRefGoogle Scholar
  35. 35.
    Shigarov, A., Khristyuk, V.: TabbyXL2: experiment data. Mendeley Data, v2 (2018).  https://doi.org/10.17632/ydcr7mcrtp.2
  36. 36.
    Shigarov, A., Mikhailov, A., Altaev, A.: Configurable table structure recognition in untagged PDF documents. In: Proceedings of the ACM Symposium on Document Engineering, pp. 119–122 (2016).  https://doi.org/10.1145/2960811.2967152
  37. 37.
    Shigarov, A.O., Paramonov, V.V., Belykh, P.V., Bondarev, A.I.: Rule-based canonicalization of arbitrary tables in spreadsheets. In: Dregvaite, G., Damasevicius, R. (eds.) ICIST 2016. CCIS, vol. 639, pp. 78–91. Springer, Cham (2016).  https://doi.org/10.1007/978-3-319-46254-7_7CrossRefGoogle Scholar
  38. 38.
    Shigarov, A.O., Mikhailov, A.A.: Rule-based spreadsheet data transformation from arbitrary to relational tables. Inf. Syst. 71, 123–136 (2017).  https://doi.org/10.1016/j.is.2017.08.004CrossRefGoogle Scholar
  39. 39.
    de Vos, M., Wielemaker, J., Rijgersberg, H., Schreiber, G., Wielinga, B., Top, J.: Combining information on structure and content to automatically annotate natural science spreadsheets. Int. J. Hum. Comput. Stud. 103, 63–76 (2017).  https://doi.org/10.1016/j.ijhcs.2017.02.006CrossRefGoogle Scholar
  40. 40.
    Wang, X.: Tabular abstraction, editing, and formatting. Ph.D. thesis, University of Waterloo, Waterloo, Ontario, Canada (1996)Google Scholar
  41. 41.
    Yang, S., Guo, J., Wei, R.: Semantic interoperability with heterogeneous information systems on the internet through automatic tabular document exchange. Inf. Syst. 69, 195–217 (2017).  https://doi.org/10.1016/j.is.2016.10.010CrossRefGoogle Scholar
  42. 42.
    Yang, S., Wei, R., Shigarov, A.: Semantic interoperability for electronic business through a novel cross-context semantic document exchange approach. In: Proceedings of the ACM Symposium on Document Engineering, pp. 28:1–28:10 (2018).  https://doi.org/10.1145/3209280.3209523

Copyright information

© Springer Nature Switzerland AG 2019

Authors and Affiliations

  • Alexey Shigarov
    • 1
    • 2
    Email author
  • Vasiliy Khristyuk
    • 1
  • Andrey Mikhailov
    • 1
  • Viacheslav Paramonov
    • 1
    • 2
  1. 1.Matrosov Institute for System Dynamics and Control Theory of SB RASIrkutskRussia
  2. 2.Institute of Mathematics, Economics and InformaticsIrkutsk State UniversityIrkutskRussia

Personalised recommendations