Transpose csv file in Excel

[Claudius]

Hi friends,

I want to import a CSV file where i am having near about 5000 stocks with 150 days of price for each stock. This CSV files is being created columns which consists of Stocks and the rows consists of Price and this won't fit into 252 column of spreadsheet. So can anyone tell me how can i import the CSV file into excel and transpose it . I have tried a lot to find the solution fro it but was not able to get any solution. Does anyone know how to do it.

[Raine]

According to me it's some what simple but very tedious and time consuming if not done in a proper way and at the same time it also depends on what data are you working with. If you are working with numbers only , and you are CERTAIN that your data will not contain any commas, then you can use the String.Split() method, but it is based upon charrray of delimeters that you feed it. Therefore it is easy to input a csv file into an array, and then create some loops to transpose that array. When Microsoft excel creates a CSV file, it places parentheses around all the data that contains a comma. That way, it will know which commas are the delimeters, and which commas are a part of the text. the String.Split() method does not take this into account.

[PinkShell]

I don't think so that there is any need of String.Split() method,instead of that you need to first import .csv file to Access and then import it to Excel 2007 in a correct format because in this case you need to write a very simple program for it. At the same time i need to some help can anyone provide me a code where i can transpose a .csv from columns to rows because i have try a lot to do it but was not able to transpose it.

[chroma]

Use the following code to transpose .csv from columns to rows:-

Sub XYZ()
Dim f1 As Long, s1 As String
Dim l As String, rws as Long
Dim ub as Long, i as Long, lb as Long
Dim bk as Workbook
Set bk = Workbooks.Add(xlWBATWork***)
s1 = "C:\Data\testcsv1.csv"
i = 1
f1 = FreeFile()
Open s For Input As #f1
Do While Not EOF(f1)
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)
i = i+ 1
Loop
Close #f1
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\testcsv_trans.csv", xlCSV
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
End Sub