I have a CSV file with anywhere from 10 - 15 rows and > 256 columns of comma separated values.
I'm trying to rotate this by 90 degrees so that i can open the csv file in excel.

so basically i want to take this:

0,0,1
0,0,0
0,1,1

0,0,0
0,0,1
1,0,1

while ($lines = <filein>{
	chomp($lines);
	@values[$i] = split /\,/, $lines;
	foreach $value (@values){

	}

}

1/24/2007 2:16:31 PM

why cant you open in the excel as is?

1/24/2007 2:18:50 PM

i have more columns than excel so all my data doesn't fit.

1/24/2007 2:21:28 PM

thats interesting. I didn't think excel had a limit on rows or columns. 2007 doesn't.

1/24/2007 2:25:32 PM

2007 can have 1,000s of columns. My first thought was just to install a trial version of it.

but my work laptop has windows 2000... Office 2007 won't install on windows 2000.

1/24/2007 2:27:35 PM

Quote :

"0,0,1 0,0,0 0,1,1 and make this: 0,0,0 0,0,1 1,0,1"

Correct me if I'm wrong here, but didn't you do the example incorrectly?

Shouldn't the 2nd one look like this:

0,0,0
1,0,0
1,0,1

?

1/24/2007 3:04:23 PM

^ No, he wants column 1 to equal row 1, column 2 to equal row 2, etc...

1/24/2007 3:29:43 PM

ah, yeah. It was the term "rotate 90 degrees" that was throwing me off. Really he wants switch the axis. I forgot he actually wanted to be able to use the data when he's through.

1/24/2007 3:41:04 PM

Thanks for making this clear darkone.

1/24/2007 3:54:40 PM

i would use fortran because i'm cool like that

(and limited in my programming abilities)

1/24/2007 4:23:53 PM

My pleasure. Doesn't Excel have a function where you can specify these kinds of geometry changes when you're importing the data?

1/24/2007 4:23:54 PM

darkone,

I think excel can do rearranging/formatting things like this, but I can't fit all the original data in excel in its "horizontal" form.

1/24/2007 4:28:38 PM

import java.util.*;
import java.io.*;

public class RotateDis
{
	private String inFile;
	private String outFile;

	private LinkedList junk;

	public RotateDis(String inFile, String outFile)
	{
		this.inFile=inFile;
		this.outFile=outFile;

		this.junk = new LinkedList();


	}


	public void rotate()
	{

		try
		{
			BufferedReader br = new BufferedReader(new FileReader(inFile));

			String s = null;




			while((s=br.readLine())!=null)
			{
				String [] stuff = s.split(",");
				junk.add(stuff);
			}

			br.close();

			int rows=0;
			if(junk.size()>0)
			{
				rows=  ((String[])junk.get(0)).length;
			}


			BufferedWriter bw = new BufferedWriter(new FileWriter(this.outFile));

			for(int i=0;i<rows;i++)
			{
				for(int j=0;j<junk.size();j++)
				{
					s = ((String[])junk.get(j))[i];
					bw.write(s);
					if(j+1<junk.size())
					{
						bw.write(",");
					}


				}
				bw.newLine();
			}

			bw.close();




		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	public static void main(String [] args)
	{

		RotateDis rotator = new RotateDis(args[0],args[1]);

		rotator.rotate();
	}





}

1/24/2007 4:34:50 PM

^ Yes it won't fit in the horizontal as is, but if you can get excel to "rotate" the data as it's imported it should avoid that horizontal limitation. I'm on my redhat box in my lab so I don't have a copy of excel to play with at the moment.

If it was me, I'd have probably written a quick script to rearrange the data in matlab and rewrite it to a text file, but that's just what I know.

1/24/2007 4:35:31 PM

^^ thanks shaggy, I thought of using java as i'm more familiar with it, but i didn't want to have to install it on my system. Maybe i can still get some ideas from your code.

^I just briefly looked through the import options and didn't see anything. I'll give it a more in depth look a little later.

1/24/2007 4:40:20 PM

ok - here's a way you might be able to get around the column/row limits in excel.

Import your dataset into Excel, but NOT IN CSV format. Import each line of the text file (including the commas) into a single cell in column A, so e.g.
A1 = 0,0,1
A2 = 0,0,0
A3 = 0,1,1

Do that in Sheet1.
Now go into Sheet2, and paste the following formula into A1:

=MID(INDEX(Sheet1!$A$1:$A$12,COLUMN()),ROW()*2-1,1)

1/24/2007 4:59:51 PM

Yea, I was gonna ask if it is possible to split it across different sheets. How many columns do you have?

1/24/2007 5:04:45 PM

The word is transpose, not rotate.

1/24/2007 7:55:55 PM

what are openoffice calc's limits on rows and columns?

1/24/2007 8:23:50 PM

this document (from 2004) says OO has the same limits as excel - 256 cols and 32k rows
http://sc.openoffice.org/row-limit.html

the original post said he has "anywhere from 10 - 15 rows and > 256 columns", so my simple excel solution above can easily handle that as long as there are less than 32k columns in the original data

1/24/2007 8:27:40 PM

yea just get office 2007

1/24/2007 10:14:14 PM

Quote :

"The word is transpose, not rotate."

Thanks I was about to say that.

This is how to do it in SAS:

PROC IMPORT
  DATAFILE='location-of-your-csv-file'
  OUT=original
  DBMS=CSV
RUN;
PROC TRANSPOSE
  DATA=original
  OUT=transposed;
RUN;

1/25/2007 12:25:44 AM

I love: LimpyNuts, philihp

1/25/2007 2:48:08 AM

LimpyNuts, philihp, Thanks for the correction, I can go on living now.

Quote :

"yea just get office 2007"

-Can't installing office 2007 on windows 2000. Can't update OS because i'm doing this on my work laptop and I'm pretty sure IT would come break my fingers.

agentlion,
Thanks! that almost works, but perhaps i've dumbed down my example of what i want to transpose a little too much.
in reality its not going to be only 1s and 0s, and i will not know how many digits each number will have. here is a short sample:
0,771,7,408,1,19,0,127,62,246,89,658,498,338312

1/25/2007 9:00:12 AM

oh, right. well in that case, you might want to use a bit of VBA. Doing it only in Excel forumlas would be very complicated, using a ton of SUBSTITUE and FIND functions, which are not very user friendly, and unfortunately there is no formula for returning a specific occurance of a word from a string.

Define the following custom function by opening the VBA editor (Tools > Macro > Visual Basic Editor). In the left upper pane, it will say "VBAProject (nameofbook.xls)". Right click on that and go to Insert > Module. Open the new module (probably Module1) and paste in the following code:

Public Function ExtractElement(str, n, sepChar)
'   Returns the nth element from a string,
'   using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If n > 0 And n - 1 <= UBound(x) Then
       ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function

=ExtractElement(INDEX(Sheet1!$A$1:$A$3,COLUMN()),ROW(),",")

1/25/2007 9:42:06 AM

Java is so kludgy for text manipulation. perl ftw



also, i would have used matlab.

1/25/2007 9:54:35 AM

for anyone interested...

while (<> {
  chomp;
  @line = split /\,/;
  $oldcol = $prevcol;
  $prevcol = $#line if $#line > $prevcol;
  for (my $i=$oldcol; $i < $prevcol; $i++) {
    $output[$i] = "," x $oldcol;
  }
  for (my $i=0; $i <=$prevcol; $i++) {
    $output[$i] .= "$line[$i],"
  }
}

$fileout = "tr_input\.csv";
open(FILEOUT,"> $fileout") || die "can not open file: $!\n";
for (my $i=0; $i <= $prevcol; $i++) {
  $output[$i] =~ s/\s*$//g;
  print FILEOUT $output[$i]."\n";
}
close FILEOUT;

1/25/2007 11:54:55 AM

Quote :

"LimpyNuts, philihp, Thanks for the correction, I can go on living now."

Hey now. I said it before they did. If anyone should get the annoying prick award here, it should be me.

1/25/2007 12:20:55 PM

Kaltofen would be proud.

1/25/2007 3:45:50 PM

Philihp, you need to include what variables by which to rotate, i.e. with the BY statement.

Noob.

1/27/2007 3:28:28 AM

^ In this case, I don't think you will need a by statement, i could be wrong though considering its been a while since I used proc transpose.

data crap;
input x y z;
cards;
1 2 3
4 5 6
7 8 9
;
run;

proc transpose data=crap
out=trans;
run;

1/27/2007 8:25:17 AM

Hmm, I guess you don't.

1/27/2007 2:14:09 PM