• State Verified Answer
  • Replies 6 replies
  • Subscribers 32 subscribers
  • Views 3736 views
  • Users 0 members are here

Excel import takes much longer than previous versions

szang
Offline

It appears that at some point in time the excel import engine has been modified.  I often import data into gINT from Excel Spreadsheats using a correspondence file.  The excel spreadsheets contain multiple sheets and a lot of extraneous information.  The import process used to take a few seconds.  My current install takes up to 5 minutes to import the same file. The import completes successfully but takes an unreasonable amount of time. According to the dialog window that displays during import, most of the time is spend building a temporary file that apparently includes all the sheets in the spreadsheet even though my correspondence file is set to default to only one of the sheets in the file.  What could have changed in gINT that makes importing the same excel file using the same correspondence file take so much longer than it used to and how do I fix this?

Parents
  • 0 Offline
    I have seen this happen when the Excel file has a LOT of empty rows. To check this, put your cursor in the first cell. Hit Ctrl+End. This will move the cursor to the last cell with data. If it goes to the bottom of a lot of empty rows, delete the empty rows. Repeat on each worksheet. Please let us know if this fixes the problem.



  • 0 Offline in reply to Kathleen Holcomb
    Yes I have some extraneous rows in my spreadsheet and several irrelevant sheets in the workbook as well but not a LOT. Each of 5 sheets has about 1000 lines, some of which are relevant and many of which are not (this confirmed by hitting Ctrl+End in each sheet). To determine what in each sheet was causing the long import times I created a series of sheets progressively deleting portions of the irrelevant data. My results were not repeatable and confused me, so I took the original sheet (which in previous versions of gINT took about 30 seconds to import) and imported it repeatedly. My procedure was: delete previously imported data in gINT, Import the file using my correspondence file, time how long the import took, repeat. My results are listed below.

    Trial 1 = 4.0 minutes
    Trial 2 = 0.5 minutes
    Trial 3 = 2.0 minutes
    Trial 4 = 0.5 minutes
    Trial 5 = 0.5 minutes
    Trial 6 = 2.0 minutes
    Trial 7 = 2.5 minutes
    Trial 8 = 0.5 minutes
    Trial 9 = 0.5 minutes
    Trial 10 = 0.5 minutes

    Close gINT and then restart gINT
    Trial 1 = 1.0 minutes
    Trial 2 = 0.5 minutes
    Trial 3 = 1.0 minutes
    Trial 4 = 0.5 minutes
    Trial 5 = 0.5 minutes
    Trial 6 = 0.5 minutes
    Trial 7 = 2.0 minutes
    Trial 8 = 0.5 minutes
    Trial 9 = 0.5 minutes
    Trial 10 = 0.5 minutes

    Close gINT, reboot machine, restart gINT
    Trial 1 = 0.5 minutes
    Trial 2 = 0.5 minutes
    Trial 3 = 0.5 minutes
    Trial 4 = 0.5 minutes
    Trial 5 = 2.0 minutes
    Trial 6 = 2.8 minutes
    Trial 7 = 0.5 minutes
    Trial 8 = 0.5 minutes
    Trial 9 = 0.5 minutes
    Trial 10 = 0.5 minutes

    As you can see, many times the file imports ok, taking the expected amount of time (0.5) minutes. However there are times when it take up to 8 times as long (in this timed test, sometimes it takes even longer). This time is consumed building the temporary file tables. Observations indicate that it is not consistent which table takes more time, sometimes it is the first table, sometimes the third etc. Once built the import is always very quick. Note that with this test you can not blame differences in the file being imported or the correspondence file since each import uses the exact same file and it is untouched between each trial in fact the import dialog comes up with the previously used file names on each successive trial and I do not change them. This is also not a network problem because all files are stored on the local machine. ProjectWise is disabled in gINT. gINT version is 8.30.04.206. Operating system is Win 7. Only program that may have been running in the background during these trials is Microsoft Outlook which should not be interfering to such a great degree. I suspect this has to do with how the import module is allocating memory for the temporary file. I note that when the times are long CPU usage goes up to 100% and my cooling fan kicks into high. As indicated previously I did not have this problem on versions of gINT from a few years ago (same import file, same correspondence file). The change may have something to do with switching from Microsoft Jet to SQL server as the import engine.

    Any light you could shed on this would be appreciated. I can tolerate a 30 second import if it is consistent. a 5 minute import makes me think something went wrong.
  • 0 Offline in reply to szang
    There is quite a wide variance in import times. Are you importing to a SQL database? Importing to SQL would certainly be slower than importing to Access. But that wouldn't account for the wide variance.



  • 0 Offline in reply to Kathleen Holcomb
    The project file is a local file (not network) stored as a .gpj and only contains a few borings.
  • 0 Offline in reply to szang
    Do you mind sending the project, import and correspondence file so that we can troubleshoot this? Kathleen.holcombATbentley.com



Reply Children
Communities
Support and Services
Training and Learning
Social Media