Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    How long can a .csv file be (Excel xp)

    I use .csv output from another program and open the .csv file in Excel. If their program outputs a .csv file that is longer than 64,000 rows how can i open it in Excel? Will it delete the extra rows? Thank you <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How long can a .csv file be (Excel xp)

    I made a CSV file that had 65536 rows (1 more than excel can read) and the last row was truncated and it caused a runtime error indicating this.

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How long can a .csv file be (Excel xp)

    If you really need to import more than the number of allowable rows, you could import into Access first then pull what you need in multiple passes into excel: some info will have to be in one sheet and the rest in another or you will have to have multiple sets of column data.

    If you want to do it into excel, here is some code to import the data into an array, when the array is full, it dumps it to a new sheet and then continues. Theoretically you are only limited by memory.

    <pre>Option Explicit
    Sub ImportMultSheets()
    Dim wks As Worksheet
    Dim sPathFilename As String
    Dim lRow As Long
    Dim lLimit As Long
    Dim sLine As String
    Dim sArray() As String

    sPathFilename = "C:ImportText.csv"
    lLimit = 65536
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sArray(lRow, 0) = sLine
    lRow = lRow + 1
    If lRow = lLimit + 1 Then
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    ReDim sArray(1 To lLimit, 0)
    lRow = 1
    End If
    Loop
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True
    End With
    Close #1
    Set wks = Nothing
    End Sub</pre>


    Change the filename as appropriate. You could add a routine to ask for the file if desired using GetOpenFileName method. You could aslo change the parameters in the texttocolumns routine if desired to make the parse more custom.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •