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.

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" wrote in message
...
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" wrote in message
...
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.