CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Replace commas between quotes with spaces while parsing CSV

 



BigRedEO
Novice

Apr 15, 2016, 10:30 AM

Post #1 of 3 (1582 views)
Replace commas between quotes with spaces while parsing CSV Can't Post

I've asked this question elsewhere, have not had luck yet. I will try here -

I have happened upon a problem with a program that parses through a CSV file with a few million records: two fields in each line has comments that users have put in, and sometimes they use commas within their comments. If there are commas input, that field will be contained in double quotes. I need to replace any commas found in those fields with a space. Here is one such line from the file to give you an idea, with the two problem fields in bold (NOTE - I put breaks in this one line to make it fit on the one page) -


Code
1925,47365,2,650187016,1,1,"MADE FOR DRAWDOWNS, NEVER P/U",16,IFC 8112NP,Standalone-6,,,44,10/22/2015, 
91607,,B24W02651,,"PA-3, PURE",4/28/2015,1,0,,1,MAN,,CUST,,CUSTOM MATCH,0,TRUE,TRUE,O,
C48A0D001EF449E3AB97F0B98C811B1B,POS.MISTINT.V0000.UP.Q,PROD_SMISA_BK,
414D512050524F445F504F5331393235906F28561D2F0020,10/22/2015 9:29,10/22/2015 9:30



NOTE - I do not have the Text::CSV module available to me, nor will it be made available in the server I am using.

Here is part of my code in parsing this file. The first thing I do is concatenate the very first three fields and prepend that concatenated field to each line. Then I want to clear out the commas in @fields[7,19], then format the DATE in three fields and the DATETIME in two fields. The only line I can't figure out is replacing those commas with a space. Is there a tr line or regex line or anything else that would work? -


Code
    my @data; 

# Read the lines one by one.

while ( $line = <$FH> ) {

# split the fields, concatenate the first three fields,
# and add it to the beginning of each line in the file

chomp($line);
my @fields = split(/,/, $line);

unshift @fields, join '_', @fields[0..2];

# remove user input commas in fields 7 and 19

$_ = for fields[7,19];

# format DATE and DATETIME fields for MySQL/sqlbatch60

$_ = join '-', (split /\//)[2,0,1] for @fields[14,20,23];
$_ = Time::Piece->strptime($_,'%m/%d/%Y %H:%M')->strftime('%Y-%m-%d %H:%M') for @fields[38,39];

# write the parsed record back to the file

push @data, \@fields;
}



FishMonger
Veteran / Moderator

Apr 15, 2016, 2:53 PM

Post #2 of 3 (1574 views)
Re: [BigRedEO] Replace commas between quotes with spaces while parsing CSV [In reply to] Can't Post

I'd have to search your past threads (here and on the other forums) to be sure, but I believe a previously mentioned to you that you should be using the Text::CSV module instead of manually parsing the fields. That module knows how to properly handle the embedded commas which will negate the need for you to manually parse and replace those commas.

http://search.cpan.org/~hmbrand/Text-CSV_XS-1.23/CSV_XS.pm


FishMonger
Veteran / Moderator

Apr 15, 2016, 2:56 PM

Post #3 of 3 (1573 views)
Re: [BigRedEO] Replace commas between quotes with spaces while parsing CSV [In reply to] Can't Post


Quote
NOTE - I do not have the Text::CSV module available to me, nor will it be made available in the server I am using.


I skipped over that in my initial reading of your question.

I counter that issue by recommending that install the module. It will save you a bunch of headaches in the parsing.

If you don't want to install the module, then you should at least read its source code so you can learn the proper methods to parse csv files


(This post was edited by FishMonger on Apr 15, 2016, 2:59 PM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives