[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to speed up Importing of Excel XML Mapped tables

Posted on 2011-05-04
14
Medium Priority
?
3,923 Views
Last Modified: 2013-11-21
I have about 6 or 7 excel tables (list objects) mapped to xml files. These tables contain large amounts of data, some of them 4,000 rows or more. I  use VBA to export and import these xml maps as follows:

ThisWorkbook.XmlMaps("InputTable_Map").Import (currClient.Path & "\input1.xml")

 ActiveWorkbook.SaveAsXMLData currClient.Path & "\input1.xml", ActiveWorkbook.XmlMaps("InputTable_Map")

The export works very fast and there is no problem. The import though takes very long and could be up to a minute or so.  I

The use of listobjects was chosen beacuase of the ability to add and delete rows in vba with minimal effort.  Is there a faster option than what I'm currently using for saving and retrieving xml data?
0
Comment
Question by:AccountantsTech
  • 7
  • 6
13 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 35695601
Make sure you have screenupdating disabled and calculations set to manual before you start the import.  Restore them after your import.
0
 

Author Comment

by:AccountantsTech
ID: 35697491
I already have that all turned off.  What seems to be slowing it down is that it is importing into a table and it is importing one line at a time.  Is there a command or code that would bring the xml data into the table in bulk instead of bringing it in line by line.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35698097
>>it is importing into a table ..

Please clarify.  Since you are importing XML into an Excel worksheet, where/what is this "table"?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:AccountantsTech
ID: 35699425
The workbook has a sheet called "Input" with a table named "input1" there is XML map called "InputTable_Map" that maps the xml file "input1.xml to that table "input1".
I have a summary report that references the information in the table "input"

To change to a different clients info we run the import code with the new clients path.

ThisWorkbook.XmlMaps("InputTable_Map").Import (currClient.Path & "\input1.xml")

It works extrememly well however, it imports slow.   I have around 10 different tables mapped to xml that I am running the same code on and it takes over two minutes to import everything.

All I can figure out is that it runs slow because every row that is imported excel goes and updates the table range.  So if you import 14,000 rows it updates the table range in the name manager 14,000 times.

Any suggestions?

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35699624
I have a hunch I know what's going on, but I'd like you to do a test for me.  Please do an XML import into an empty workbook (empty cells and no 'table' definitions).

Also, please import the XML in non-table format into a worksheet.

Are both of these slow?
0
 

Author Comment

by:AccountantsTech
ID: 35699737
Yeah I think you know what is going on.  I tried to run your test but as soon as you map the xml excel automatically creates a new table.  Then when you run it the first time it take REALLY long to build the table.  If you refresh the data it run QUICKLY.  However, as soon as you add a lot of rows to the table it REALLY slows down.

Bottomline is I can't import from xml into a non-table format.

0
 
LVL 46

Expert Comment

by:aikimark
ID: 35699793
>>I can't import from xml into a non-table format
Please explain.

* If we can't get past the performance issue, we might be able to work around the table performance by importing in non-table format and then moving the data into a table.  We would be simulating the data table import.

* What does the data look like?  

* I'm thinking that we might disable text wrapping and the data filters prior to the XML table import.
0
 

Author Comment

by:AccountantsTech
ID: 35699821
I just found a way to import it in a non table format by using the data tab from other sources and picking XML.  this runs much faster.  It takes about 7 seconds compared to about 25 seconds using the mapping xml method and then refreshing.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35699879
seven seconds still seems like a long time.  How many rows in your import test?
0
 

Author Comment

by:AccountantsTech
ID: 35699896
3,500 rows takes about 4 - 7 seconds
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 35700093
That still seems like a lot, especially when faced with importing 6 more tables.

My train of thought is to remove the data filters from the table and then import or refresh the data.  The data filters can be restored after the XML import.

How close is that imported data to what you might see in a table format?

If we can't get the data quickly, we might populate a variable with the data and then push the data into the worksheets in one step.  I wrote about this in an EE article.
http://www.experts-exchange.com/A_2253.html
0
 

Author Comment

by:AccountantsTech
ID: 35700184
I am creating an excel application that is storing client by client data in xml format.  The idea is to use VBA code to save and retrieve the xml files.  So they are simply the same table data just that it is stored in XML format.   At first this seemed like a great way to do it but now it runs so slow when I try and retrieve a clients data that I am looking for a speedier way to save the table data in my excel file to a specified file location and then retrieve it again.  

The idea is to create an excel workbook as my application so I can keep making changes in one workbook and use it for all clients data that I save and retrieve.

Any better suggestions.............All the inforation is generated within excel and just needs to be able to be saved and retrieved outside of the workbook QUICKLY :)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35700262
Why not save to a database format?  Access is an acceptable choice if everything is local.  Also, why not use plain XML as the format?

Have you considered converting this Excel VBA application into a VSTO application?  That might be worth opening a new question, once you've gotten past this performance hurdle.  
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question