Import and Split CSV

[mpeplow]

I need to import a csv file into excel but the file is way too big to load onto a single speadsheet. How would have excel import the file, then once a sheet is full, create a new sheet and continue the import?

[Chip Pearson]

Try the following code:

Sub AAA()

Dim FNum As Integer
Dim RowNdx As Long
Dim FName As String
Dim V As Variant
Dim WS As Worksheet
Dim ColNdx As Long
Dim Arr As Variant

FNum = FreeFile
FName = "H:\Temp2\Test.txt"
RowNdx = 1
Set WS = Worksheets(1)
Open FName For Input As FNum
Do Until EOF(FNum)
Line Input #1, V
Arr = Split(V, ",")
For ColNdx = 0 To UBound(Arr)
WS.Cells(RowNdx, ColNdx + 1) = Arr(ColNdx)
Next ColNdx
ColNdx = 1
RowNdx = RowNdx + 1
If RowNdx >= Rows.Count Then
RowNdx = 1
With ActiveWorkbook.Worksheets
Set WS = .Add(after:=.Item(.Count))
End With
End If
Loop

Close FNum
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"mpeplow" <[email protected]>
wrote in message
news:[email protected]...
>
> I need to import a csv file into excel but the file is way too
> big to
> load onto a single speadsheet. How would have excel import the
> file,
> then once a sheet is full, create a new sheet and continue the
> import?
>
>
>
> --
> mpeplow
> ------------------------------------------------------------------------
> mpeplow's Profile:
> http://www.excelforum.com/member.php...o&userid=34812
> View this thread:
> http://www.excelforum.com/showthread...hreadid=545694
>

[mpeplow]

The code seems to work but after running for it about 20 mins I tried to ctrl+break the sub routine to see if it was working and excel crashed. Should have mention before this is a very large CSV/text file. I think over 8 MEGS. Any suggestions?