importing large files into excel

[rraajjiibb]

I am trying to import contents of a file into the excel . My file's no of row contents are more to surpass the 65536 row limitation of excel.

I am aware of the earlier postings on the board on this.

I have tried both the approaches that of using DAO object or line inputting one by one and then adding an workshet as the row number exceedes 65536.

But none of the approaches seem to solve my problem.

In the DAO approach I have used iget an error at : oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
Stating it as an invalid SQL statement.

Whereas in later case I get a response as out of memory.


I doubt that this has some thing to do with the format of my my data file
My data source when opened with write comes out nicely formatted like this

HOUR 0

ECP 01/08/04 23:00 01/09/04 00:00

ESID 070214655
RELEASE r20.0

ECP CNTS 00000 00036 00000 00000 00000 00000 00000 00000 00000
ECP TAND 000003391 000000000 000001042
ECP ISUP 00000 00000 00000 00000 00000 00000 00000 00000 00005 00000 00000
ECP LCNTS 000000000 000000000 000000000 000121724 000000263 000000000 000000069
000000000 000000000 000002228 000000000 000000001 000000000 000000000
000000000 000001127 000000000 000004304 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000001000 000000000 000000000
000000000 000000000 000000000 000000000 000041119 000016717 000000002
000000012 000000000 000000000 000004571 000000000 000000000 000000000
000007766 000000142 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000167 000000000 000000183 000000000
000000019 000000000 000000000 000000000 000000000 000000000 000032199
000017251 000012745 000010761 000001498 000000392 000000001 000000001
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000001 000000000 000000001 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000000000 000007531
000000313 000005777 000000060 000000000 000000000 000000000 000000000
000000006 000000000 000000000 000000000 000000000 000005386 000000169
000003043 000012679 000005311 000000000 000000023 000000000 000000000
000000223 000000000 000000000 000000000 000000000 000000000 000000000
000000000 000000000 000000000 000000000 000000000 000803592
ECP VSCNTS-8K
00000
ECP VSCNTS-13K
00000
ECP VSCNTS-EVRC
00000
ECP VSCNTS-SMV
00000
ECP P_LANG ATTEMPT
00000 00000 00000 00000 00000 00000 00000 00000 00000 00000 00000
00000 00000 00000 00000 00000 00000 00000 00000 00000 00000 00000
00000 00000 00000 00000 00000 00000 00000 00000 00000 00000 00000
00000 00000 00000 00000 00000 00000 00000 00000 00000 00000 00000
00000 00000 00000 00000 00000 00000 00000 00000 00000 00000 00000
00000 00000 00000 00000 00000 00000 00000
DCCH
DCCH SMS 000000000 000000000 000000000 000000000 000000000 000000000
DTC SMS 000000000 000000000 000000000 000000000 000000000 000000000
DTC MO-SMS
000000000 000000000 000000000 000000000
CDMA PKT DATA
000000241 000000152 000000000 000000000 000000511 000000392 000000000
000000000 000000000 000000000 000000000
CDMA SMS 000008695
A-MODE 000000000 000000000 000000000 000000000 000000000
DP-MODE 000003772 000000259 000000000 000000000 000000000
CT-MODE 000004690 000003565 000000000 000000000 000000000
COACC 000001529 000000031 000000000 000000000 000000000
COTRAF 000017095 000000033 000000001 000000000 000000000
BRDCAST 000000000 000000000 000000000 000000000 000000000
ECP MSCTS 000000000 000000000 000000000 000000000 000087365 000000000 000000000
000000000 000000000 000000000 000000000 000087369 000000000

........


could any one out there pl help?

 

[TommyGun]

Why would the simple I/O method not work? The following worked just fine for me with a text file containing 190k+ rows.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ImportLargeTextFile()
    <SPAN style="color:#00007F">Dim</SPAN> tmpStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> CurrentRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">Const</SPAN> FILEPATH <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C:\LargeText.txt" <SPAN style="color:#007F00">'change to your file</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    
    f = FreeFile
    CurrentRow = 1
    
    <SPAN style="color:#00007F">Open</SPAN> FILEPATH <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Input</SPAN> <SPAN style="color:#00007F">As</SPAN> #f
        
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> EOF(f)
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> (CurrentRow <= ActiveSheet.Rows.Count) <SPAN style="color:#00007F">Then</SPAN>
            ActiveWorkbook.Sheets.Add After:=ActiveSheet
            CurrentRow = 1
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
        <SPAN style="color:#00007F">Input</SPAN> #f, tmpStr
        
        ActiveSheet.Cells(CurrentRow, 1) = tmpStr
        
        CurrentRow = CurrentRow + 1
    <SPAN style="color:#00007F">Loop</SPAN>
    
    <SPAN style="color:#00007F">Close</SPAN> #f

    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[rraajjiibb]

THanks for that

I am unable to get it...
it has still given me error 7 : out of memory
error while executing at line
ActiveSheet.Cells(CurrentRow, 1) = tmpStr

do you want to look at my data file???

 

[TommyGun]

Yup...I'll PM you my email address.

 

[TommyGun]

WOW!!! I opened this file in Word and it was almost 1500 pages long! May I ask why do you need to put this information into XL? What are you hoping to do with it?

Oh, and the reason the code didn't work is because of the way the data was written to the file. It is not written in a line-feed format. I believe it is a true DAT style with records being written vs. lines. Therefore, it was reading the entire file into a single variable, and then trying to dump 4+ million characters into one cell in XL. This is what caused the out of memory error.

 

[rraajjiibb]

Actually this file contains some specific counters which give sus the performance measures of awireless network.
The file used to open in excel earlier when we changed the extensin to .xls .
With the new updated format of the file the file has grown in size and dose't fit into one sheet any more.
That's why we need to open it excel and manipulate out some part of it.

With the current format what should be the forward path..???.

 

[TommyGun]

I'm not sure what version of Office you are running, but here is what I did...I don't really like the method, but it worked.

I opened the file in Word. Once Word finished processing the file, I saved it as a standard Windows TXT file. I was promted if I wanted to add a LineFeed character to the end of the lines. I did this, and then ran the code from XL that I posted earlier. This seemed to work fine. It's not the best method, but it will work (at least on Office 2003).

Let me know if you have any problems.

 

[rraajjiibb]

yeah.. that has worked certainly.

Now going a step further I wanted to automate this business of opening in word and saving in this line feed format.(I gave a thought on alternatives but still nothing has passed through my mind)
I tried in the following way but it gives error :4198 .. any suggestions....
??
Sub ImportLargeTextFile()
Set INWORD = CreateObject("Word.Application")

INWORD.Documents.Open FILENAME:= _
"C:\all_cell_data\smd.040113_07.done.x_FLY-PROCESSED_x.13_01_2004_07_31_16", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto
INWORD.Documents.SaveAs FILENAME:= _
"C:\all_cell_data\smd.040113_07.done.x_FLY-PROCESSED_x.13_01_2004_07_31_16.txt", _
FileFormat:=wdFormatTextLineBreaks, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False

Dim tmpStr As String
Dim f As Integer
Dim CurrentRow As Long

FILEPATH = Application.GetOpenFilename '"C:\all_cell_data\smd.040113_01.done.x_FLY-PROCESSED_x.13_01_2004_01_31_34" 'change to your file

Application.ScreenUpdating = False

f = FreeFile
CurrentRow = 1

Open FILEPATH For Input As #f

Workbooks.Add TEMPLATE:=xlWorksheet
Do While Not EOF(f)
If Not (CurrentRow <= ActiveSheet.Rows.Count) Then
ActiveWorkbook.Sheets.Add After:=ActiveSheet
CurrentRow = 1
End If

Input #f, tmpStr

ActiveSheet.Cells(CurrentRow, 1) = tmpStr

CurrentRow = CurrentRow + 1
Loop

Close #f

Application.ScreenUpdating = True
End Sub

 

[TommyGun]

What line of code is giving the error?

 

[rraajjiibb]

INWORD.Documents.Open FILENAME:= _
"C:\all_cell_data\smd.040113_07.done.x_FLY-PROCESSED_x.13_01_2004_07_31_16", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto

i.e the second line i just now ran it with little change which runs through but converts the file into doc format and so the txt file changes. The output is just ÐÏࡱ--- the first 5 characters of the file.


my lines of code now
Sub ImportLargeTextFile()
Set INWORD = CreateObject("Word.Application")

Set CURRENT = INWORD.Documents.Open("C:\all_cell_data\smd.040113_10.done.x_FLY-PROCESSED_x.13_01_2004_10_32_07")

CURRENT.SaveAs FILENAME:= _
"C:\all_cell_data\smd.040113_10.done.x_FLY-PROCESSED_x.13_01_2004_10_32_07.txt", _
FileFormat:=wdFormatTextLineBreaks, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False
Set INWORD = Nothing
Set CURRENT = Nothing
..........