Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Import Large Files (Excel 2002)

    Hi

    I would like to use the code below to import a large file into Excel however this code is for importing a .csv file and my file is a .txt file, is there anyway this code can be adapted to import a .txt file

    Many Thanks

    Braddy

    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.

    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
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Large Files (Excel 2002)

    What is the layout of the text file?
    - Does it contain columns of fixed width (padded with spaces)?
    or
    - Are the data separated by a delimiter, such as a comma or tab? If so, what is the delimiter?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import Large Files (Excel 2002)

    Hi Hans

    How can I check the things you are asking.

    Have attached a small sample of the file if this helps.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import Large Files (Excel 2002)

    Thanks, it's a tab-delimited file. You'll have to specify that most of the columns are text, to preserve the formatting.

    In the code as posted in the first post in this thread, change the lines

    <code>.Columns("a:a").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, Comma:=True</code>

    to

    <code>.Columns("A:A").TextToColumns DataType:=xlDelimited, Tab:=True, _
    FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), _
    Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 1), Array(10, 1))</code>

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Import Large Files (Excel 2002)

    Hi Hans

    Thank you very much for your help on this, I am not in a position to try this just yet, I will let you know how I get on, thanks again.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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