FR2944895A1 - METHOD FOR THE HEURISTIC ANALYSIS OF A FILE AND COMPUTER PROGRAM PRODUCT FOR IMPLEMENTING SUCH A METHOD - Google Patents

Abstract

Method for heuristically analyzing a file representative of a data table in order to reconstitute said table, the file being structured in lines of characters comprising column separator characters, characterized in that it comprises the search for a character whose number of occurrences is the same for all lines; said character being chosen as a column separator, so as to determine a format dependent array structure. A computer program product comprising program code instructions for carrying out such a method.

Description

The invention relates to a heuristic analysis method of a file representative of a data table, in order to provide a method for heuristically analyzing a file representative of a table of data, in order to reconstructing said array, as well as a computer program product comprising program code instructions for carrying out such a method. The invention applies in particular to the interpretation of CSV type files (comma separated values, ie values separated by comma). The CSV format is an open computer format for representing data in the form of a table. It is a text format, structured in lines of characters separated by a carriage return (CRLF character). Each line is subdivided into the same number of columns by a predefined character, called column separator. This gives a one-dimensional representation of a two-dimensional array. The column separator can be treated as an ordinary character as long as it appears inside a group of characters enclosed in double quotation marks.

This format is very used, because of its simplicity. However, its use is made difficult by the fact that it is only imperfectly standardized. Unlike the line separator (CRLF character), the column separator is not standardized. Sometimes this is the comma - hence the name of the format - but this choice is not very happy, because the comma is also used as decimal separator (or thousands, in the Anglo-Saxon countries), in addresses, etc. Therefore other characters (semicolon, tab, dash, slash ...) are commonly used as column separators. It follows that the user wishing to import a CSV type file into a spreadsheet must manually choose the separator character of the columns. This task is tedious, but its automation has not been possible to date.

The invention aims to solve this problem. In a preferred embodiment, the invention also makes it possible to automatically identify a file data format. By format we mean here in particular a regional format. Indeed some data such as dates or numbers are written differently in different countries: thus, in many countries the comma is the decimal separator and the point the optional separator thousands, while in Anglo countries -should the reciprocal be true; similarly, dates are written as day / month / year in Europe (United Kingdom included) and month / day / year in the United States, and so on. The automatic identification of the regional format of the so-called unique file allows the interpretation of the data contained in the signifying groups. An idea underlying this preferred embodiment of the invention is that the problems of identifying the structure of the table and its regional format should not be solved separately, but in a synergistic way, which allows to find a heuristic solution particularly powerful. It is important to underline the heuristic nature of the process of the invention: it is not possible to guarantee that the table will be reconstituted exactly, but in fact it happens in the vast majority of cases, except when uses files that are too small or pathological. In the event of failure, of course, it remains possible to use manual identification of the separator characters and / or the regional format. An object of the invention is therefore a heuristic analysis method of a file representative of a data table in order to reconstitute said table, the file being structured in lines of characters comprising column separator characters, characterized in that it includes the search for a character, with the exception of possible characters enclosed by double quotation marks, whose number of occurrences is the same for all lines; said character being chosen as a column separator so as to determine an array structure. According to a preferred embodiment of the invention, the method may comprise: a. identifying, within each line, groups of characters considered significant, at least some of the characters not belonging to such groups being considered as potential column separators; this identification being performed in parallel for several predefined data formats; b. for each data format, searching for a character, among said potential column separators, whose number of occurrences is the same for all the lines; said character being chosen as an effective column separator, so as to determine a format dependent array structure; and c. the application of a selection criterion to choose a data format and the corresponding column separator as the most likely. According to particular variants of the process of the invention: step c. may include the elimination of the predefined format or formats that do not identify an effective column separator. - Step a. may include the use of a plurality of character group identification strategies considered significant for each predefined data format. - Step c. may include the selection of the format or the data format / identification strategy combination of character groups leading to the identification of the largest number of characters considered significant. - The steps a. and B. may be implemented jointly, and include: identifying, within each line, said groupings of characters considered significant; 4 a2. counting occurrences of potential column separators in said line; and a3. except for the first line analyzed, the deletion of potential column separators whose number of occurrences is different from that of the previous line. - The method may also include the identification of predefined types of said character groups considered significant. The method may also include identifying the first row of the array as a header line if all other rows of the array have a common structure, the structure of said first row being different. The method may also include identifying the first column of the array as a column of headers if all the other columns of the array have a common structure, the structure of said first column being different. Another object of the invention is a computer program product comprising program code instructions for carrying out such a method. Other characteristics, details and advantages of the invention will emerge on reading the description made with reference to FIGS. 1, 2, 3 and 4, which illustrate an example of implementation of the method of the invention. The invention will be described with reference to particular cases in which the file to be analyzed is of CSV type and may contain text, possibly decimal numbers and dates. We consider three possible data formats, or parameters, corresponding to distinct geographical regions: a French format, in which the comma serves as a decimal separator, the point as an optional separator of thousands, and the dates are written in the following manner : day (an integer between 1 and 31) / month (an integer between 1 and 12) / year (a four-digit integer, not beginning with zero); - an American format, in which the point serves as a decimal separator, the comma as an optional separator for thousands, and the dates are written in the following way: month (integer between 1 and 12) / day (integer between 1 and 31) ) / year (four-digit integer, not starting with zero); and - a British format in which the point is used as a decimal separator, the comma as an optional separator for thousands and the dates are written in the following way: day (integer between 1 and 31) / month (integer between 1 and 12) ) / year (four-digit integer, not starting with zero). It is clear that this is a simplification: in reality, the dates can be written in several different ways, for example with the name of the month written in letters, in whole or in a shortened form. In addition, other types of data may be used, such as sums of money (integer or decimal followed or preceded by the name or symbol of the currency), addresses, e-mail addresses, phone, percentages, etc.

The character set used by the file is assumed to be known. There are also many techniques to determine it automatically. We also make the following assumptions, which are not very limiting, because we can assume that they are satisfied by any reasonable CSV file: - the character used to separate the columns exists (there are therefore at least two columns where this hypothesis will however be released later) and it is unique over the entire length of the file; The double quotation mark is a special character and can not be a column separator.All characters between two double quotation marks are treated literally and can not be straddling between two columns, even if the character string contains the double quotation mark. Column separation character - The number of columns is constant for each line of the file - The format parameters (regional settings) can not vary within the same file Some formats of numbers and dates are only valid for certain locales - The first line may be different from the following lo lines if it matches the column headers - Similarly, the first column may be different in nature from the other columns if it matches The method according to the invention aims at identifying the separator character and, consequently, at determining the number Optionally, such a method can also determine: - the geographical format of the data (limited in the examples to the three aforementioned possibilities: French, American, English); if the first row and / or the first column represent headers; - the type (number, date, text) and, if applicable, the value of each data. As mentioned above, the preferred embodiment of the invention is based on the simultaneous determination of the column separator character (and thus the number of columns) and the format of the data - a format that does not necessarily have a geographic meaning. as in the examples that follow. This idea can be exposed with the help of some very simple examples. We can begin by considering the following file, consisting of two lines: 1,234,567 1,234 taken in isolation, these two lines can each be interpreted in two ways: comma as separator of thousands or comma as separator of columns. It is clear that the comma can not be a decimal separator, because in this case the first line would have an irregular format. On the other hand, a single interpretation makes it possible to maintain the number of columns constant: comma as separator of thousands, a single column. Conversely, in the following file: 10 1,234,567 1,23,456 The comma is not a valid thousands separator on the second line, which suggests to interpret it as a column separator (including, retroactively, for the first line, where both options were possible). If you stick to the constant number of columns rule only, the period, decimal, and slash characters are all potential column separators in the following file: 20 1.25,13 / 10 / 2008.9.99 10.5,5 / 5 / 2008,200.5 The first line of the file can therefore be decomposed as follows: 1 ù 25,13 / 10 / 2008,9 ù 99 (If the point is the column separator); or 1.25 ù 13/10/2008 ù 9.99 (If the decimal point is the column separator); or 1.25,13 ù 10 ù 2008,9.99 (If the slash is the column separator). Thus, it can be seen that simply counting the instances of the characters in the different lines of the file is sometimes insufficient to reliably identify the column separator. In addition, this approach provides no indication of the format of the data. It is at this point that the notion of a grouping of characters signifying, or considered as such, comes into play. A signifying group is any grouping of characters that is likely, because of its form, to represent something other than text: a number, a date, a telephone number, and so on. A grouping of characters is not significant in itself, but only in the context of a data format.

For example, 1.25 is a grouping that can be considered as meaning in English or American format: it represents a decimal number. On the other hand, it is not significant (it only represents text) in French format. Similarly, 13/10/2008 is meaning (a date) in French or English format, but not in American format. The method according to the preferred embodiment of the invention comprises identifying, within each line, groups of potentially significant characters, and this for each of the predefined formats. Thus, in French format, the first line of the file can be broken down as follows: 1 (number) ù .25,13 / 10 / 2008,9. (text) 99 (number); or again: 1 (number) ù. (text) ù 25,13 (number) ù / 25 (text) ù 10 (number) ù / (text) ù 2008 (number) ù, (text) ù 9 (number) ù. (text) 99 (number); or again: 1.25, (text) ù 13/10/2008 (date) ù, 9.99 (text), and so on. It can be seen that several divisions are possible for each regional format: it is therefore necessary to define one or more cutting strategies. For example we can consider a first strategy, called gluttonous, which tends to favor large numbers and dates compared to small numbers, and a second so-called frugal strategy that tends to favor small numbers. Advantageously, these strategies will be used in parallel. Another idea underlying said preferred embodiment of the invention is that the characters forming part of the groups identified as signifiers can not be column separators (the groupings of characters enclosed in double quotation marks are treated as signifying groups ). It is therefore only among the other characters, which are not part of any signifying group, that we will look for the one that has the same number of occurrences in all the lines, and which will therefore be identified as the column separator. effective. If the number of lines of the file is sufficient, most often only one character will satisfy this condition, and that for a single format. We will have identified at the same time the structure of the table represented by the file and the data format of the table. The operating principle of the invention will now be illustrated with the aid of a more complete example. In this example, we try to analyze the following file, consisting of four lines: Name, Age, Date of birth Peter, 38.10 / 5/1970 Paul, 20,25 / 8/1988 Jacques, 70,7 / 7 / 1938 The interpretation of this file is obvious to a human being, but not to a computer. As in the previous example, we consider three regional formats (French - FR, English - UK and US - US), with both gluttonous and frugal strategies, and text type data, date or number. Note that the first line is a header, and is therefore different from the other lines. The method of the invention makes it possible to manage this additional difficulty. Figure 1 illustrates the analysis of the first line of the file. In principle, this line is divided into meaningful groupings in six different ways (two strategies for three regional formats). Concretely, it contains only text, and therefore no meaningful grouping can be identified, and this whatever the format considered. All characters (N, o, m,,, A, g, e, D, a, t,, d, n, i, s, c) are therefore considered as potential column separators, and their occurrences are counted . For each regional format / strategy pair, a counter indicates the number of potentially significant characters identified from the beginning of the file. These counters are initialized to zero, and their values remain null after analysis of the first line (which contains only text, and therefore no significant character). The analysis of the second line (Figure 2) is less commonplace. In US format, gluttonous strategy, the algorithm identifies the following signifying groups: 38 (number) and 10/5/1970 (date), that is 11 characters; the counter therefore takes the value 11. The potential column separators are therefore: P, i, e, r and,. However, P and r can be discarded immediately, because these characters 20 were absent in the first line: it can not therefore be effective column separators. The character e must also be discarded because it appears four times in the first line, but only twice in the second line. Only the characters, and i, whose number of occurrences is the same in the first and second rows are therefore retained as potential column separators. In US format, frugal strategy, the signifying groups are: 38, 10, 5 and 1970, that is 9 characters. The potential column separators are therefore: P, i, e, r,, and /. As in the previous case, only the characters, and i have the same number of occurrences in the first and in the second row, and are therefore retained as potential column separators. 2944895 Il In UK format, the results are the same as for the US format. It is therefore not necessary to describe the analysis in detail. In FR format, gluttonous strategy, the signifying groupings are 38.10 (number, with decimal point as a separator), 5 5, and 1970, that is 10 characters. The potential column separators are therefore: P, i, e, r,, and /. But this time, only one of the two commas is considered as a potential column separator: indeed, the comma that appears in 38,10 belongs to a signifying group, and can not be used to separate two columns. In these conditions, the only candidate for the column separator function is the character i, which appears once in each line outside any signifying group. The frugal strategy applied to the FR format gives the same results. The analysis of the third line is illustrated in FIG. 3. In US format, gluttonous strategy, the algorithm identifies the following signifying groups: 25, 8 and 1988, since 25/8/1988 is not a date valid in this format. These groupings comprise 9 characters in total, which brings the value of the counter to 20. 20 It is not necessary to count the occurrences of all the other characters of the line, but one can be interested only in, and i, whose number of occurrences was the same in the first and the second line. However, the i character does not appear in the third line of the file, which indicates that it can not be a column separator. On the other hand, the character, appears twice, as in the first and the second line: this character thus retains its potential column separator status. The frugal strategy analysis / US or UK format gives the same results. In UK format, gluttonous strategy, on the other hand, the division gives the following significant groups: 20 (number) and 25/8/1988 (date), that is 11 characters, which brings the value of the counter to 22. As in the In other cases, the character remains the only potential column separator. In FR format there is a difficulty: the comma was eliminated in the previous step, and only the character i was still considered a potential column separator. But the third column contains no i: this character must be discarded in turn. It follows that the adoption of the FR format does not identify a column separator. This does not necessarily mean that the file does not use the French format: after all, a file may have only one column. However, this assumption is considered unlikely, therefore the FR format is provisionally discarded. The analysis of the fourth and last line is illustrated in Figure 4. This analysis is simplified compared to the previous ones: firstly, because only four cuts have to be considered (US and UK formats, frugal and greedy strategies); secondly, only the number of occurrences of the character, must be determined. In all four cases, we find two occurrences of the character, outside any meaningful grouping. The column separator has thus been unambiguously identified as the character, but two regional formats remain possible: US and UK. To choose between these two formats, we are interested in the values recorded in the counters. We find: - US format, gluttonous strategy: 30 significant characters

Signifiers - US format, frugal strategy: 26 characters - UK format, gluttonous strategy: 32 characters 30 signifiers and - UK format, frugal strategy: 26 significant characters.

One chooses preferably the format / strategy pair which gives the higher number of significant characters, that is to say the UK format coupled with the gluttonous strategy. In conclusion, it has been determined that all the lines except the first one (which consists only of text) have the following structure: text - number in date in English format, as column separator. The first line is considered a header because of its different structure from the other lines. Since the columns all have different structures (the first is text data, the second is number data, and the third is date data) there is no reason to consider that the first column constitutes a header.

Thus, the method of the invention made it possible to determine the structure of the table by identifying the column separator, the type of each data field, the regional format used and the fact that the first line constitutes a header. This was done completely automatically, without the need for any intervention on the part of the user.

If no format / strategy pair had identified a separator character, this would have led to the conclusion that the file consisted of a single column. Identification of the regional format and type of data would not have been possible. Alternatively, it is possible to implement a simplified version of the method, in which only some of the non-significant characters are considered potential column separators. Indeed, it is unlikely that an alphanumeric character (A ù Z, a ù z, 0 ù 9) will be chosen as a column separator: we can therefore limit ourselves to counting the occurrences of other non-significant characters (signs of 30 punctuation, special characters, etc.). According to an even more simplified embodiment of the invention, it is sufficient to compare line by line the number of occurrences of all the characters, with the exception of those framed by double quotation marks, without prior identification of signifying groups. . This variant is much simpler to implement but, as shown by the example above, it does not always allow to uniquely determine a column separator. It may therefore be advantageous to proceed in two phases: in a first phase, only the simplified method is implemented; if at the end of this phase an ambiguity on the separator character remains, the more complete method, as described above, is implemented. lo 15 20 25

Claims (10)

REVENDICATIONS1. A computer heuristic analysis method of a computer file representative of a data table in order to reconstitute said table, the file being structured in lines of characters comprising column separator characters, characterized in that it comprises the use said computer to search for a character, except for any characters enclosed in double quotation marks, whose number of occurrences is the same for all the lines; said character being chosen as a column separator so as to determine an array structure. 2. Method according to claim 1, comprising the following steps: a. identifying, within each line, groups of characters considered significant, at least some of the characters not belonging to such groups being considered as potential column separators; this identification being performed in parallel for several predefined data formats; b. for each data format, searching for a character, among said potential column separators, whose number of occurrences is the same for all the lines; said character being chosen as an effective column separator, so as to determine a format dependent array structure; and c. the application of a selection criterion to choose a data format and the corresponding column separator as the most likely. 3. The method of claim 2, wherein step c. 30 comprises the elimination of the predefined format or formats that do not make it possible to identify an effective column separator. 0 5 10 20 25 4. Method according to one of claims 2 or 3, wherein step a. comprises using a plurality of character group identification strategies considered significant for each predefined data format. The method of claim 4, wherein step c. includes the selection of the format or the data format / character group identification strategy pair leading to the identification of the largest number of characters considered significant. 6. Method according to one of claims 2 to 5, wherein the steps a. and B. are implemented jointly, and include: identifying, within each line, said groupings of characters considered significant; a2. counting occurrences of potential column separators in said line; and a3. except for the first line analyzed, the deletion of potential column separators whose number of occurrences is different from that of the previous line. 7. Method according to one of claims 2 to 6, also comprising the identification of predefined types of said groups of characters considered significant. The method of claim 7 including identifying the first row of the array as a header line if all other rows in the array have a common structure, the structure of said first row being different. 30 9. Method according to one of claims 7 or 8, comprising the identification of the first column of the table as a column of headers if all the other columns of the table have a common structure, the structure of said first column being different. A computer program product comprising program code instructions for carrying out a method according to one of the preceding claims.