Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Importing Large CSV file into Exel

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hope someone can assist me with this problem?
    I have a large CSV file that i wish to load into Excel, Problem is that there is over 20,000 items and attempting to load it causes Excel to report that the file was not fully loaded, as everything is trying to load into the one Book/

    Any assistance would be greatly appreciated.

    Bails

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Essex, England
    Posts
    459
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What version are you using? 97 should be able to load 65,000 lines without a problem, but I know 4.0 had a limit of about 16,000

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Import it into word first and the copy and paste half at a time into excel

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 00:33, GaryB wrote:
    What version are you using? 97 should be able to load 65,000 lines without a problem, but I know 4.0 had a limit of about 16,000
    I am using version 7 for win 95.
    Will give "Nehpets12" hint a try.
    Thanks..Bails
    PS Keep watching as I may still require guidance.....

  5. #5
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this but first check how many rows you actually have this code is set to 65536 alter it if need be.

    You need to add the code to a module in your workbook.

    If you run the code you will be shown an Input box which will allow you to select the file you want.

    It imports your text and if it reaches the row 65536 it creates a new worksheet.

    Hope it helps

    Kev

    PS it's not my code but I can't remember the author

    Sub ImportBigText()
    Dim ResultStr As String
    Dim FileName As String
    Dim FileNum As Integer
    Dim Counter As Double
    FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
    'If Error Then End
    If FileName = "" Then End
    FileNum = FreeFile()
    Open FileName For Input As #FileNum
    Application.ScreenUpdating = False

    Workbooks.Add template:=xlWorksheet
    Counter = 1
    Do While Seek(FileNum) < LOF(FileNum)

    Application.StatusBar = "Importing Row " & _
    Counter & " of text file " & FileName
    Line Input #FileNum, ResultStr
    If Left(ResultStr, 1) = "=" Then
    ActiveCell.Value = "'" & ResultStr

    Else
    ActiveCell.Value = ResultStr
    End If

    If ActiveCell.Row = 65536 Then
    ActiveWorkbook.Sheets.Add
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Counter = Counter + 1
    Loop

    Close
    Application.StatusBar = False

    End
    End Sub


    [ This Message was edited by: swaink on 2002-04-18 05:18 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to you guys.
    Problem solved.
    Now the Happy owner of an Excel workbook and a redundant CSV File.
    Thanks again....Bails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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