topic Re: perl + csv in Operating System - HP-UX

perl + csv

Piotr Kirklewski — Mon, 30 May 2011 11:53:33 GMT

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

Re: perl + csv

Dennis Handly — Mon, 30 May 2011 12:25:33 GMT

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

Re: perl + csv

James R. Ferguson — Mon, 30 May 2011 12:30:22 GMT

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...

Re: perl + csv

James R. Ferguson — Mon, 30 May 2011 12:49:42 GMT

Hi (again) Piotr:

So what hasn't satisfied your question?

...JRF...

Re: perl + csv

Piotr Kirklewski — Mon, 30 May 2011 12:55:19 GMT

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.

Re: perl + csv

Dennis Handly — Mon, 30 May 2011 13:03:43 GMT

>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.

Re: perl + csv

James R. Ferguson — Mon, 30 May 2011 13:13:40 GMT

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...

Re: perl + csv

H.Merijn Brand (procura — Mon, 30 May 2011 13:14:27 GMT

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

Re: perl + csv

H.Merijn Brand (procura — Mon, 30 May 2011 13:46:25 GMT

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

Re: perl + csv

Piotr Kirklewski — Mon, 30 May 2011 19:02:09 GMT

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

Re: perl + csv

Piotr Kirklewski — Mon, 30 May 2011 19:25:06 GMT

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

Sorted :)

Re: perl + csv

Piotr Kirklewski — Mon, 30 May 2011 19:54:36 GMT

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 ?

Re: perl + csv

James R. Ferguson — Mon, 30 May 2011 19:57:01 GMT

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...

Re: perl + csv

Dennis Handly — Mon, 30 May 2011 21:15:14 GMT

>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