By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,455 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

How to read csv file that has ',' within data?

P: 13
I am new to Perl programming and I faced a problem now.

I need to process a csv file and as for now, if the string is like '123' or 'asd', I could do it without any problem because it is commas inbetween data. But if I want to process like 'asd,asd,asd', I do not know how to read the whole string with the commas because as of now, I use split ',' to get data from different field of the csv. please help!!!
Jul 30 '08 #1
Share this Question
Share on Google+
8 Replies


nithinpes
Expert 100+
P: 410
I believe you have tried writing the code. But instead of putting it in plain words, it would have been better if you posted the piece of script that you tried and the sample data.
Suppose, your data is as below:
Expand|Select|Wrap|Line Numbers
  1. 'asd,asd,asd','123','123'
  2. 'asd','dgf','pqr'
  3.  
One way of looking at the requirement is, you would essentially need to take out the string within single quotes (' ') rather than that delimited by comma(,). You can acheive this through regex.
The code:
Expand|Select|Wrap|Line Numbers
  1. use strict;
  2. open (IN,'input_file') or die "$!"; 
  3. my @data;
  4.  while (<IN>) {
  5. push @data, $1 while(/('.+?')/g); #get the string surrounded with single-quotes
  6. }
  7. print "$_\n" foreach(@data);
  8.  
will produce the output:
Expand|Select|Wrap|Line Numbers
  1. 'asd,asd,asd'
  2. '123'
  3. '123'
  4. 'asd'
  5. 'asd'
  6. 'asd'
  7.  
-Nithin
Jul 30 '08 #2

KevinADC
Expert 2.5K+
P: 4,059
In a properly formatted CSV file, some of the data will be quoted and some will not be. The Text::CSV_XS module can correctly parse a CSV file with embedded commas as long as that particular field is quoted.
Jul 30 '08 #3

P: 13
Thanks for the prompt reply.

My data was input through excel then save under csv. In the excel, i gt few fields... Lets say is "abc,def,ghi",123,asd

I tried this

Expand|Select|Wrap|Line Numbers
  1. my $csv = "test.csv";
  2. my @data = read_file($csv);
  3.  
  4. foreach($a=1; $a<@data.""; $a++)
  5. {
  6.     print @data[$a];
  7. }
  8.  
  9. sub read_file{
  10.     open(F, $_[0]) || die("Can't open file $_[0]!");
  11.     my @data = <F>;
  12.     close F;
  13.     return @data;
  14. }
the output is "abc,def,ghi",123,asd

What i 1 is that if i to assign "abc,def,ghi" to $name, how do i get the data within the double quote and also how to assign the 123 and asd which doesnt have the double quote as output to be $contact_no and $address respectively?

I tried using the delimited comma, but within the double quote, there are 2 commas, as a result, i will have 5 individual data...
Jul 30 '08 #4

KevinADC
Expert 2.5K+
P: 4,059
I still recommend you use Text::CSV_XS but here is a way to do it without the module:

Expand|Select|Wrap|Line Numbers
  1. while (<DATA>) {
  2.     my @new  = ();
  3.     push(@new, $+) while $_ =~ m{
  4.         # the first part groups the phrase inside the quotes.
  5.         # see explanation of this pattern in MRE
  6.         "([^\"\\]*(?:\\.[^\"\\]*)*)",?
  7.            |  ([^,]+),?
  8.            | ,
  9.        }gx;
  10.        push(@new, undef) if substr($_, -1,1) eq ',';
  11.        print "$_\n" for @new;
  12. }  
  13.  
  14. __DATA__
  15. "abc,def,ghi",123,asd
The solution is from the Perl CookBook which got the regexp from "Mastering Regular Expressions". Don't ask me to explain the regexp, it confuses the hell out of me.
Jul 30 '08 #5

P: 13
Thanks alot kevin.

But, if i want to use the module, do you mind if you actually show me an example? thanks alot in advance... :p
Jul 30 '08 #6

KevinADC
Expert 2.5K+
P: 4,059
The modules documentation has examples. You can find the documentation on CPAN or google for Text::CSV_XS.
Jul 30 '08 #7

P: 13
Just for information, may i know are you from Singapore?
Jul 30 '08 #8

KevinADC
Expert 2.5K+
P: 4,059
Just for information, may i know are you from Singapore?
If you are asking me, no, I am not from Singapore. And I have never been there.
Jul 30 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.