Re: perl + csv (684 Views)
Reply
Super Advisor
Piotr Kirklewski
Posts: 339
Registered: ‎01-12-2007
Message 1 of 14 (685 Views)

perl + csv

Hi there
How do I operate on collumns rather then lines in csv file from perl ?
I have two csv files each having a number of columns. The goal is to combine them together in a third file by concatenation.

File1 ColA,ColB
File2 ColC,ColD
File3 ColA,ColB,ColC,ColD

Thanks
Jesus is the King
Acclaimed Contributor
Dennis Handly
Posts: 25,287
Registered: ‎03-06-2006
Message 2 of 14 (685 Views)

Re: perl + csv

I assume you need to read both files in parallel and then write the columns from File1 then a comma then the columns in File2.

Or use: paste -d, File1 File2 > File3
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 3 of 14 (685 Views)

Re: perl + csv

Hi:

If you are serious about operating on CSV files, you should use a module that handles the nuances well. One such module is:

Text::CSV_XS

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

As a general response to your question, 'split' divides a sting of characters into elements (fields) of an array based on the regular expression you define as its delimiter. Using 'join' is one way to compose a new string with a new separator from an array.

If, as your example suggests your task is as simple as shown, you could simply use 'paste' to join the line of the two files into a third.

As usual, I urge you to *read* the manpages and the Perl documentation for the aforementioned commands. TMTOWTDI.

Regards!

...JRF...
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 4 of 14 (685 Views)

Re: perl + csv

Hi (again) Piotr:

So what hasn't satisfied your question?

...JRF...
Super Advisor
Piotr Kirklewski
Posts: 339
Registered: ‎01-12-2007
Message 5 of 14 (685 Views)

Re: perl + csv

The files look something like this:

ColA ColB
val1 val1
val2 val2
val3 val3

ColC ColD
val1 val1
val2 val2
val3 val3

ColA ColB ColC ColD
val1 val1 val1 val1
val2 val2 val2 val2
val3 val3 val3 val3
...

I tried "paste -d" but i think it operates on rows rather than columns. The output file was a complete mess.

Will try to use perl instead.
Jesus is the King
Acclaimed Contributor
Dennis Handly
Posts: 25,287
Registered: ‎03-06-2006
Message 6 of 14 (685 Views)

Re: perl + csv

>I tried "paste -d" but I think it operates on rows rather than columns. The output file was a complete mess.

Yes, paste(1) operates on lines. But in your case, you want to use all of the columns in a line and just concatenate them.
You might want to show the "complete mess".
Or use paste on your above simple example to see how it works.
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 7 of 14 (685 Views)

Re: perl + csv

Hi (again):

I tried "paste -d" but i think it operates on rows rather than columns. The output file was a complete mess.

Define "mess". What command syntax did you actually use? By default, a tab character is used for the field delimiter.

Regards!

...JRF...
Honored Contributor
H.Merijn Brand (procura
Posts: 6,189
Registered: ‎10-13-1997
Message 8 of 14 (685 Views)

Re: perl + csv

install, if not already installed, Spreadsheet::Read

It will use Text::CSV_XS to parse the CSV file/data and present you with a column-interface

Enjoy, Have FUN! H.Merijn
Enjoy, Have FUN! H.Merijn
Honored Contributor
H.Merijn Brand (procura
Posts: 6,189
Registered: ‎10-13-1997
Message 9 of 14 (685 Views)

Re: perl + csv

FWIW, this awfully looks like http://www.perlmonks.org/?node_id=901226

Read more there, though that also did not lead to a definite conclusion

Enjoy, Have FUN! H.Merijn
Enjoy, Have FUN! H.Merijn
Super Advisor
Piotr Kirklewski
Posts: 339
Registered: ‎01-12-2007
Message 10 of 14 (685 Views)

Re: perl + csv

Maybe paste is not that bad but it inserts ^M after the last column of the first file and before the first column of the second file:
This meses the format:

,Stopped (F),Stopped (F)^M,,2011-05-30-09:24:34.963,20,0,0,

:%s /^M/ /g fixes it from within the vim but the problem is I want to clean ^M from my script automatically.

Sed doesn't work:

sed -i s/"^M"/""/ test.csv

Please

Help
Jesus is the King
Super Advisor
Piotr Kirklewski
Posts: 339
Registered: ‎01-12-2007
Message 11 of 14 (684 Views)

Re: perl + csv

sed -i s/'^M'/""/ test.csv

Sorted :)
Jesus is the King
Super Advisor
Piotr Kirklewski
Posts: 339
Registered: ‎01-12-2007
Message 12 of 14 (684 Views)

Re: perl + csv

If i put this line in the script:

/bin/sed -i s/'^M'/""/ /home/peter/test/"0041"$TODAY".csv"

It gets interpreted as:

/bin/sed -i 's/^M//' /home/peter/test/004120110530.csv

And it doesn't remove the ^M from the file.

How to fix this ?

Jesus is the King
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 13 of 14 (684 Views)

Re: perl + csv

Hi (again):

> Maybe paste is not that bad but it inserts ^M after the last column of the first file and before the first column of the second file:

Then your file contained the carriage return in the first place. 'paste' uses a tab character as its default unless you override it with '-d .

Since you asked about Perl, you could remove the nasty carriage-returns with:

# perl -pi -e 's{\r$}{}' file

...which will do an in-place update of 'file' ridding it of the pesky carriage-returns.

Regards!

...JRF...
Acclaimed Contributor
Dennis Handly
Posts: 25,287
Registered: ‎03-06-2006
Message 14 of 14 (684 Views)

Re: perl + csv

>It gets interpreted as:

Are you using control-V control-M to insert the CR into the string?
sed -i -e 's/^V^M//' 004120110530.csv

Otherwise you can use echo:
sed -i -e 's/$(echo "\r\c")//' 004120110530.csv
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.