Converting LARGE text files to excel

**baldkat82** · 06-11-2009, 02:42 PM

I am lookign for a way to convert text files with over 65000 lines of data to excel in a manner that would split the data to multiple worksheets. Does anybody know of a way to do this? Preferably a free way, but i'm open to other ideas.

**mrice** · 06-11-2009, 03:04 PM

This macro should do it, albeit slowly.

Code:

Sub Test()
Open "C:\temp\mytextfile.txt" For Input As #1
Do While Not EOF(1)
    Counter = Counter + 1
    Line Input #1, FileLine
    ActiveSheet.Cells(Counter, 1) = FileLine
    If Counter = 65000 Then
        Counter = 0
        Sheets.Add
    End If
Loop
Close #1
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

You will need to edit the path\filename.

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**baldkat82** · 06-11-2009, 03:40 PM

Ok, it's splittign the data into multiple sheets. That is great. However, it's not putting the data into separate cells. It's keeping each line separate, but it looks like the separation from one field to the next is being held with the small box character.

The .txt file I'm trying to convert has blanks between data fields. There is no separator character. Is that part of the problem? It appears to be Tab-delimited.

**mrice** · 06-11-2009, 05:27 PM

You didn't mention how the lines were formatted. If there are not too many sheets, you could manually use the text to columns option to split.

Otherwise, please post a small segment of the text file to allow for some experimentation.

**baldkat82** · 06-12-2009, 01:21 PM

Here is a small clip of the file format i am trying to convert. The software I use appears to only export text in this format.

**martindwilson** · 06-12-2009, 03:16 PM

ok i cobbled together this from above code
it just uses the text to columns and autofit. i couldnt get beyond18 sheets without running out of memory tho (with either code)
martin rice might have a better solution though

Code:

Sub Test()
Application.ScreenUpdating = False
Open "C:\Documents and Settings\Martin Wilson\Desktop\test[1].txt" For Input As #1
Do While Not EOF(1)
    Counter = Counter + 1
    Line Input #1, FileLine
    ActiveSheet.Cells(Counter, 1) = FileLine
    If Counter = 65000 Then
      Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1))
   Cells.Select
    Cells.EntireColumn.AutoFit
        Counter = 0
        Sheets.Add
    End If
Loop
Close #1
  Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1))
   Cells.Select
    Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub