Transpose a .csv from columns to rows

I have a .csv file that has around 700 columns, and 20 rows. I need to
transpose this and write out to a new .csv file so I can fit it into Excel,
resulting in 20 columns by 700 rows. Here is an example:

Read In:
CustID, Age, Zip, Gender
1,45,90210, M
2,30,44853,M
3,50,23456,F
4,20,23499,F

And output:
CustId,1,2,3,4
Age,45,30,50,20
Zip,90210,44853,23456,23499
Gender,M,M,F,F

Thanks.

Select all of your data, use Copy
choose where you want to place a the transposed data
The use Edit | Paste Special and click the [Transpose] box
delete what you don't want and save with a new file name.

"Chris" wrote:

> I have a .csv file that has around 700 columns, and 20 rows. I need to
> transpose this and write out to a new .csv file so I can fit it into Excel,
> resulting in 20 columns by 700 rows. Here is an example:
>
> Read In:
> CustID, Age, Zip, Gender
> 1,45,90210, M
> 2,30,44853,M
> 3,50,23456,F
> 4,20,23499,F
>
> And output:
> CustId,1,2,3,4
> Age,45,30,50,20
> Zip,90210,44853,23456,23499
> Gender,M,M,F,F
>
> Thanks.

Thanks for the reply, but as I said I have around 700 columns of data, which
won't fit into Excel (max columns = 256), so the "Paste Special"->Transpose
won't work.

"JLatham" wrote:

> Select all of your data, use Copy
> choose where you want to place a the transposed data
> The use Edit | Paste Special and click the [Transpose] box
> delete what you don't want and save with a new file name.
>
> "Chris" wrote:
>
> > I have a .csv file that has around 700 columns, and 20 rows. I need to
> > transpose this and write out to a new .csv file so I can fit it into Excel,
> > resulting in 20 columns by 700 rows. Here is an example:
> >
> > Read In:
> > CustID, Age, Zip, Gender
> > 1,45,90210, M
> > 2,30,44853,M
> > 3,50,23456,F
> > 4,20,23499,F
> >
> > And output:
> > CustId,1,2,3,4
> > Age,45,30,50,20
> > Zip,90210,44853,23456,23499
> > Gender,M,M,F,F
> >
> > Thanks.

I got stupid on 2 levels: didn't realize the size of the list and should have
paid attention to the fact that you'd put the question up in coding section
anyhow. Hopefully the more observant Tom Ogilvy's suggesting helped you like
you really needed.

"Chris" wrote:

> Thanks for the reply, but as I said I have around 700 columns of data, which
> won't fit into Excel (max columns = 256), so the "Paste Special"->Transpose
> won't work.
>
> "JLatham" wrote:
>
> > Select all of your data, use Copy
> > choose where you want to place a the transposed data
> > The use Edit | Paste Special and click the [Transpose] box
> > delete what you don't want and save with a new file name.
> >
> > "Chris" wrote:
> >
> > > I have a .csv file that has around 700 columns, and 20 rows. I need to
> > > transpose this and write out to a new .csv file so I can fit it into Excel,
> > > resulting in 20 columns by 700 rows. Here is an example:
> > >
> > > Read In:
> > > CustID, Age, Zip, Gender
> > > 1,45,90210, M
> > > 2,30,44853,M
> > > 3,50,23456,F
> > > 4,20,23499,F
> > >
> > > And output:
> > > CustId,1,2,3,4
> > > Age,45,30,50,20
> > > Zip,90210,44853,23456,23499
> > > Gender,M,M,F,F
> > >
> > > Thanks.

Sorry to revive such an old thread. I used this code but I bumped into a problem.

So, I use this code to transpose a csv which has more than 900 columns. The csv data comes from my survey software I use for my master thesis research and since I have text fields comma is very popular in those fields and I end up with a messed up excel file.

I have 0 experience with macros and the visual basic part of the programming. Of course being just for my master I can enter and modify the answers searching for commas in strings, or I can modify the php exporter changing the delimiter symbol to something else than comma. But just for the sake of learning I am curious how this script can be adapted to split a string like:
"In weekends I watch movies, go outside, read books" , "2" , "Another string, with commas" , "1,5"

Mainly this is how my csv looks like so I got lot of commas in the strings and with this script I get nasty outcome. And I use Excel 2003, yeah old ... but since I am no user of any of the microsoft office programs on a daily basis I see no reasons having the last one just for my master thesis.

Thank you for any help, I am just curious how can be solved within the macro because I am having hard time understanding this programming language

Sub ABC()
Dim ff As Long, s As String
Dim l As String, rws as Long
Dim ub as Long, j as Long, lb as Long
Dim bk as Workbook
Set bk = Workbooks.Add(xlWBATWorksheet)
s = "C:\Data\testcsv.csv"
j = 1
ff = FreeFile()
Open s For Input As #ff
Do While Not EOF(ff)
Line Input #1, l
v = Split(l, ",")
ub = UBound(v)
lb = LBound(v)
rws = ub - lb + 1
bk.Worksheets(1).Cells(1, j) _
.Resize(rws, 1).Value = _
Application.Transpose(v)
j = j + 1
Loop
Close #ff
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\testcsv_trans.csv", xlCSV
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
End Sub

--
Regards,
Tom Ogilvy

"Chris" <Chris@discussions.microsoft.com> wrote in message
news:2CCA50D9-9A27-4FBE-81C1-C2B00DEDE83F@microsoft.com...
> I have a .csv file that has around 700 columns, and 20 rows. I need to
> transpose this and write out to a new .csv file so I can fit it into
Excel,
> resulting in 20 columns by 700 rows. Here is an example:
>
> Read In:
> CustID, Age, Zip, Gender
> 1,45,90210, M
> 2,30,44853,M
> 3,50,23456,F
> 4,20,23499,F
>
> And output:
> CustId,1,2,3,4
> Age,45,30,50,20
> Zip,90210,44853,23456,23499
> Gender,M,M,F,F
>
> Thanks.

Thanks Tom! Worked like a charm.

"Tom Ogilvy" wrote:

> Sub ABC()
> Dim ff As Long, s As String
> Dim l As String, rws as Long
> Dim ub as Long, j as Long, lb as Long
> Dim bk as Workbook
> Set bk = Workbooks.Add(xlWBATWorksheet)
> s = "C:\Data\testcsv.csv"
> j = 1
> ff = FreeFile()
> Open s For Input As #ff
> Do While Not EOF(ff)
> Line Input #1, l
> v = Split(l, ",")
> ub = UBound(v)
> lb = LBound(v)
> rws = ub - lb + 1
> bk.Worksheets(1).Cells(1, j) _
> .Resize(rws, 1).Value = _
> Application.Transpose(v)
> j = j + 1
> Loop
> Close #ff
> Application.DisplayAlerts = False
> ActiveWorkbook.SaveAs "C:\testcsv_trans.csv", xlCSV
> Application.DisplayAlerts = True
> ActiveWorkbook.Close SaveChanges:=False
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Chris" <Chris@discussions.microsoft.com> wrote in message
> news:2CCA50D9-9A27-4FBE-81C1-C2B00DEDE83F@microsoft.com...
> > I have a .csv file that has around 700 columns, and 20 rows. I need to
> > transpose this and write out to a new .csv file so I can fit it into
> Excel,
> > resulting in 20 columns by 700 rows. Here is an example:
> >
> > Read In:
> > CustID, Age, Zip, Gender
> > 1,45,90210, M
> > 2,30,44853,M
> > 3,50,23456,F
> > 4,20,23499,F
> >
> > And output:
> > CustId,1,2,3,4
> > Age,45,30,50,20
> > Zip,90210,44853,23456,23499
> > Gender,M,M,F,F
> >
> > Thanks.
>
>
>