Text::C...parator(3)

📦
version 0.20 · 2 versions
, in fedora 43

💖 Text::CSV::Separator 💖

okay bestie, listen up — this is the page from User Contributed Perl Documentation for Text::CSV::Separator, and it is literally the friend who looks at your messy CSV file and goes "babe, that's a semicolon." 🎀

✨ NAME ✨

Text::CSV::Separator — Determine the field separator of a CSV file

🌸 VERSION 🌸

Version 0.20 — November 2, 2008

🦋 SYNOPSIS 🦋

use Text::CSV::Separator qw(get_separator);

my @char_list = get_separator(
                                path    => $csv_path,
                                exclude => $array1_ref, # optional
                                include => $array2_ref, # optional
                                echo    => 1,           # optional
                             );

my $separator;
if (@char_list) {
    if (@char_list == 1) {           # successful detection
        $separator = $char_list[0];
    } else {                         # several candidates passed the tests
        # Some code here
} else {                             # no candidate passed the tests
    # Some code here
}

# "I'm Feeling Lucky" alternative interface
# Don't forget to include the 'lucky' parameter

my $separator = get_separator(
                                path    => $csv_path,
                                lucky   => 1,
                                exclude => $array1_ref, # optional
                                include => $array2_ref, # optional
                                echo    => 1,           # optional
                             );

💅 DESCRIPTION 💅

This module gives you a fast way to sniff out the field separator character (aka the field delimiter) of a CSV file — or honestly, of any character separated text file (yes, "delimited text file" girlies, that's you) — and it hands it back to you all ready to drop straight into a CSV parser like Text::CSV_XS, Tie::CSV_File, or Text::CSV::Simple. Which is a whole blessing for the vulnerable -and often ignored- population of programmers who have to auto-process CSV files from who-knows-where. 🙈

The default squad of candidates is these characters: ',' ';' ':' '|' '\t'

The only required parameter is the CSV file path. If you want, you can also say which characters to exclude or include in the candidate list.

The routine returns an array holding the list of candidates that survived the tests. If it wins, that array holds exactly one value: the field separator you were looking for. And if nobody survives the tests? Empty list, bestie. Nothing. Void. 💀

The technique is based on this principle:

  • For every line in the file, the number of instances of the separator character acting as separators must be an integer constant > 0, although a line may also contain some instances of that character as literal characters.
  • Most of the other candidates won't appear in a typical CSV line.

The second a candidate misses a line, it gets yeeted from the candidates list. ✂️

This is the first test done to the CSV file. In most cases it'll detect the separator after processing just the first few lines. Especially if the file has a header line — one line is probably all it takes, no cap. Processing stops and hands control back to the caller the moment the program hits a status of 1 single candidate (or 0 candidates left).

If the routine can't figure out the separator in that first pass, it does a second pass based on several heuristic techniques. It checks whether the file has columns consisting of time values, comma-separated decimal numbers, or numbers containing a comma as the group separator — all of which can lead to false positives in files that don't have a header row. It also measures the variability of the remaining candidates. Obviously you can always craft a CSV file that resists the siege 🏰, but this approach works correctly in many cases. And the ability to exclude some of the default candidates can totally help resolve cases with several possible winners. The resulting array contains the list of possible separators sorted by their likelihood, with the first array item being the most probable separator. ✨

The module also ships an alternative interface with simpler syntax, which is handy if you think the files your program will deal with aren't too exotic. To use it, you just add the lucky => 1 key-value pair to the parameters hash and the routine returns a single value, so you can assign it straight to a scalar. If no candidate survives the first pass, it returns undef. This code path skips the 2nd pass, which is usually unnecessary anyway, so the program won't store counts and won't check any existing regularities. So it runs faster and eats less memory. Should be enough in most cases. 🍓

🔥 FUNCTIONS 🔥

get_separator(%options)

Returns an array containing the field separator character (or characters, if more than one candidate passed the tests) of a CSV file. In case no candidate passes the tests, it returns an empty list.

The available parameters are:

  • "path" — Required. The path to the CSV file.
  • "exclude" — Optional. Array containing characters to be excluded from the candidates list.
  • "include" — Optional. Array containing characters to be included in the candidates list.
  • "lucky" — Optional. If selected, get_separator will return one single character, or undef in case no separator is detected. Off by default.
  • "echo" — Optional. Writes to the standard output messages describing the actions performed. Off by default. This is useful to keep track of what's going on, especially for debugging purposes.

🌈 EXPORT 🌈

None by default.

🧚 EXAMPLE 🧚

Picture this scenario, bestie: your program has to process a batch of csv files, and you know the separator could be a comma, a semicolon, or a tab. You also know that one of the fields contains time values. That field is gonna hand out a fixed number of colons that could mislead the detection code. 😭 So in this case you should exclude the colon (and you can also exclude the other default candidate not considered, the pipe character):

my @char_list = get_separator(
                                path    => $csv_path,
                                exclude => [':', '|'],
                             );

if (@char_list) {
    my $separator;
    if (@char_list == 1) {
        $separator = $char_list[0];
    } else {
        # Some code here
    }
}

# Using the "I'm Feeling Lucky" interface:

my $separator = get_separator(
                                path    => $csv_path,
                                lucky   => 1,
                                exclude => [':', '|'],
                              );

👑 MOTIVATION 👑

XML may be popular, but the CSV file format is still widely used for data exchange between applications, because its overhead is so much lower: it needs way less bandwidth and storage space than XML, and it also performs better under compression (peep the References below 👀).

Unfortunately, there is no formal specification of the CSV format. The Microsoft Excel implementation is the most widely used and has become a de facto standard, but the variations are almost endless. 😵

One of the biggest annoyances of this format is that in most cases you don't know a priori what the field separator character used in a file is. CSV stands for "comma-separated values", but most spreadsheet applications let the user pick the field delimiter from a list of several different characters when saving or exporting data to a CSV file. And on a Windows system, when you save a spreadsheet in Excel as a CSV file, Excel uses the default list separator of your system's locale as the field delimiter — which just so happens to be a semicolon for several European languages. You can even customize that setting and use whatever list separator you like. For these and other reasons, automating the processing of CSV files is a risky task. 🫶

This module can be used to determine the separator character of a delimited text file of any kind, but since the aforementioned ambiguity problems show up mainly in CSV files, I decided to use the Text::CSV:: namespace.

⭐ REFERENCES ⭐

<http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm>

<http://www.xml.com/pub/a/2004/12/15/deviant.html>

💜 SEE ALSO 💜

There's another module in CPAN for this task, Text::CSV::DetectSeparator, which follows a different approach.

🌸 ACKNOWLEDGEMENTS 🌸

Many thanks to Xavier Noria for wise suggestions. The author is also grateful to Thomas Zahreddin, Benjamin Erhart, Ferdinand Gassauer, and Mario Krauss for valuable comments and bug reports.

🎀 AUTHOR 🎀

Enrique Nell, <perl_nell@telefonica.net>

Copyright (C) 2006 by Enrique Nell.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

and that's the whole page, bestie — go detect some separators, you absolute icon 👑

this was served up by perl v5.42.0 on 2025-07-25, mwah 💋

📦 where this page comes from