Transpose a .csv from columns to rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Thanks Tom! Worked like a charm.

Tom Ogilvy said:
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
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top