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

1. ## 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. ## 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. ## 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
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
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
•