Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
Hi all,
I'm having problems finding out why the performance of my text file import routine takes a hit after the first import operation. I have a series of text files with a set format that I'd like to import into individual sheets in excel. I have written a procedure that takes care of this. The first file that I import is extremely fast. As soon as I import the next file, the speed drops to 1/10 of the first time. The speed deteriorates as I import more files, but the drop is more gradual. Here is the import code: Open textFile For Input As #1 'the text file is read line by line Do While Not EOF(1) Line Input #1, TextLine If InStr(TextLine, "POINT") < 0 Then If CheckHeader(Right(TextLine, Len(TextLine) - 13), textFile) = True Then GoTo ExitHandler Else Worksheets("Template").Copy After:=Worksheets("Summary") Worksheets(3).Name = txt_name.Value Worksheets(CStr(txt_name.Value)).Visible = True Sheets(CStr(txt_name.Value)).Activate ActiveSheet.Move Befo=Worksheets("Template") 'the script deletes previous information and replaces it with a new set rowNum = ActiveSheet.Range("Data").Row rowOrigin = rowNum ActiveSheet.Range("A" & (rowOrigin + 1), "A" & (ActiveSheet.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete If Notes.Range("Tbl_Case_Hdr").Row - Notes.Range("Tbl_Case_Ftr").Row = -2 Then Summary.Range("A" & (rowOrigin + 1), "A" & (Summary.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete End If End If ElseIf InStr(TextLine, "element group") < 0 Then 'whenever a new element is declared, the element and group ID are read TextPos = InStr(TextLine, "Element") + 8 TextLen = InStr(TextPos, TextLine, " ") - TextPos elmID = CInt(Right(Left(TextLine, (TextPos + TextLen - 1)), TextLen)) TextPos = InStr(TextLine, "group") + 5 TextLen = Len(TextLine) - TextPos grpID = CInt(Right(TextLine, TextLen)) Else If InStr(TextLine, "node 1") < 0 Then ActiveSheet.Cells(rowNum, 2) = grpID ActiveSheet.Cells(rowNum, 3) = elmID ActiveSheet.Cells(rowNum, 4) = grpID & "-" & elmID values = SplitData(Right(TextLine, Len(TextLine) - 13)) ActiveSheet.Range("V" & rowNum, "AA" & rowNum) = values If Notes.Range("Tbl_Case_Hdr").Row - Notes.Range("Tbl_Case_Ftr").Row = -2 Then Summary.Cells(rowNum, 2) = grpID Summary.Cells(rowNum, 3) = elmID Summary.Cells(rowNum, 4) = grpID & "-" & elmID End If ElseIf InStr(TextLine, "node 2") < 0 Then values = SplitData(Right(TextLine, Len(TextLine) - 13)) ActiveSheet.Range("AC" & rowNum, "AH" & rowNum).Value = values rowNum = rowNum + 1 End If End If 'update the progress bar prg_import.Value = Round((Loc(1) * 12800) / LOF(1), 0) Loop Close #1 Any help is appreciated. If someone wants to take a look at the complete file with sample text files, just let me know. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
Are you turning off screenupdating and setting calculation to manual during
each import ? Tim "Klips" wrote in message oups.com... Hi all, I'm having problems finding out why the performance of my text file import routine takes a hit after the first import operation. I have a series of text files with a set format that I'd like to import into individual sheets in excel. I have written a procedure that takes care of this. The first file that I import is extremely fast. As soon as I import the next file, the speed drops to 1/10 of the first time. The speed deteriorates as I import more files, but the drop is more gradual. Here is the import code: Open textFile For Input As #1 'the text file is read line by line Do While Not EOF(1) Line Input #1, TextLine If InStr(TextLine, "POINT") < 0 Then If CheckHeader(Right(TextLine, Len(TextLine) - 13), textFile) = True Then GoTo ExitHandler Else Worksheets("Template").Copy After:=Worksheets("Summary") Worksheets(3).Name = txt_name.Value Worksheets(CStr(txt_name.Value)).Visible = True Sheets(CStr(txt_name.Value)).Activate ActiveSheet.Move Befo=Worksheets("Template") 'the script deletes previous information and replaces it with a new set rowNum = ActiveSheet.Range("Data").Row rowOrigin = rowNum ActiveSheet.Range("A" & (rowOrigin + 1), "A" & (ActiveSheet.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete If Notes.Range("Tbl_Case_Hdr").Row - Notes.Range("Tbl_Case_Ftr").Row = -2 Then Summary.Range("A" & (rowOrigin + 1), "A" & (Summary.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete End If End If ElseIf InStr(TextLine, "element group") < 0 Then 'whenever a new element is declared, the element and group ID are read TextPos = InStr(TextLine, "Element") + 8 TextLen = InStr(TextPos, TextLine, " ") - TextPos elmID = CInt(Right(Left(TextLine, (TextPos + TextLen - 1)), TextLen)) TextPos = InStr(TextLine, "group") + 5 TextLen = Len(TextLine) - TextPos grpID = CInt(Right(TextLine, TextLen)) Else If InStr(TextLine, "node 1") < 0 Then ActiveSheet.Cells(rowNum, 2) = grpID ActiveSheet.Cells(rowNum, 3) = elmID ActiveSheet.Cells(rowNum, 4) = grpID & "-" & elmID values = SplitData(Right(TextLine, Len(TextLine) - 13)) ActiveSheet.Range("V" & rowNum, "AA" & rowNum) = values If Notes.Range("Tbl_Case_Hdr").Row - Notes.Range("Tbl_Case_Ftr").Row = -2 Then Summary.Cells(rowNum, 2) = grpID Summary.Cells(rowNum, 3) = elmID Summary.Cells(rowNum, 4) = grpID & "-" & elmID End If ElseIf InStr(TextLine, "node 2") < 0 Then values = SplitData(Right(TextLine, Len(TextLine) - 13)) ActiveSheet.Range("AC" & rowNum, "AH" & rowNum).Value = values rowNum = rowNum + 1 End If End If 'update the progress bar prg_import.Value = Round((Loc(1) * 12800) / LOF(1), 0) Loop Close #1 Any help is appreciated. If someone wants to take a look at the complete file with sample text files, just let me know. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
On May 7, 4:48 pm, "Tim" <tim j williams at gmail dot com wrote:
Are you turning off screenupdating and setting calculation to manual during each import ? Tim "Klips" wrote in message oups.com... Hi all, I'm having problems finding out why the performance of my text file import routine takes a hit after the first import operation. I have a series of text files with a set format that I'd like to import into individual sheets in excel. I have written a procedure that takes care of this. The first file that I import is extremely fast. As soon as I import the next file, the speed drops to 1/10 of the first time. The speed deteriorates as I import more files, but the drop is more gradual. Here is the import code: Open textFile For Input As #1 'the text file is read line by line Do While Not EOF(1) Line Input #1, TextLine If InStr(TextLine, "POINT") < 0 Then If CheckHeader(Right(TextLine, Len(TextLine) - 13), textFile) = True Then GoTo ExitHandler Else Worksheets("Template").Copy After:=Worksheets("Summary") Worksheets(3).Name = txt_name.Value Worksheets(CStr(txt_name.Value)).Visible = True Sheets(CStr(txt_name.Value)).Activate ActiveSheet.Move Befo=Worksheets("Template") 'the script deletes previous information and replaces it with a new set rowNum = ActiveSheet.Range("Data").Row rowOrigin = rowNum ActiveSheet.Range("A" & (rowOrigin + 1), "A" & (ActiveSheet.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete If Notes.Range("Tbl_Case_Hdr").Row - Notes.Range("Tbl_Case_Ftr").Row = -2 Then Summary.Range("A" & (rowOrigin + 1), "A" & (Summary.Range("Data").Rows.Count + rowOrigin)).EntireRow.Delete End If End If ElseIf InStr(TextLine, "element group") < 0 Then 'whenever a new element is declared, the element and group ID are read TextPos = InStr(TextLine, "Element") + 8 TextLen = InStr(TextPos, TextLine, " ") - TextPos elmID = CInt(Right(Left(TextLine, (TextPos + TextLen - 1)), TextLen)) TextPos = InStr(TextLine, "group") + 5 TextLen = Len(TextLine) - TextPos grpID = CInt(Right(TextLine, TextLen)) Else If InStr(TextLine, "node 1") < 0 Then ActiveSheet.Cells(rowNum, 2) = grpID ActiveSheet.Cells(rowNum, 3) = elmID ActiveSheet.Cells(rowNum, 4) = grpID & "-" & elmID values = SplitData(Right(TextLine, Len(TextLine) - 13)) ActiveSheet.Range("V" & rowNum, "AA" & rowNum) = values If Notes.Range("Tbl_Case_Hdr").Row - Notes.Range("Tbl_Case_Ftr").Row = -2 Then Summary.Cells(rowNum, 2) = grpID Summary.Cells(rowNum, 3) = elmID Summary.Cells(rowNum, 4) = grpID & "-" & elmID End If ElseIf InStr(TextLine, "node 2") < 0 Then values = SplitData(Right(TextLine, Len(TextLine) - 13)) ActiveSheet.Range("AC" & rowNum, "AH" & rowNum).Value = values rowNum = rowNum + 1 End If End If 'update the progress bar prg_import.Value = Round((Loc(1) * 12800) / LOF(1), 0) Loop Close #1 Any help is appreciated. If someone wants to take a look at the complete file with sample text files, just let me know. Thanks- Hide quoted text - - Show quoted text - When I open my file, I set enablecalculation to false for all the sheets. The only sheet that this doesn't apply, I think, is the new sheet that is created. I tried setting enablecalculation = false after this sheet was created to see if there was an imporovement, but nothing changed. And yes, just above this i set screenupdating to false. Should I do things differently? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
Have you considered opening the text file directly into Excel and then formatting the file. Excel plops a text file into the first column of a worksheet in tenths of a second... Sub OpenTextFile() Workbooks.Open "F:\Acme\Z2Compliance.txt" End Sub Also, have you declared all of your variables? The code you posted doesn't show anything declared. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Klips" wrote in message oups.com... Hi all, I'm having problems finding out why the performance of my text file import routine takes a hit after the first import operation. I have a series of text files with a set format that I'd like to import into individual sheets in excel. I have written a procedure that takes care of this. The first file that I import is extremely fast. As soon as I import the next file, the speed drops to 1/10 of the first time. The speed deteriorates as I import more files, but the drop is more gradual. Here is the import code: -snip-Any help is appreciated. If someone wants to take a look at the complete file with sample text files, just let me know. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
On May 7, 5:18 pm, "Jim Cone" wrote:
Have you considered opening the text file directly into Excel and then formatting the file. Excel plops a text file into the first column of a worksheet in tenths of a second... Sub OpenTextFile() Workbooks.Open "F:\Acme\Z2Compliance.txt" End Sub Also, have you declared all of your variables? The code you posted doesn't show anything declared. -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware "Klips" wrote in ooglegroups.com... Hi all, I'm having problems finding out why the performance of my text file import routine takes a hit after the first import operation. I have a series of text files with a set format that I'd like to import into individual sheets in excel. I have written a procedure that takes care of this. The first file that I import is extremely fast. As soon as I import the next file, the speed drops to 1/10 of the first time. The speed deteriorates as I import more files, but the drop is more gradual. Here is the import code: -snip-Any help is appreciated. If someone wants to take a look at the complete file with sample text files, just let me know. Thanks The problem is that I'm not sure if this performance issue has anything to do with reading the data in. I feel like it is more a problem of how content gets assigned to cells. I have put all the code in a textfile at http://www.lapse.ca/code.txt in case you need to look at it. I'm using Option Explicit, so variables seem to be all defined. The code is executed by pushing a button on a userform. Any help is appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow Text File Import
On May 8, 8:17 am, Klips wrote:
On May 7, 5:18 pm, "Jim Cone" wrote: Have you considered opening the text file directly into Excel and then formatting the file. Excel plops a text file into the first column of a worksheet in tenths of a second... Sub OpenTextFile() Workbooks.Open "F:\Acme\Z2Compliance.txt" End Sub Also, have you declared all of your variables? The code you posted doesn't show anything declared. -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware "Klips" wrote in ooglegroups.com... Hi all, I'm having problems finding out why the performance of my text file import routine takes a hit after the first import operation. I have a series of text files with a set format that I'd like to import into individual sheets in excel. I have written a procedure that takes care of this. The first file that I import is extremely fast. As soon as I import the next file, the speed drops to 1/10 of the first time. The speed deteriorates as I import more files, but the drop is more gradual. Here is the import code: -snip-Any help is appreciated. If someone wants to take a look at the complete file with sample text files, just let me know. Thanks The problem is that I'm not sure if this performance issue has anything to do with reading the data in. I feel like it is more a problem of how content gets assigned to cells. I have put all the code in a textfile athttp://www.lapse.ca/code.txtin case you need to look at it. I'm using Option Explicit, so variables seem to be all defined. The code is executed by pushing a button on a userform. Any help is appreciated.- Hide quoted text - - Show quoted text - Tim & Jim, I read Tim's message again (and more carefully) and realized that my method of using enablecalculation was in fact not preventing excel from recalculating the entire workbook over and over again after each cell input. As Tim had pointed out I needed to set the Application.calculation parameter to manual in order for excel to stop calculating the entire sheet until I was finished with my import operation. I'm sorry it took me a while to figure out. Thank you both for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |