Excel data manipulate

rahulrathod · #1 (**permalink**) 11-26-2008

All,

I have the following format of data in a spreadsheet

Code:

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.

Franklin52 · #2 (**permalink**) 11-26-2008

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

mk1216 · #3 (**permalink**) 11-26-2008

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

rahulrathod · #4 (**permalink**) 11-26-2008

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.

CarlosNC · #5 (**permalink**) 11-26-2008

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

CarlosNC · #6 (**permalink**) 11-26-2008

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

rahulrathod · #7 (**permalink**) 11-26-2008

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.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Help on email data file as excel from unix!!	sparan_peddu	Shell Programming and Scripting	1	10-19-2008 03:08 AM
manipulate data with specific format	tonet	Shell Programming and Scripting	5	04-25-2008 12:24 PM
how to read the data from an excel sheet and use those data as variable in the unix c	Anne Grace	UNIX for Advanced & Expert Users	1	03-03-2008 07:21 AM
sed or other tool to manipulate data, including email addresses	manouche	Shell Programming and Scripting	3	02-19-2008 05:53 PM
reading data from excel using shell script	tiger99	Shell Programming and Scripting	11	01-06-2008 01:35 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: