Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Writing to csv file

by maestromani (Initiate)
on Mar 02, 2011 at 10:33 UTC ( #890943=perlquestion: print w/replies, xml ) Need Help??

maestromani has asked for the wisdom of the Perl Monks concerning the following question:

Hi There, I'm in the process of generating csv file from some output. some variable in the script has character+ ", ". Because of this while priting in csv these characters are getting added in two column. How do i print then in one column. Example: Title variable -> bob,tom got XYZ this is printing in csv as bob tom got xyz BRS

Replies are listed 'Best First'.
Re: Writing to csv file
by roboticus (Chancellor) on Mar 02, 2011 at 10:46 UTC

    maestromani:

    You should put quotes around the data items that contain commas. Unfortunately, there are too many CSV file formats. So rather than trying to fix it yourself, you should try using a module such as Text::CSV to generate your file. It has a few optional parameters that can help you adapt your CSV file to whatever variation used by the software that reads your file.

    Note: In the future, you should provide your code, example data and example output to make it easier to help you.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Re: Writing to csv file
by mertserger (Curate) on Mar 02, 2011 at 10:54 UTC
    If you have a copy of the Perl CookBook by O'Reilly there is a bit about parsing CSV data in Section 1.15, which would be worth reading. This suggests using Text::ParseWords
      He's not parsing CSV, he's generating it.
Re: Writing to csv file
by jdporter (Canon) on Mar 02, 2011 at 18:31 UTC
Re: Writing to csv file
by fidesachates (Monk) on Mar 02, 2011 at 17:17 UTC
    This might be bad practice. I don't know; a fellow monk will have to tell me if it is.

    When I don't know the application that will be reading my csv file, I search for commas before writing to the file and replace it with a random character that means nothing. For instances/,/|/g. This way the reader of the csv file can always split by commas. The only thing to look out for is which character you pick to use to replace the comma.

      The problem with replacing a comma with a different character, such as a pipe symbol, is that there is a good chance that that character will turn up in the data at some point in the future. If taking this approach I think I would lengthen the odds by using a longer string rather than a single character, something like

      foreach my $field ( @fields ) { $field =~ s{,}{::comma::}g; }

      I hope this is of interest.

      Cheers,

      JohnGG

      I do not substitute a comma in the text with another character. I just normally use "|" to separate fields. See post below.
Re: Writing to csv file
by Marshall (Canon) on Mar 02, 2011 at 22:28 UTC
    "CSV" or Comma Separate Value has evolved. If you actually use commas, then there is the problem of commas within quotes. The program and regex logic to deal with with this is actually quite complex.

    An alternate to the comma that is used in many data base outputs is the "pipe" or | symbol. Although common in programming languages, this symbol is almost unknown in name, address, department name fields, etc. A database output that uses the "|" instead of the "," to separate fields is also known as a "CSV" file. Microsoft Excel can easily import CSV files separated by "|".

    If I have a choice when generating the file, I use "|". When I have to read a file from some other source, very often but not always it uses "," and I have to deal with that.

    So, if you generate the file, I would use "|" instead of "," because it makes the parsing easier - which is reason enough.

      Using a different character, or even a string of characters, makes no difference to the nature of the problem of the possibility that the separator may validly be part of the data. The much better solution is to quote the separator string in some fashion if it appears in the data. Such quoting is what CSV modules do, albeit with some lack of standardisation in how the quoting is achieved.

      True laziness is hard work
        Thanks a lot for all the reply. I'm generating csv for crystal report. If i replace , with | Crystal report is not identifying a separate column.. All i did is used "=~ s{,}{::comma::}g;" as advised by John BRs
        I don't see any real disagreement here. Some of the databases I work with use "|" instead of "," and disallow "|" as a valid data character - so this "|" within "|" issue does not come up.

        When I generate a CSV file on my own, sometimes I use the "|" instead of "," when I know that "|" is not going to be in the data. I'm just saying that option is possible. Although the format is called "Comma Separated Value", the character can be anything you want, which is what you are saying. Some folks take CSV literally and don't realize that some other character can be used and that standard programs can parse files like that. To parse the general case of a CSV file, I would use one of the Perl modules as this is a very deceptively easy looking, but very hard to implement specification.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://890943]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (3)
As of 2020-10-28 07:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (260 votes). Check out past polls.

    Notices?