UNIX for Dummies Questions & Answers

All,

I have the following format of data in a spreadsheet

Code:

A	1
            2
            3
            4
B	1
            2
            3
            4

where 'A' is value of 'A1', '1 2 3 4' is value of cell B1, 'B' is value of cell A2, and '1 2 3 4' is value of cell B2.

There are thousands of rows with such a data format in the spreadsheet.

Is there any way which you can think where I can get this data in the followin format.

Code:

A          1
A          2
A          3
A          4
B          1
B          2
B          3
B          4

I was thinking if I could convert the spreadsheet to a csv and do some manipulations using sheel script.

The above is just an example format. the data in the cells in spreadsheet may be different.

Regards,
Rahul.

It seems that your cells have different properties (amounts and text) in the same column. Select the whole column and change the property for the whole column.

Regards

I think we first need to see data in csv , then can think of using shell.

Yes. Data in csv, will come like this

A,"1234"
B,"1234"

for the spreadsheet.

But what if data is like

A abcd
cdab
B abcd
fedh

I wont be able to distinguish the cell values.

The problem is cells in column B have multiple values separated by new line char in the excel. and i want this to be mapped individually to cell value in column A.

Assuming the number of columns is consistent throughout, and in this case is 1 or 2, an awk script might look like:

Code:

awk -F, '
NF == 2 {x=$1; print; next}
NF == 1 {print x","$1}
' infile > outfile

Cheers

If there are spaces instead of commas, just remove the -F, from the statement, and replace "," with " " in the third line.

Code:

awk '
NF == 2 {x=$1; print; next}
NF == 1 {print x" "$1}
' infile > outfile

Ok. Lets make it simple. I have got the data into a csv file as follows.

Code:

A,"YAHOO3456-K9YAHOO9876YAHOO67/2YAHOO65432-1"
B,"YAHOO01YAHOO07865YAHOO67-AD987/1YAHOO67/23"

And what I want as the output is

Code:

A,YAHOO3456-K9
A,YAHOO9876
A,YAHOO67/2
A,YAHOO65432-1
B,YAHOO01
B,YAHOO07865
B,YAHOO67-AD987/1
B,YAHOO67/23

The format that would be common is YAHOOxxxxx in the 2nd column.

Let me know if you can think of an easy conversion.

Thanks,
Rahul.