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:
Parsing CSV file - Add Concatenated field/sort

 

First page Previous page 1 2 3 Next page Last page  View All


BigRedEO
Novice

Mar 28, 2016, 8:42 AM

Post #26 of 53 (9369 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

I haven't tried DBI yet because I haven't created the MySQL table yet.


(This post was edited by BigRedEO on Mar 28, 2016, 8:53 AM)


BigRedEO
Novice

Mar 28, 2016, 8:46 AM

Post #27 of 53 (9365 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

I just downloaded the file I attached in this thread and FTPed it to my server and tried to run it - STILL got that weird hiccup on the last variable.


BigRedEO
Novice

Mar 28, 2016, 8:52 AM

Post #28 of 53 (9359 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

I just did a cat -vet on my Perl script and have this (I'm trying to add writing to an Outfile) -

Code
#!/usr/bin/perl/  $ 
$
use strict; $
use warnings; $
use Data::Dumper; $
$
my $filename = '/swpkg/shared/batch_processing/mistints/test.csv'; $
$filename = 'test.csv'; $
$
open my $FH, $filename $
or die "Could not read from $filename <$!>, program halting."; $
$
# Read the header line. $
chomp(my $line = <$FH>); $
my @fields = split(/,/, $line); $
#print "Field Names:\n", Dumper(@fields), $/; $
print Dumper(@fields), $/;$
$
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]; $
push @data, \@fields; $
} $
close $FH; $
print "Unsorted:\n", Dumper(@data); #, $/; $
$
@data = sort { $
$a->[0] cmp $b->[0] || $
$a->[20] cmp $b->[20] || $
$a->[23] cmp $b->[23] || $
$a->[26] cmp $b-> [26] $
} @data; $
$
$
open my $OFH, '>', '/swpkg/shared/batch_processing/mistints/parsedTest.csv';$
print $OFH Dumper(@data);$
close $OFH;$
$
$
#print "Sorted:\n", Dumper(@data); $
#print "Sorted:", Dumper(@data);$
$
exit;$
$


Is there something in there I'm not seeing that is causing the weird hiccup with that last variable?

Also, how to I write it to the outfile as a single record, minus the single quotes around each field, rather than the entire record as on Variable each?


FishMonger
Veteran / Moderator

Mar 28, 2016, 8:56 AM

Post #29 of 53 (9354 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Run this command on the server and attach that file so I can review it.


Code
od -c test.csv > test.csv_od_dump



FishMonger
Veteran / Moderator

Mar 28, 2016, 9:00 AM

Post #30 of 53 (9351 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post


In Reply To
I haven't tried DBI yet because I haven't created the MySQL table yet.


You don't need mysql to run the test. The script uses the csv file, not the database.


BigRedEO
Novice

Mar 28, 2016, 9:03 AM

Post #31 of 53 (9345 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Attached is the od_dump file
Attachments: test.csv_od_dump (6.32 KB)


FishMonger
Veteran / Moderator

Mar 28, 2016, 9:05 AM

Post #32 of 53 (9341 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

That file is corrupt.


BigRedEO
Novice

Mar 28, 2016, 9:05 AM

Post #33 of 53 (9340 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Aha! I'll learn new things yet - never would have guess that's how it works.


BigRedEO
Novice

Mar 28, 2016, 9:07 AM

Post #34 of 53 (9336 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

I will recreate the test file - all I did was

Code
head -4 bigfile.csv . test.csv

So I'll try that again.


BigRedEO
Novice

Mar 28, 2016, 9:10 AM

Post #35 of 53 (9333 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Nope - STILL the same error. I hope that doesn't mean my main file is corrupted?


BigRedEO
Novice

Mar 28, 2016, 9:19 AM

Post #36 of 53 (9328 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Just attempted the DBI version and got this when I tried to run it -


Code
Can't locate DBI.pm in @INC (@INC contains: /usr/perl5/site_perl/5.12/sun4-solaris-64int /usr/perl5/site_perl/5.12 /usr/perl5/vendor_perl/5.12/sun4-solaris-64int /usr/perl5/vendor_perl/5.12 /usr/perl5/5.12/lib/sun4-solaris-64int /usr/perl5/5.12/lib .) at AlterDataNewDBI.pl line 5. 
BEGIN failed--compilation aborted at AlterDataNewDBI.pl line 5.



FishMonger
Veteran / Moderator

Mar 28, 2016, 9:24 AM

Post #37 of 53 (9324 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

The od dump file should look like this:

c:\test>od -c test.csv

Code
0000000   S   T   O   R   E   _   N   B   R   ,   C   O   N   T   R   O 
0000020 L _ N B R , L I N E _ N B R , S
0000040 A L E S _ N B R , Q T Y _ M I S
0000060 T I N T , R E A S O N _ C O D E
0000100 , M I S T I N T _ C O M M , S Z
0000120 _ C D E , T I N T E R _ M O D E
0000140 L , T I N T E R _ S E R L _ N B
0000160 R , S P E C T R O _ M O D E L ,
0000200 S P E C T R O _ S E R L _ N B R
0000220 , E M P _ N B R , T R A N _ D A
0000240 T E , T R A N _ T I M E , C D S
0000260 _ A D L _ F L D , P R O D _ N B
0000300 R , P A L E T T E , C O L O R _
0000320 I D , I N I T _ T R A N _ D A T
0000340 E , G A L L O N S _ M I S T I N
0000360 T E D , U P D A T E _ E M P _ N


Lets pull directly from the big csv file.


Code
head -4 bigfile.csv | od -c > od_dump

Attach that dump file.


FishMonger
Veteran / Moderator

Mar 28, 2016, 9:26 AM

Post #38 of 53 (9322 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

You need to install the DBI and DBD::CSV modules as well as Text::CSV_XS module.


BigRedEO
Novice

Mar 28, 2016, 9:28 AM

Post #39 of 53 (9320 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Attached is the od_dump from the big file
Attachments: od_dump (6.32 KB)


BigRedEO
Novice

Mar 28, 2016, 9:53 AM

Post #40 of 53 (9317 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

I tried this command for installing DBI - failed with "fatal error."

Code
perl -MCPAN -e 'install Bundle::DBI'



BigRedEO
Novice

Mar 28, 2016, 10:38 AM

Post #41 of 53 (9313 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

PROBLEM CORRECTED ON THE TEST FILE -

I used dos2unix on the file and it now reads correctly. Most likely a Ctrl character unseen in there somewhere.

Now - how to format it to write back to a .csv file the same format as the original?


(This post was edited by BigRedEO on Mar 28, 2016, 10:38 AM)


FishMonger
Veteran / Moderator

Mar 28, 2016, 11:33 AM

Post #42 of 53 (9302 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post


In Reply To
PROBLEM CORRECTED ON THE TEST FILE -

I used dos2unix on the file and it now reads correctly. Most likely a Ctrl character unseen in there somewhere.

Now - how to format it to write back to a .csv file the same format as the original?



Code
print join(',', @$_), $/ for @data;


Or use the Text::CSV module.


BigRedEO
Novice

Mar 28, 2016, 12:41 PM

Post #43 of 53 (9298 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

That has done it! I was able to sort the actual, very large file and all data appears to be correct.

Thank you. This gets me further along to where I need to be!


BigRedEO
Novice

Apr 12, 2016, 7:55 AM

Post #44 of 53 (9240 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Running into a new problem - I have to format the Date and DateTime fields in this .csv file from MM/DD/YYYY to YYYY-MM-DD. I added these two lines -

Code
my $in_date = $fields[14]; 
my $db_date = join '-', reverse split /\D/, $in_date;

right after the push @data, \@fields; line and the script ran without any errors, but it also didn't reformat the date field. And I actually need to do this on three fields - [14], [20], [23]. And I also will need to reformat the dates in the last two fields, both of which are DATETIME - how do I format just the Date in the DATETIME fields?


FishMonger
Veteran / Moderator

Apr 12, 2016, 8:50 AM

Post #45 of 53 (9231 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post


Code
my $db_date = join '-', (split m{/}, $in_date)[2,0,1];



BigRedEO
Novice

Apr 12, 2016, 8:57 AM

Post #46 of 53 (9229 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Thank you - but I guess I'm missing a line to put it back into the file.


Code
#!/usr/bin/perl/ 

use strict;
use warnings;
use Data::Dumper;

my $filename = 'tested.csv';

open my $FH, $filename
or die "Could not read from $filename <$!>, program halting.";

# Read the header line.
chomp(my $line = <$FH>);
my @fields = split(/,/, $line);
print Dumper(@fields), $/;

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];
push @data, \@fields;
my $in_date = $fields[14];
my $db_date = join '-', (split m{/}, $in_date)[2,0,1];
}
close $FH;
print "Unsorted:\n", Dumper(@data); #, $/;

@data = sort {
$a->[0] cmp $b->[0] ||
$a->[20] cmp $b->[20] ||
$a->[23] cmp $b->[23] ||
$a->[26] cmp $b-> [26]
} @data;


open my $OFH, '>', '/swpkg/shared/batch_processing/mistints/parsedMistints.csv';
print $OFH join(',', @$_), $/ for @data;
close $OFH;

exit;


What am I missing to put it back in with the rest of the data?


FishMonger
Veteran / Moderator

Apr 12, 2016, 9:11 AM

Post #47 of 53 (9224 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Fix the date formatting before the push statement and instead of copying those fields into new vars, just work with the @fields array directly.

Code
    my @fields = split(/,/, $line); 
unshift @fields, join '_', @fields[0..2];
$fields[14] = join '-', (split m{/}, $fields[14])[2,0,1];
# fix the other date fields in the same way then do the push
push @data, \@fields;



BigRedEO
Novice

Apr 12, 2016, 9:34 AM

Post #48 of 53 (9220 views)
Re: [FishMonger] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

I've run into some errors here in trying that same line for each of the three date fields -

Useless use of a constant (2) in void context at AlterDataNew.pl line 28.
Useless use of a constant (2) in void context at AlterDataNew.pl line 29.
Useless use of a constant (2) in void context at AlterDataNew.pl line 30.
Can't use string ("10/23/2015") as an ARRAY ref while "strict refs" in use at AlterDataNew.pl line 28, <$FH> line 2.


FishMonger
Veteran / Moderator

Apr 12, 2016, 9:43 AM

Post #49 of 53 (9217 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

Post your updated script.

The change I suggested would not have given you those warnings. You must have made some additional changes.


FishMonger
Veteran / Moderator

Apr 12, 2016, 9:50 AM

Post #50 of 53 (9214 views)
Re: [BigRedEO] Parsing CSV file - Add Concatenated field/sort [In reply to] Can't Post

You should also post (as an attachment) a short 10 line sample input data file so I can try to duplicate your problem.

First page Previous page 1 2 3 Next page Last page  View All
 
 


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

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