Opening CSV file with 1000 values per line in execl

[joecon]

Hi. I have a CSV file (the output of test machine) which has 1000 values per line (numeric & text) with line length of 10,000 characters. There will be between 1 & 50 lines in each file. If I can transpose the rows & columns then excel will handle the data. Is there a way within excel to do this at load time, do I need VB code, or an external application to do this transposation?

Thanks in advance, Joe.

 

[BrianB]

Needs a macro like this

'===========================================================
'- IMPORT .CSV FILE INTO EXCEL TRANPOSING ROWS TO COLUMNS
'- Brian Baulsom September 2007
'===========================================================
Sub TransposeImport()
    Dim ws As Worksheet
    Dim MyCSV As String
    Dim TextLine As String
    Dim ToRow As Long
    Dim ToCol As Integer
    Dim MyChar As String            ' single character
    Dim MyString As String          ' Text field data
    '-------------------------------------------------------
    Application.Calculation = xlCalculationManual
    '- INITIALISE VARIABLES
    Set ws = Worksheets("Transpose")
    ws.Cells.ClearContents                ' clear worksheet
    MyCSV = "F:\XL_MACROS\TransposeText.csv"
    ToCol = 1
    '-------------------------------------------------------
    '- TEXT FILE ROWS
    Open MyCSV For Input As #1      ' Open file
    '- MAIN LOOP
    Do While Not EOF(1)             ' Check for end of file.
        Application.StatusBar = " Processing line : " & ToCol
        Line Input #1, TextLine     ' Read line of data.
        MyString = ""
        ToRow = 1
        '----------------------------------------------------
        '- loop to parse line of text -> column
        For c = 1 To Len(TextLine)
            MyChar = Mid(TextLine, c, 1)
            If MyChar = "," Then    ' check for comma
                ws.Cells(ToRow, ToCol).Value = MyString
                ToRow = ToRow + 1
                MyString = ""
            Else
                MyString = MyString & MyChar
            End If
        Next
        '------------------------------------------------------
        '- ADD LAST STRING
        ws.Cells(ToRow, ToCol).Value = MyString
        '- NEXT LINE OF TEXT
        ToCol = ToCol + 1
    Loop
    '----------------------------------------------------------
    '- finish
    Close #1    ' Close text file.
    Application.Calculation = xlCalculationAutomatic
    MsgBox ("Done")
    Application.StatusBar = False
End Sub
'======= END OF PROCEDURE =====================================

 

[jindon]

try

Sub test()
Dim fn As String, ff As Integer, txt As String, a(), n As Long
fn = "c:\test.csv"  '<- alter to suite
ff = FreeFile
Open fn For Input As #ff
Do While Not EOF(ff)
     Line Input #ff, txt
     n = n + 1
     ReDim Preserve a(1 To n)
     a(n) = Split(txt,",")
Loop
With ThisWorkbook.Sheets(1).Range("a1")
     For i = 1 To n
          .Offset(,i-1).Resize(UBound(a(i))).Value = Application.Transpose(a(i))
     Next
End With
End Sub

 

[joecon]

Thank you Brian & nijdon for the prompt response.
Both of your solutions meet my requirement exactly. This was my first time using this forum, Great resource.
Many thanks, Joe.