This content is part of the series: Tip Stay tuned for additional content in this series. Contrary to what you might think, not every document is initially written in XML. Actually, most documents are prepared with some other tool and later converted to XML. Many documents originate from relational databases such as DB2, or from Microsoft Office applications such as Word or Excel. I have found that many businesses use Excel to edit and prepare data. It's simple to use, widely available, and its tabular format is well-suited to all kinds of information, like lists of products, lists of names, financial data, or statistical data. Excel spreadsheets are often e-mailed to users who are asked to fill in the blanks.
Retrieve your data One of the issues that arises when collecting and preparing data in a proprietary application is recovering the data. Fortunately, this is not an issue with Excel. Although the specifications for the Excel file format are not publicly available, a number of options can extract XML information from a spreadsheet. This tip reviews your options and highlights each solution's pros and cons. XMLSS The latest versions of Excel - Excel 2002 and Excel XP - can export a spreadsheet to XML. While you don't have any say over the choice of tags, you do get a valid XML document that you can post-process with any XML tool, including an XSLT stylesheet.
The format is known as XMLSS. This is the simplest solution if:. Your users have the latest version of Excel, and. You're processing XML data on a workstation. The first condition is obvious, the second one might need more explanation.
Suppose you set up a server to collect and process statistical data. Your users prepare the data with Excel and later upload their spreadsheets to the server for further processing. The first step is to convert the spreadsheet to XML. One solution is to open the spreadsheet in Excel and export it in the XMLSS format. I have seen companies implement such a solution and, while it can run, it's important to keep in mind that Excel was designed for workstations, not servers.
Although functional, this solution might not be as stable as you would like. Among other issues, the spreadsheet conversion may become a bottleneck because it may be difficult to multithread the conversion. In the worst case, every request is queued into a single copy of Excel. It also limits your hosting options: Excel is only available on Windows and MacOS. CSV files The first alternative is to work not with actual spreadsheet files but with comma-separated value (CSV) files instead. CSV is a popular file format for exchanging spreadsheets.
Microsoft Download Center Homepage. New Surface Pro 6. Stand out from the ordinary. Top download categories. Excel, PowerPoint, and more.
Any worthy spreadsheet can export to and import from CSV files. Furthermore, many third-party products that work with spreadsheets also support CSV. For example, most accounting packages can work with CSV files. Although CSV files are not XML, it is simple to convert them into XML files with tools such as XI (see ). The TopXML site also includes algorithms for a pure XSLT solution.
Using CSV files is much more attractive for servers. For one thing, you are no longer limited to certain platforms. Instead, you are broadening the options for your users (who can now work with Lotus 1-2-3 and other spreadsheets as well as many third-party tools that also recognize the CSV format). About the only downside with this solution is that your users must take the extra step of saving their data in CSV. In practice I have found that this is seldom a problem, but your mileage may vary. Plain conversion Last but not least, you can turn to special libraries to read Excel documents. The Excel file format is not officially documented, but third-parties have been reverse-engineering it for some time.
They have also produced libraries to decode Excel files (see ). Here's a list of some of the available APIs:.
Java Excel API is one of my favourite solutions because it is portable, does a fair job of reading XML documents, and includes a conversion to XML. XML::Excel is a Perl API for converting Excel documents into XML. OpenOffice includes C libraries for reading Excel documents. You can use these libraries as a basis for converting from Excel to XML. Apache POI supports reading Excel files in Java code.
Again, you can use it as a basis for an Excel-to-XML converter. This list is a representative sample only. Other tools are available for different platforms. Converting Excel files has three benefits:. The files will run on any platform. The libraries are easy to integrate with a server.
Users don't need to remember to export the data. The main danger with this solution is that no library is as good as the real thing and you will find that some spreadsheets do not convert well. You will need to test the best library for your project. Get going Excel is a popular tool for preparing all kinds of data that you may want to process in XML. Fortunately, as this tip demonstrates, you have many options for retrieving data in XML.
The best option will depend on the specifics of your project. No matter how you convert the spreadsheet, you will find that the resulting XML document is never quite what you were expecting. Maybe it includes cells that you don't need or maybe the XML vocabulary is not quite what you were expecting. Don't worry - in most cases, it is simple to prepare an XSLT stylesheet to filter out unwanted data or rename tags. Related topics. For a quick training guide to XSLT, read Don Day's ' ( developerWorks, March 2000).
It introduces XSLT, the best tool for post-processing documents after a format conversion. Read ' ( developerWorks, April 2002) by Benoit Marchal, which introduces a tool to convert CSV files into XML. Download the, a great tool for converting Excel files to XML. Check out, which includes libraries for parsing Excel files. Apache's is a Java API for Excel files. Use this as a basis for your own Excel-to-XML converter. Cocoon uses Jakarta POI to convert from XML to Excel.
![Excel Excel](/uploads/1/2/5/3/125391962/685281204.jpg)
Find more XML resources on the developerWorks. For a complete list of XML tips to date, check out the. Find out how you can become an.
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I'd love to be able to create a smaller keyfield that's readily reproducible in case I need to do a VLOOKUP from another workseet or workbook. I'm thinking a hash from the title that'd be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm? Any thoughts or ideas on this or another strategy? You don't need to write your own function - others already did that for you. I don't care very much about collisions, but needed a weak pseudorandomizer of rows based on a variable-length string field.
Here's one insane solution that worked well: =MOD(MOD(MOD(MOD(MOD(IF(LEN(Z2)=1,CODE(MID(Z2,1,1))+10,31),1009).IF(LEN(Z2)=3,CODE(MID(Z2,3,1))+10,41),1009).IF(LEN(Z2)=5,CODE(MID(Z2,5,1))+10,59),1009).IF(LEN(Z2)=7,CODE(MID(Z2,7,1))+10,26),1009).IF(LEN(Z2)=9,CODE(MID(Z2,9,1))+10,53),1009) Where Z2 is the cell containing the string you want to hash. 'MOD's are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X.255. For a reasonably small list you can create a scrambler (poor man's hash function) using built-in Excel functions. =CODE(A2).LEN(A2) + CODE(MID(A2,$A$1,$B$1)).LEN(MID(A2,$A$1,$B$1)) Here A1 and B1 hold a random start letter and string length. A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN as a 'fanning function' to reduce the chance of collisions. CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well. Edit: If your strings should have variable lengths (e.g. Full names) but are pulled from a database record with fixed width fields, you will want to do it like this: =CODE(TRIM(C8)).LEN(TRIM(C8)) +CODE(MID(TRIM(C8),$A$1,1)).LEN(MID(TRIM(C8),$A$1,$B$1)) so that the lengths are a meaningful scrambler.
I am using this which gives pretty good results with preventing clashing without needing to run a script each time. I needed a value between 0 - 1. =ABS(COS((CODE(MID(A2,ROUNDUP(LEN(A2)/9,0),1)).(CODE(MID(A2,ROUNDUP(LEN(A2)/5,0),1))+100)/CODE(MID(A2,ROUNDUP(LEN(A2)/3,0),1)).(CODE(MID(A2,ROUNDUP(LEN(A2).8/9,0),1))+25)/CODE(MID(A2,ROUNDUP(LEN(A2).6/9,0),1)).(CODE(MID(A2,ROUNDUP(LEN(A2).4/9,0),1))-25))/LEN(A2)+CODE(A2))) It picks letters from across the string, takes the value of each of those letters, adds a value (to prevent same letters in different places giving same results), multiplies/divides each and runs a COS function over the total.