Import and transpose a large CSV in Excel

[morvi]

Hi

I'm sure I am not the first one with this problem, but could not find any solution.

I have a text file with far to many columns for excel.

Is there a way to transpose the columns to rows while or before importing?

Should I try with Access ?

Any pointers are welcome!

Thanks in advance.

 

[Legacy 98055]

Do you have to open the entire CSV file? Do you only need certain data from it? You can query a CSV file without having to open it. Post a few sample line, including headers (if any), and I'll post an example. Also include information on the queries you will need to run against this data. Or at least the fields you actually need to import...

 

[morvi]

Thanks from the prompt reply.

I have a several lines CSV each with 2700+ columns.
(These are 4 weeks server load statistics per 15 minutes).

First line are dates and times

server,25/08/06 14:15,25/08/06 14:30,25/08/06 14:45

Other lines are server load , users online etc...

Load,3.06,3.06,3.34,3.41,3.17,2.66,2.72,2.69,
...
...

If I want to see the progress in a month I will need to import all the Columns of the CSV file as rows in excel.

The only alternative I can find is to copy and paste in a text editor and manually replace comas by end of line and this per each row (ouff!).

 

[Legacy 98055]

This is a rough draft. However, it should get us started. This code simply reads in your scv file and transposes all of the data to rows within a single column. Download the example, click on 'GetData', browse for your csv file, and there you have it. If it needs refinemen, just post back and somebody will help you improve it.

TransposeCSV.zip

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> GetCsv()
       <font color="#0000A0">Dim</font> FileName <font color="#0000A0">As</font> String, fso <font color="#0000A0">As</font> Object, Data <font color="#0000A0">As</font> <font color="#0000A0">String</font>
       FileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
       <font color="#0000A0">If</font> FileName = "False" <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>

       <font color="#0000A0">Set</font> fso = CreateObject("Scripting.FileSystemObject")

       <font color="#0000A0">With</font> fso.GetFile(FileName).OpenAsTextStream
           <font color="#0000A0">Do</font> <font color="#0000A0">Until</font> .AtEndOfStream
               Data = Data & .ReadLine & ","
           <font color="#0000A0">Loop</font>
           .Close
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>

       <font color="#0000A0">Call</font> SplitToRange(Data, [a1])
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> SplitToRange(Data <font color="#0000A0">As</font> String, FirstCell <font color="#0000A0">As</font> Range)
       <font color="#0000A0">Dim</font> DataArray() <font color="#0000A0">As</font> String, a, b, c

       DataArray = Split(Data, ",")

       FirstCell.Resize(UBound(DataArray) + 1).Value = Application.WorksheetFunction.Transpose(DataArray)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("922200604834437").value=document.all("922200604834437").value.replace(/<br \/>\s\s/g,"");document.all("922200604834437").value=document.all("922200604834437").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("922200604834437").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="922200604834437" wrap="virtual">
Sub GetCsv()
Dim FileName As String, fso As Object, Data As String
FileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
If FileName = "False" Then Exit Sub

Set fso = CreateObject("Scripting.FileSystemObject")

With fso.GetFile(FileName).OpenAsTextStream
Do Until .AtEndOfStream
Data = Data & .ReadLine & ","
Loop
.Close
End With

Call SplitToRange(Data, [a1])
End Sub

Sub SplitToRange(Data As String, FirstCell As Range)
Dim DataArray() As String, a, b, c

DataArray = Split(Data, ",")

FirstCell.Resize(UBound(DataArray) + 1).Value = Application.WorksheetFunction.Transpose(DataArray)
End Sub</textarea>

TransposeCSV.zip

 

[morvi]

Thanks a lot for this - is really going to help

Yes it works brilliantly.
It makes all the columns from the CSV to rows in Excel.

I just have to move to Excel columns each of the rows of the original CSV file;

Original CSV
row1,a1,a2,a3,a4,..
row2,b1,b2,b3,b4,..
row3,c1,c2,c3,c4,..

After your solution I get this in Excel (all in column A)

row1
a1
a2
a3
a4

row2
b1
b2
b3
b4

row3
c1
c2
c3
c4


That I can reorganise in Excel columns as

row1  row2     row3
a1      b1      c1
a2      b2      c2
a3      b3      c3
a4      b4      c4

Thanks a lot TOM

Mauricio

 

[Legacy 98055]

Glad it works for you. This edit will place each row from your CSV file into a separate column.

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> GetCsv()
       <font color="#0000A0">Dim</font> FileName <font color="#0000A0">As</font> String, fso <font color="#0000A0">As</font> Object, DestinationCell <font color="#0000A0">As</font> Range
       FileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
       <font color="#0000A0">If</font> FileName = "False" <font color="#0000A0">Then</font> <font color="#0000A0">Exit</font> <font color="#0000A0">Sub</font>

       <font color="#0000A0">Set</font> fso = CreateObject("Scripting.FileSystemObject")
       <font color="#0000A0">Set</font> DestinationCell = [a1]

       <font color="#0000A0">With</font> fso.GetFile(FileName).OpenAsTextStream
           <font color="#0000A0">Do</font> <font color="#0000A0">Until</font> .AtEndOfStream
               <font color="#0000A0">Call</font> SplitToRange(.ReadLine, DestinationCell)
               <font color="#0000A0">Set</font> DestinationCell = DestinationCell.Offset(, 1)
           <font color="#0000A0">Loop</font>
           .Close
       <font color="#0000A0">End</font> <font color="#0000A0">With</font>

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> SplitToRange(Data <font color="#0000A0">As</font> String, DestinationCell <font color="#0000A0">As</font> Range)
       <font color="#0000A0">Dim</font> DataArray() <font color="#0000A0">As</font> String, a, b, c

       DataArray = Split(Data, ",")

       DestinationCell.Resize(UBound(DataArray) + 1).Value = Application.WorksheetFunction.Transpose(DataArray)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("922200618352578").value=document.all("922200618352578").value.replace(/<br \/>\s\s/g,"");document.all("922200618352578").value=document.all("922200618352578").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("922200618352578").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="922200618352578" wrap="virtual">
Sub GetCsv()
Dim FileName As String, fso As Object, DestinationCell As Range
FileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
If FileName = "False" Then Exit Sub

Set fso = CreateObject("Scripting.FileSystemObject")
Set DestinationCell = [a1]

With fso.GetFile(FileName).OpenAsTextStream
Do Until .AtEndOfStream
Call SplitToRange(.ReadLine, DestinationCell)
Set DestinationCell = DestinationCell.Offset(, 1)
Loop
.Close
End With

End Sub

Sub SplitToRange(Data As String, DestinationCell As Range)
Dim DataArray() As String, a, b, c

DataArray = Split(Data, ",")

DestinationCell.Resize(UBound(DataArray) + 1).Value = Application.WorksheetFunction.Transpose(DataArray)
End Sub</textarea>

 

[morvi]

It works exactly as I wanted

U r brilliant Tom

Thanks!

Mauricio

 

[claudiuCR]

Re: It works exactly as I wanted

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

 

[par60056]

Re: It works exactly as I wanted

When you export the data from your survey software, do you have any choice on what it will use as a field separator? If you can use TABs or |s then you can use the code by changing the character in the call to split. If you have no choice on the field separator character then you have to write a function to either split it paying attention to if you are inside quoted text or at a minimum replace the ',' with another character and then call split to break it up.


I wrote a simple function to swap the field separator. It will not be the fastest but it should work ok for a reasonable dataset.

include this function after the "End Sub"

Function swapFieldSep(iStr As String, oldFS As String, newFS As String) As String


Dim cPos As Long
Dim inQuote As Boolean




inQuote = False
For cPos = 1 To Len(iStr)
    If (Mid(iStr, cPos, 1) = """") Then
        inQuote = Not inQuote
    ElseIf (Not inQuote) Then
        If (Mid(iStr, cPos, Len(oldFS)) = oldFS) Then
            iStr = Left(iStr, cPos - 1) & newFS & Mid(iStr, cPos + Len(oldFS), Len(iStr))
        End If
    End If
Next
swapFieldSep = iStr
End Function

Then change the line:
DataArray = Split(Data, ",")
to
DataArray = Split(swapFieldSep(Data,",","|"), "|")


That should work.

BTW Welcome to the forum

 

[claudiuCR]

Thank you very much for the function.

As I said I could do that changing the php code of my csv exporter script. But I was really curious how it can be done in a macro, and hats down, works like a charm although I don't understand a bit of that code. Ok, is an if else if function but the rest is ancient writing to me.
I can do php, javascript, c++ and java programming but I don't understand any of this macro VBA, maybe is something that I should add on my "to learn" list.

Thank you again for your quick response and solution.