Opening CSV file with 1000 values per line in execl

joecon

New Member
Joined
Sep 7, 2007
Messages
2
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Needs a macro like this
Code:
'===========================================================
'- 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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
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

New Member
Joined
Sep 7, 2007
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top