Data scientists spend the majority of their time on preparing data for analysis. One of the first steps in this preparation phase is to load the data from the raw storage format. Comma-separated value (CSV) files are a popular format for tabular data due to their simplicity and ostensible ease of use. However, formatting standards for CSV files are not followed consistently, so each file requires manual inspection and potentially repair before the data can be loaded, an enormous waste of human effort for a task that should be one of the simplest parts of data science. The first and most essential step in retrieving data from CSV files is deciding on the dialect of the file, such as the cell delimiter and quote character. Existing dialect detection approaches are few and non-robust. In this paper, we propose a dialect detection method based on a novel measure of data consistency of parsed data files. Our method achieves 97 overall accuracy on a large corpus of real-world CSV files and improves the accuracy on messy CSV files by almost 22 compared to existing approaches, including those in the Python standard library. Our measure of data consistency is not specific to the data parsing problem, and has potential for more general applicability.
Other work related to CSV parsing is the work on DeExcelerator by @cite_0 . This work focuses mainly on spreadsheets in Excel format, but can also handle CSV files when the correct dialect is provided by the user. The DeExcelerator program then extracts tables from the spreadsheet and in the process performs header recognition, data type recognition, and value extrapolation, and other operations based on heuristic rules. In follow-up work, koci2016machine present a method for classifying the role of each cell in the spreadsheet (i.e. attribute, data, header, etc.) using surface-level features derived from the formatting of the text and classification methods such as decision trees and support vector machines. While the DeExcelerator package offers no method for detecting the dialect of CSV files, the methods for table and header detection are suitable to the general CSV parsing problem outlined above.
@cite_0
1974329121
Of the structured data published on the web, for instance as datasets on Open Data Platforms such as data.gov, but also in the form of HTML tables on the general web, only a small part is in a relational form. Instead the data is intermingled with formatting, layout and textual metadata, i.e., it is contained in partially structured documents. This makes transformation into a true relational form necessary, which is a precondition for most forms of data analysis and data integration. Studying data.gov as an example source for partially structured documents, we present a classification of typical normalization problems. We then present the DeExcelerator, which is a framework for extracting relations from partially structured documents such as spreadsheets and HTML tables.