42 bands from 15 countries to feature at Music Matters Live 2012 which will beam live via YouTube for 1st time this year. #mm12
10 minutes ago by EileenZDNetAsia on twitterZDNet / Techguide / Web Development
Write an advanced Excel data import with Agile Platform
Summary
Justin James explains how to create a data import for Excel in Agile Platform. He states the process is much faster than using third-party components in .NET code.
Events
Echelon 2012
June 11 and 12, 2012
University Cultural Centre, National University of Singapore
Startup Asia Jakarta 2012
June 7 and 8, 2012
12th Floor, Annex Building, Wisma Nusantara Complex, Jl. M.H. Thamrin No. 59 Jakarta 10350, Indonesia
MMA Forum Singapore
April 23-25, 2012
Grand Hyatt Singapore
One of the most persistently obnoxious requirements in the history of development is the need to import data into an application from Microsoft Excel.
We know how much users love to use Excel as a miscellaneous catch-all application (especially small businesses that seem to run their entire organizations from it), so the data import is important, but dealing with Excel is a hassle.
Running Office on the server is known to be problematic (it creates a memory leak), which rules out using Visual Studio Tools for Office. Third-party components are available, but the good ones are usually quite expensive, and the free and open source ones are typically buggy, out of date, or lacking features.
Luckily for me, creating a data import in the OutSystems Agile Platform took hours rather than days like it usually does; I used components built-in to the system and learned what to do in a few minutes. I'll show you what I did to write an advanced data import for Excel 2007/2010. Excel data imports
The first step in the data import is to get the data from the user. For this, we use the FileUpload Widget--just place it on the screen and give it a name. Next, create a new Button and link it to a new Action to perform your import; this will put a box on the screen for the user to select their local file and then upload it.
In the Action, the real fun begins. What makes this so insanely easy is one simple built-in Action that's available in the Toolbox: ExcelToRecordList. Incidentally, there is also a RecordListToExcel Widget, which does just what you think, and is great for writing data exports. Using this Action takes the Excel file and turns it into a RecordList, using the Record Definition you provide. Here are some notes on how it parses the file:
- You can specify which sheet to read. If you don't, it looks for "Sheet1" and, if that is not available, it uses the first sheet in the workbook.
- If the number of columns in the sheet matches the number of columns in the record definition, it will read them from left-to-right to fill in the record. Otherwise, it will use the data in row 1 to match columns to Attributes (I highly recommend this).
- If two record types in the definition have Attributes with the same name, the column name in row 1 should differentiate which one it means using a colon to separate the record's type from the column name (e.g., "Customer:Name" and "Part:Name").
- Your record definition can contain Entities or Structures (this will become important in a moment).
- The record definition cannot contain any Entities or Structures with Attributes of the type BinaryData.
My initial idea was to use the destination Entity for the import in the data definition; this is perfectly fine in many situations, but I quickly hit a few walls. First of all, one of the Entities I was importing had a BinaryData Attribute, and I really did not feel like rewriting all of the places it was used to put it in its own Entity. Secondly, some of the Entities I needed to import refer to other Entities. For example, my PART entity is linked to SUPPLIER, which I was also importing. Even if they were not both coming in at the same time, I certainly did not want to force my users to find out the ID numbers for the linked Entities.
So, I reached into my bag of tricks and came up with an easy solution: I selected the Entity, right-clicked and chose Copy, and then went to the very top of the data tree, right-clicked, and chose Paste As.... I then selected Structure, which gave me a Structure with identical Attributes as the Entity I wanted. Next, I removed the BinaryData Attributes and the Id Attribute. Then, any Attribute that referred to another Entity, I replaced with a Text Entity. For example, where my PART Entity has a Supplier Attribute pointing to SUPPLIER, I replaced it with a Text Attribute called SupplierName.
Using this record in my record definition for the ExcelToRecordList Action, it was time to move on. I used a ForEach Action to iterate over the resulting Record List. Within the ForEach, my first step was to look up the SUPPLIER Entity by the SupplierName Attribute in the imported data, which was easy enough. Then I used an Assign Action to copy all of the data from the imported structure into a local variable of type Record with a definition of SUPPLIER, as well as some default data value and the Supplier Identifier needed for the Supplier Attribute of PART. This local variable gets passed to the CreatePART Action defined by the Entity.
One Entity (CUSTOMER) was particularly tricky--it referred to another Entity (ADDRESS) three times (PhysicalAddress, MailingAddress, and BillingAddress). What to do? Easy! I copied the ADDRESS Entity into three structures (ForImportPhysicalAddress, ForImportMailingAddress, and ForImportBillingAddress), and in each of them, I changed the Attributes to be in the form of "PhysicalAddressCity" or "MailingAddressLine1". While I could have just left the names the same and used the names "PhysicalAddress:City" or "MailingAddress:Line1" in the Excel column headers, my way is a lot easier for the users who will be working with the Excel file. In the import for this sheet, I first created each ADDRESS Entity separately, and then filled the resulting IDs into the CUSTOMER Entity before creating it.
As an added bonus, I did the heavy lifting in an eSpace Action, passing in the BinaryData from the upload. I created an Output Variable of type RecordList, with a definition of PART for my Action. After creating each Entity in the database, I took the resulting ID, added it to the local PART record variable, and used ListAppend to add it to the Output Variable. With this, I have the option of displaying to the user what records were imported after the successful import.
The only trouble I had with this process was that I lost a lot of time because I was editing a different Excel file than the one I was actually testing with. Outside of that, the process took me about two hours (including figuring out the Structure trick) to do imports for five Entities, some of which were fairly complex. I know that using third-party components in .NET code would have taking me much, much longer.
Read my other TechRepublic posts about Agile Platform
Resource Centre Useful content from our premier sponsors
Web Development TechGuides
Creating a liquid layout for an Agile Platform theme
Working with Visual Studio 2010 from command line
Key to making applications discoverable
Easily connect to RavenDB data via C#
Use qTrace to replicate errors for QA testing
Tips on making sure your apps work for mobile users
RavenDB, a database alternative for .NET developers
Importing existing database schemas as Agile Platform entities
5 persistent Web application myths
Improve page performance with Microsoft Ajax Minifier
ZDNet Asia Live
Music Matters to be launched in Bali via partnership w/Telkom Indonesia. #mm12
10 minutes ago by EileenZDNetAsia on twitter
HP to shed 27K workers by 2014 http://t.co/OevueOGh http://t.co/erFSwAUB #arcavir
11 minutes ago by V_RaV on twitter
http://t.co/VNaUVSe1 HP to shed 27K workers by 2014: IT vendor plans exit of 8 percent of gl... http://t.co/5LKpdBSZ http://t.co/wiqTBKkj
11 minutes ago by RavtachSolution on twitter
China solar cell makers seek Taiwan partnershipshttp://bit.ly/JErUGz via @zdnetasia #solar #energy #china
26 minutes ago by newellpr on twitter
Malaysia organizations don't realize severity of cyberattacks http://t.co/PUCv68Rd
1 hour ago by ALLsecuritySoft on twitter
News: Radio Costa Rica by EnjoyIT 1.0: Radio Costa Rica allows you to listen to a great var... http://t.co/BLzVT5As http://t.co/1Dhcy6ki
1 hour ago by CostaRica_VIP on twitterThe key for mobile operators is identifying the applications that are popular with subscribers on their network. They can then work partn...
3 hours ago by camcullen on Experience trumps content in apps monetization
Experience trumps content in apps monetization | ZDNet http://t.co/gBXcjbGd
3 hours ago by DennisOosterman on twitter
Experience trumps content in apps monetization - ZDNet Asia News: "What we are doing currently is not to monetiz... http://t.co/S2EZtd8m
3 hours ago by kennyfabre1 on twitter
Malaysia organizations don't realize severity of cyberattacks: "Minister Maximus Johnity Ongkili said at the Sec... http://t.co/bgVlOBvx
5 hours ago by Bug2Hunt on twitter
#security Malaysia organizations don't realize severity of cyberattacks: "Minister Maximus Johnity Ongkili said ... http://t.co/hkFb4zrI
5 hours ago by Wiredsec on twitter
Malaysia organizations don't realize severity of cyberattacks http://t.co/EEEmRM3j via @zdnetasia
5 hours ago by RedDragon1949 on twitter
Malaysia organizations don't realize severity of cyberattacks - ZDNet Asia News http://t.co/YpNMYgb5
5 hours ago by RedDragon1949 on twitter
Malaysia organizations don't realize severity of cyberattacks http://t.co/FFems54Q
5 hours ago by mytech_pro on twitter
China solar cell makers seek Taiwan partnerships http://t.co/p5Hh7kJD
6 hours ago by Export2China on twitterSo much as we know , MTK6575 extremely integrated frequency1GHz ARM Cortex-A9 processor, the superiority of 3G / HSPA Modem, and help the...
1 day ago by y15822137359 on 5 SaaS adoption speed bumps to avoidI reckon your view: "CRM is strategy, not software", if a company replicating the approach uses in ERP implementation into CRM, what they...
3 days ago by wykoong on Gartner: Mobile CRM gives better ROI than socialThis video will teach you about the Excel fill handle but also provide you with a workook to download... http://www.youtube.com/watch?v=...
3 days ago by TradeBrother on A quick fill handle trick for Microsoft Excelwaiting...
5 days ago by eapete on What should count in a company's market value?Boy, you've opened a can of worms now.
Wait for the rants & raves.
I was puzzling before this whether to replicate the success formula we executed for a financial institute, and come out with a standard s...
6 days ago by wykoong on Drop the egos, copy ideas, then innovateKeep up with ZDNet Asia
Facebook 
RSS Feeds 
Newsletters 
Twitter Whitepapers
Understanding Awareness in Mixed Presence Collaboration
SUSE Linux Enterprise Server on the IBM PureFlex System
IBM® PureFlex™ System: The Future of Datacenter Management by Ptak, Noel & Associates LLC
Businesses are ready for a new approach to IT
IBM PureFlex System for consistent SAP solution hosting and management
Downloads
Web Development News
Inside ZDNet Asia
Sponsored
Threats and malware know no boundaries. Neither should your web security. See how far Blue Coat Unified Web Security goes to protect your network.
Echelon 2012 - The Awesomer Tech Event in Asia
Echelon 2012 – SEA’s longest running tech startup event goes Awesomer. Catch 50 of Asia’s most promising startups & over 40 international speakers on June 11-12.
Startup Asia Jakarta showcases new product-ready tech startups. Plus: hackathon, exhibition, and speakers. Use promo code CBSi50 for 50% discount.
ZDNet Asia Intelligent Singapore video series
Featuring inteviews with CXOs who define "intelligence" in their markets and reveal how their companies drive business efficiencies through ICT.
