perl + csv

[Piotr Kirklewski]

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

[Dennis Handly]

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

[James R. Ferguson]

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

[James R. Ferguson]

Hi (again) Piotr:

So what hasn't satisfied your question?

...JRF...

[Piotr Kirklewski]

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.

[Dennis Handly]

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

[James R. Ferguson]

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

[H.Merijn Brand (procura]

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

[H.Merijn Brand (procura]

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

[Piotr Kirklewski]

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

[Piotr Kirklewski]

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

Sorted :)

[Piotr Kirklewski]

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 ?

[James R. Ferguson]

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

[Dennis Handly]

>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