Transpose a .csv
 

I have a .csv file that I would like to read in, transpose from rows to
columns, and write out to another .csv. 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

Is this possible?

Transpose a .csv
 

Hi Chris,

Chris wrote:
I have a .csv file that I would like to read in, transpose from rows
to columns, and write out to another .csv. example:

Is this possible?

Here's some code that should do what you're looking for:

Sub demo()
Dim wb As Workbook
Dim vData As Variant

Set wb = Workbooks.Open("c:\test.csv")

With wb.Sheets(1)
vData = .UsedRange.Value
vData = Application.Transpose(vData)
.UsedRange.Delete
.Cells(1, 1).Resize(UBound(vData, 1), _
UBound(vData, 2)).Value = vData
End With

wb.SaveAs "c:\test_out.csv", xlCSV

wb.Close False
Set wb = Nothing
End Sub

You'll have to change the paths in the above code to match your situation.
Also, there are some size limitations on the TRANSPOSE worksheet function
(invoked via Application.Transpose), but I don't recall what they are. If
you run into problems with errors or truncation of data, that would be one
thing to check.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Transpose a .csv
 

Hi Jake -
Thanks for your reply. I actually tried that out but it seems the
limitation of Transpose is 256 variables. My data set has around 700, which
is why I need to transpose it in the first place, to fit into Excel.

"Jake Marx" wrote:

Hi Chris,

Chris wrote:
I have a .csv file that I would like to read in, transpose from rows
to columns, and write out to another .csv. example:

Is this possible?

Here's some code that should do what you're looking for:

Sub demo()
Dim wb As Workbook
Dim vData As Variant

Set wb = Workbooks.Open("c:\test.csv")

With wb.Sheets(1)
vData = .UsedRange.Value
vData = Application.Transpose(vData)
.UsedRange.Delete
.Cells(1, 1).Resize(UBound(vData, 1), _
UBound(vData, 2)).Value = vData
End With

wb.SaveAs "c:\test_out.csv", xlCSV

wb.Close False
Set wb = Nothing
End Sub

You'll have to change the paths in the above code to match your situation.
Also, there are some size limitations on the TRANSPOSE worksheet function
(invoked via Application.Transpose), but I don't recall what they are. If
you run into problems with errors or truncation of data, that would be one
thing to check.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Transpose a .csv
 

Hi Chris,

Chris wrote:
Thanks for your reply. I actually tried that out but it seems the
limitation of Transpose is 256 variables. My data set has around
700, which is why I need to transpose it in the first place, to fit
into Excel.

I'm not sure that I understand what you're trying to do. Are you saying you
have 700 fields and less than 256 records? So "CustID, Age, Zip, Gender"
was just an example, and you actually have 700 fields? Or do you have 4
fields and 700 records? I'm guessing you have 700 fields and less than 256
records, as otherwise the data would fit on an Excel Worksheet just fine
without transposing. If you have 700 records and 4 fields, the code I
provided won't work, as the transposed array won't fit on a Worksheet (but
I'm not sure why you'd need to transpose it anyway).

I believe the limitation of Transpose is more than 256 elements:
http://support.microsoft.com/kb/q177991/. However, if you have more than
256 fields, then obviously opening the CSV in Excel and then transposing
won't work, as you don't have enough columns. You could definitely do it
all in memory - open the CSV using ADO or standard file I/O (see Open
statement in help), then transpose it, then write the resulting array to a
range and save as CSV. Or skip the Worksheet altogether and just write the
array back out to a file using file I/O.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Transpose a .csv
 

Hi Chris, I read your posts last week but didn't find much time to reply.

Anyhow, I've put together a solution using VBA (since I didn't want to
assume you've got other dev tools available) and this piece of code uses
standard file I/O exclusively. It should handle all CSV files but I don't
guarantee that the code is bug free :o) Paste the source code into e.g. a
standard code module, or perhaps the ThisWorkbook module.

I haven't added many comments but I'm sure you can figure out what's going
on simply by single-stepping the code. There is some very basic attempt at
error handling as well, that should at least make you aware of any errors
that may occur.

Feel free to get back with questions and/or comments...

Cheers,
/MP

================================================== =

Option Explicit

' TODO: Define your file paths here (input file, output file)
Private Const PathNameIn As String = "C:\tmp\Test.csv"
Private Const PathNameOut As String = "C:\tmp\TestTrans.csv"

Private g_nFileHandleIn As Integer
Private g_nFileHandleOut As Integer
Private g_oRowSet As Collection

' Main method, includes some pretty
' rudimentary error handling as well
Public Sub Transpose()

On Error GoTo ErrorHandler

If Not OpenFiles Then GoTo Finalize
If Not ReadFile Then GoTo Finalize
If Not WriteFile Then GoTo Finalize

Finalize:
On Error GoTo 0
CloseFiles
Exit Sub

ErrorHandler:
MsgBox _
"Error (" & CStr(Err.Number) & ") : " & Err.Description, _
vbOKOnly, _
"Transpose Error"

Resume Finalize

End Sub

' Open input and output files, return true if okay
Private Function OpenFiles() As Boolean
g_nFileHandleIn = FreeFile(0)
Open PathNameIn For Input Access Read Shared As #g_nFileHandleIn
If g_nFileHandleIn = 0 Then Return

g_nFileHandleOut = FreeFile(0)
Open PathNameOut For Output Access Write Lock Write As #g_nFileHandleOut
If g_nFileHandleOut = 0 Then Return

OpenFiles = True
End Function

' Close input and output file, return true if okay (always)
Private Function CloseFiles() As Boolean
If (g_nFileHandleIn 0) Then
Close #g_nFileHandleIn
g_nFileHandleIn = 0
End If

If (g_nFileHandleOut 0) Then
Close #g_nFileHandleOut
g_nFileHandleOut = 0
End If

If Not g_oRowSet Is Nothing Then
Set g_oRowSet = Nothing
End If

CloseFiles = True
End Function

' Read line by line of input CSV,
' add data to global collection
Private Function ReadFile() As Boolean
Dim sLineBuffer As String
Dim vFields As Variant

Set g_oRowSet = New Collection

Do While Not EOF(g_nFileHandleIn)
Line Input #g_nFileHandleIn, sLineBuffer
vFields = Split(sLineBuffer, ",")
g_oRowSet.Add vFields
Loop

ReadFile = True
End Function

' Write line by line to new CSV,
' but with transposed rows/cols
Private Function WriteFile() As Boolean
Dim sLineBuffer As String
Dim vFields As Variant
Dim nLength As Integer
Dim f As Integer

For f = LBound(g_oRowSet(1)) To UBound(g_oRowSet(1))
sLineBuffer = ""
For Each vFields In g_oRowSet
sLineBuffer = sLineBuffer & vFields(f) & ","
Next vFields
sLineBuffer = Mid$(sLineBuffer, 1, Len(sLineBuffer) - 1)
Write #g_nFileHandleOut, sLineBuffer
Next f

WriteFile = True
End Function

================================================== =


"Chris" wrote:

Hi Jake -
Thanks for your reply. I actually tried that out but it seems the
limitation of Transpose is 256 variables. My data set has around 700, which
is why I need to transpose it in the first place, to fit into Excel.

"Jake Marx" wrote:

Hi Chris,

Chris wrote:
I have a .csv file that I would like to read in, transpose from rows
to columns, and write out to another .csv. example:

Is this possible?

Here's some code that should do what you're looking for:

Sub demo()
Dim wb As Workbook
Dim vData As Variant

Set wb = Workbooks.Open("c:\test.csv")

With wb.Sheets(1)
vData = .UsedRange.Value
vData = Application.Transpose(vData)
.UsedRange.Delete
.Cells(1, 1).Resize(UBound(vData, 1), _
UBound(vData, 2)).Value = vData
End With

wb.SaveAs "c:\test_out.csv", xlCSV

wb.Close False
Set wb = Nothing
End Sub

You'll have to change the paths in the above code to match your situation.
Also, there are some size limitations on the TRANSPOSE worksheet function
(invoked via Application.Transpose), but I don't recall what they are. If
you run into problems with errors or truncation of data, that would be one
thing to check.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]