+ Reply to Thread
Results 1 to 7 of 7

XML Maps import slow only for complex workbooks

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    XML Maps import slow only for complex workbooks

    I have several workbooks that use an XMLMap to import XML data. The data usually includes about 200 single cells and 20 tables about 400 rows and 5 columns each. When I set up this XMLMap in a blank workbook the import is nearly instant, but with a complex workbook like I need to use it in, it takes about 20 seconds to import. My complex workbooks have hundreds of thousands of formulas and many spreadsheets.

    I have calculations turned to manual, screen updating turned off, I've tried removing autofiltering on the sheets.

    I've been working on this for several days and can't seem to get anywhere. If I delete large portions of the formulas, the import happens faster. Are there any known issues with the workbooks complexity affecting data import? Are there any things I haven't tried yet?

    I'm leveraging VSTO for visual studios to run this in C#, but the speed is also slow in VBA and using the import button on the developer menu of the ribbon.

    Thanks a ton!!!

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: XML Maps import slow only for complex workbooks

    Yes, but that is only after you imported the data in to excel. All imports through data have the auto fresh is on. This could slow as the excel is carrying extra work. You can temp turn the auto fresh off , but I doubt if this helps you with the issue you are having.

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: XML Maps import slow only for complex workbooks

    Quote Originally Posted by AB33 View Post
    Yes, but that is only after you imported the data in to excel. All imports through data have the auto fresh is on. This could slow as the excel is carrying extra work. You can temp turn the auto fresh off , but I doubt if this helps you with the issue you are having.
    What AutoRefresh setting are you referring to? I've already tried turning the calculation mode to manual.

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: XML Maps import slow only for complex workbooks

    Hmm! you might be right, though, I am not sure if turning off auto calculation is the same turning off the Auto-refresh.

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: XML Maps import slow only for complex workbooks

    I just tested this again starting with a blank workbook.

    Created Mapping
    Imported data (immediate)
    Added 200,000 rows x 4 columns of numbers
    Added 3 more columns of formulas to sum, average, median them
    Imported data again and it took 20 seconds.

    I don't understand why import would slow down because of unrelated content in the workbook.

  6. #6
    Registered User
    Join Date
    05-01-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: XML Maps import slow only for complex workbooks

    one more piece to the puzzle; if I remove all of the formulas from the above example but leave the data, the import is immediate again. It also seems to not make a difference if the formulas are complex or not. It's just the number of formulas.

  7. #7
    Registered User
    Join Date
    05-01-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: XML Maps import slow only for complex workbooks

    Just to wrap this up in case anyone else has this issue...

    I never found a simple fix for this this issue. A large number of formulas in excel slows down XML import regardless of the usage of the data imported.

    Therefore, I had to programmatically open another workbook in a completely different session of excel (still refrenced from my addin) with an identical map and import there. I then copy over the data to the workbook I need to use it in. I do have to be careful the typing of text data is accurate since "010" might get updated to 10 upon copying.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Function for XFDF import into Excel very slow
    By willefg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2016, 06:28 PM
  2. Replies: 1
    Last Post: 10-11-2016, 02:13 PM
  3. [SOLVED] Extremely slow workbook and complex formulas
    By gaker10 in forum Excel General
    Replies: 7
    Last Post: 07-28-2014, 09:13 AM
  4. [SOLVED] Delete XML maps while import xml files
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2012, 05:23 PM
  5. Macro Tied to Complex Nested If Statement Extremely Inefficient/Slow - HELP
    By Umann51 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-06-2011, 04:34 PM
  6. [SOLVED] XML Import too slow
    By detritus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2006, 06:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1