Free Your Data Techniques for Getting Data In

Free Your Data! Techniques for Getting Data In and Out of Lotus Notes Julian Robichaux, SNAPPS © 2009 by the individual speaker

Iam. LUG 2009

Iam. LUG 2009

What do you mean by “Data”? Iam. LUG 2009

What do you mean by “Data”? • Mainly “view-type” data in a Notes database • Stored in Notes documents (natch) • For external data sources, it could be: s Text files s Relational databases s File System s Data objects s Spreadsheets s Maps s Tag clouds s Punch cards Iam. LUG 2009

Import/Export Versus Synchronization • We’re talking about importing or exporting data here • Synchronizing with an external source is trickier s Deal with updates and diffs s Deletion policies s Conflict winners and losers • NB: there are tools for this too s LEI s Casahl s Notrix Iam. LUG 2009

Enough of this talk! Let’s get started Iam. LUG 2009

CSV Files • CSV = Comma Separated Values • Comma-delimited text files • Old skool data format, very simple • Some flexibility (variations) in format: s Values can be enclosed in double-quotes or not s Sometimes text will be enclosed in quotes and numbers will not s Embedded double-quotes need to be double-doublequoted s Linefeeds end the data record, unless they’re inside a quoted string s If a value has commas, you must quote the whole value Iam. LUG 2009

CSV Files: Export • From a Notes View, File > Export…, then choose “Comma Separated Value” as the format • Custom Lotus. Script/Java agent to walk a View s Custom selections s Special groupings and collations of data s Calculations not easily done in views s Server-side CSV generation Iam. LUG 2009

CSV Files: Import • From a Notes View, File > Import…, then choose “Tabular Text” as the format Iam. LUG 2009

CSV Files: Import (cont. ) • That’s right, I said TABULAR Text for the format • You must create a. COL file (“format file”) to define rules for the import process s Format information is in the Domino Designer Help data. csv one, two, three four, five, six seven, eight, nine Iam. LUG 2009 data. col Field 1: UNTIL ", "; Field 2: UNTIL ", "; Field 3: UNTIL "";

CSV Files: Import (cont. ) • View Import limitations and gotchas s Each line must be less than 1, 536 characters s Quoted fields in data records keep their quotes s No embedded commas allowed in any text fields s Errors if a record line has fewer fields than expected s Formulas can be used for data transformations (see Designer Help), but can be perilous • Unless your CSV data is very controlled and consistent, you probably don’t want to use the native View import s But hey, it’s always fun to play! Iam. LUG 2009

CSV Files: Import (cont. ) • Lotus. Script CSVReader class s http: //nsftools. com/tips/Notes. Tips. htm#csvtoxml s Something I wrote to easily parse a CSV file s Deals well with quotes or lack of quotes, commas in value fields, etc. Dim reader As New CSVReader("c: data. csv") header. Array = reader. get. Next. Line Do Until reader. is. EOF data. Array = reader. get. Next. Line If Not Isnull(data. Array) Then Print header. Array(0) & " = " & data. Array(0) End If Loop Iam. LUG 2009

CSV Files: Import (cont. ) • ODBC s Will discuss a bit later s Windows has a native driver you can use s Usually overkill for CSV, but might be desirable s Must create a schema. ini file to define the fields in the CSV file, sort of like the. COL file we saw earlier s http: //msdn. microsoft. com/enus/library/ms 709353. aspx Iam. LUG 2009

XML Files Iam. LUG 2009

XML Files • Yeah, the ones with angle brackets everywhere s No, not HTML dummy • Pros s Multi-lingual s Can be used with most systems or programming languages s Parsers handle the dirty work for you • Cons s Get big fast (lots of text) s Parsers are strict – and PLEASE don’t write your own parser s Large files can eat up a lot of memory as they’re Iam. LUG 2009

XML Files: Export • http: //server/db. nsf/view? Read. View. Entries&Co unt=5000 Iam. LUG 2009

XML Files: Export (cont. ) • Custom Lotus. Script options • You CAN write out raw XML as text s Watch out for encodings and special characters s Make sure ALL tags are matched and closed • For complex XML (or unknown data), it might be better to use a parser s See Notes. DOMDocument. Node and Append. Child method for starters s Kind of a lot of work, but you’ll be sure to have valid XML Iam. LUG 2009

XML Files: Export (cont. ) • Custom Java options • Similar to Lotus. Script options s Raw text s Use built-in parsers (additional native Java classes available) • Third-party libraries might be easier s Apache Jakarta Element Construction Kit (ECS) s JDOM s XStream Iam. LUG 2009

XML Files: Export (cont. ) • DXL? Iam. LUG 2009

XML Files: Export (cont. ) • DXL? • DXL is probably more suited for exporting design rather than data (my opinion) • You don’t export view data, you export entire documents s LOTS of data for each doc s You can ignore extraneous data or use XSLT s Usually easier to export data from a view Iam. LUG 2009

XML Files: Import • Native Notes classes (Lotus. Script and Java) s Notes. DOMParser creates a tree structure Somewhat easy to step through Reads the whole file into memory (be careful of large files) s Notes. SAXParser processes the file sequentially Have to code various events (SAX_Start. Element, etc. ) Can only go forwards through XML, not backwards Uses less memory for large files s Notes. XSLTransformer Apply an XSL style sheet to an XML doc to Iam. LUG 2009 reformat it

XML Files: Import (cont. ) • Interesting related technique s Use XSLT to convert XML to DXL format s Pipeline XML through XSL transformer, directly to DXL Importer s XML > Notes. XSLTransformer > Notes. DXLImporter > new docs s Used by the Lotus Connections Plugin (ilc 4 ln. ntf) to convert ATOM feeds directly to Notes docs Iam. LUG 2009

XML Files: Import (cont. ) • Java also has native XPath support s Sometimes easier than walking a DOM tree import org. w 3 c. dom. *; import javax. xml. parsers. *; import java. io. *; import org. apache. xpath. *; Document. Builder. Factory factory = Document. Builder. Factory. new. Instance(); factory. set. Validating(false); org. w 3 c. dom. Document dom. Doc = factory. new. Document. Builder(). parse( new Byte. Array. Input. Stream(xml. String. get. Bytes())); Node n = XPath. API. select. Single. Node(dom. Doc, "//temp[@type='max']/value"); Iam. LUG 2009

XML Files: Import (cont. ) • You can use MSXML OLE object for XPath in Lotus. Script s Windows-only, of course (but nothing extra to install) Dim url As String, x. Path As String Dim dom. Doc As Variant, node As Variant url = "c: KFFC. xml" x. Path = "/current_observation/temperature_string" Set dom. Doc = Create. Object("MSXML. DOMDocument") dom. Doc. async = False Call dom. Doc. load(url) Set node = dom. Doc. select. Single. Node(x. Path) Print "Temperature is " & node. text Iam. LUG 2009

XML Files: Import (cont. ) • XMLNode. Reader: easy wrapper around Notes. DOMParser s http: //nsftools. com/tips/Xml. Node. Reader s Simple node syntax, also easy to get collections of nodes Dim reader As New Xml. Node. Reader Call reader. Read. File( "c: booklist. xml" ) Print reader. get( "bookshelf. book. title" ) books = reader. get. Node. Readers("bookshelf. book") Forall book In books Print book. get("title") End Forall Iam. LUG 2009

ODBC Iam. LUG 2009

ODBC • Old and standard way to connect to (and run SQL queries against) a relational database • Provides a consistent interface, but you still need drivers • No longer Windows-only Iam. LUG 2009

ODBC: Export • Lotus Notes is NOT a relational database s You knew that, right? • IBM has a “Notes. SQL” driver s Free download, easy install s Treats Notes Views like SQL tables s Has been updated for Notes 8 Iam. LUG 2009

ODBC: Export (cont. ) • Notes. SQL Issues s After install, Notes. SQL directory and Notes Program directory MUST be in your Windows PATH Vague error messages if they’re not s Known issues with Excel 2003 (but not Excel 2007) Often hangs when accessing or saving data s Known issues with Open. Office 2. x View columns with text values are blank s Notes. SQL driver (and related DSN) must be manually installed and configured on every client machine (or server) that needs to use it s Make sure you read the Help docs on performance Iam. LUG 2009

ODBC: Export (cont. ) • So, what is Notes. SQL good for? s Users who are already familiar with ODBC reporting s Environments where ODBC-based reporting tools (like Crystal Reports) are in regular use Crystal also has its own native Notes driver s Power users who use MS Access to write reports Oddly, even though there are issues with Excel 2003, Access works just fine s Nice “self-service” option for users • Very little work involved on the Notes side s Maybe a few custom views, maybe not s No custom exports to deal with Iam. LUG 2009

ODBC: Import • Much more useful when getting data from other sources Iam. LUG 2009

ODBC: Import (cont. ) • LS: DO (Lotus. Script Data Object) s “Old” way of making ODBC connections with Lotus. Script s Use an existing DSN to connect and get data Uselsx "*LSXODBC“ Dim con As New ODBCConnection Dim qry As New ODBCQuery Dim result As New ODBCResult. Set qry. Connection = con Set result. Query = qry con. Connect. To("My. DSN") Iam. LUG 2009 qry. SQL = "SELECT * FROM Some. Table" result. Execute Do result. Next. Row Print result. Get. Value("COLUMN 1") Loop Until result. Is. End. Of. Data result. Close(DB_CLOSE) con. Disconnect

ODBC: Import (cont. ) • LEI (Lotus Enterprise Integrator) s Can use ODBC connections or direct connections with special provided drivers • Administrator Database user interface s Set up connections, virtual documents, direct transfers, synchronization, etc. using a simple UI s Costs extra $$$ (or £££, or whatever) • LSXLC s Lotus. Script alternative to LS: DO using LEI code s Potentially more robust, and generally handles Unicode better s More dynamic than the UI option s Included with your standard Notes/Domino license Iam. LUG 2009

ODBC: Import (cont. ) • LSXLC s ODBC example below, direct driver connections (no DSN required) are essentially the same Uselsx "*lsxlc“ Dim LCses As New LCSession Dim con As New LCConnection ("odbc 2") con. Server = "Some. System. DSN" con. Metadata = "My. Table" con. Userid = "My. User. Name" con. Password = "My. Password" Iam. LUG 2009 con. Connect Dim field. List As New LCField. List sql = "SELECT * from My. Table" Call con. Execute(sql, field. List) Do While (con. Fetch(field. List) > 0) Print field. List. Some. Field. Name(0) Loop con. Disconnect

ODBC: Import (cont. ) • ADO (Microsoft COM object, called from Lotus. Script) s Another way to use either DSNs or DSN-less connections s Allows you to properly use the option in SQL Server that will return results as XML instead of a resultset http: //www 10. lotus. com/ldd/eiforum. nsf/0/bd 63 aeea 8 bb 94 f 0785256 f 41000 d 6 e 3 c? Open. Docume nt s Lots of examples on the web (easy to convert VBScript examples to Lotus. Script) • JDBC, if you’re using Java s Can use ODBC or special drivers s Again, lots of examples out there Iam. LUG 2009

ODBC: Export (cont. ) • Bill Buchan did a nice presentation on comparison of many of these techniques s Good information and useful sample code s “Leveraging Lotus. Script for Database Connectivity” s http: //www. hadsl. com – under “The View” tab Iam. LUG 2009

Spreadsheets Iam. LUG 2009

Spreadsheets • Spreadsheets are where much of the world’s data lives (or ends up, at least) • Easy to enter data, easy to manipulate data s And you don’t have to be a programmer to create one • In fairness, we’ll discuss both Excel and Symphony Iam. LUG 2009

Getting Data Into Spreadsheets • All of the methods we’ve discussed already s CSV files open natively s XML files open natively (keep it simple though) s ODBC connection via Notes. SQL • Notes COM objects called from a macro • Edit > Copy As Table from any Notes View s There is a limit to how many rows will copy though • Excel can use a URL to a Domino view as a “Web Query” data source Iam. LUG 2009

Spreadsheet Export Agents • Basic Excel technique Set xl. App=create. Object("Excel. Application") xl. App. Visible = False xl. App. Workbooks. Add Set xl. Workbook = xl. App. Active. Workbook Set xl. Sheet = xl. Workbook. Active. Sheet Set view. Nav=view. Create. View. Nav Set entry=view. Nav. Get. First Iam. LUG 2009 While Not entry Is Nothing col=1 row=row+1 Forall v In entry. Column. Values col=col+1 xl. Sheet. Cells(row, col). Value = v End Forall Set entry=view. Nav. Get. Next(entry) Wend xl. App. visible=True

Spreadsheet Export Agents • Basic Symphony technique Dim args() As Variant Set xsm = Create. Object ("com. sun. star. Service. Manager") Set xd = xsm. create. Instance ("com. sun. star. frame. Desktop") Set xbook = xd. load. Component. From. URL ("private: factory/scalc", "_blank", 0, args) Set xsheet = xbook. Sheets. get. By. Index(0) Set view. Nav=view. Create. View. Nav Set entry=view. Nav. Get. First Iam. LUG 2009 While Not entry Is Nothing col=0 row=row+1 Forall v In entry. Column. Values Set xcell = xsheet. get. Cell. By. Position(col, row) Call xcell. set. String(Cstr(v)) col=col+1 End Forall Set entry=view. Nav. Get. Next(entry) Wend

Getting Data Out of Spreadsheets • Again, methods we’ve already discussed s Save as CSV or XML, import into Notes s Use native Windows ODBC driver (for Excel) • Use COM/OLE like the export, only backwards s A little trickier to import than to export, because the data might have slight formatting variations s Often better to save to an intermediate format first Iam. LUG 2009

JSON Iam. LUG 2009

JSON • JSON = Java. Script Object Notation • Extremely easy way to store, retrieve, and interchange data in Java. Script s Native to Java. Script, so no external libraries required • See json. org for details on syntax and var my. JSONObject = {"meals": [ {“type": "breakfast", "food": "cookies"}, {“type": "lunch", "food": "Pringles"}, {“type": "dinner", "food": "Guinness"} ] }; semantics Iam. LUG 2009 alert(“For breakfast I ate “ + my. JSONObject. meals[0]. food);

JSON • Also very easy to construct a JSON object from a String s Format the string according to syntax rules, and use eval() var my. JSONObject = eval( ‘( {“food”: “coconut”, “texture”: “furry”} )’ ); • To go from JSON to a string is not native s Use the JSON class from json. org s Firefox 3. 5 has a JSON object built-in • Interesting use case: libraries can convert XML to JSON Iam. LUG 2009 s Makes it very easy to work with XML in Java. Script

JSON Iam. LUG 2009

JSON: Export • http: //server/db. nsf/view? Read. View. Entries&Output. Format=JSON&C ount=5000 Iam. LUG 2009

JSON: Export (cont. ) • Lotus. Script JSONWriter class s http: //download. snapps. com s By Troy Reimer, Apache licensed s Converts the following Notes objects to JSON strings Array List Notes. Document. Collection Notes. View. Entry Iam. LUG 2009

JSON: Import • Lotus. Script JSONReader class s http: //download. snapps. com s By Troy Reimer, Apache licensed s Convert JSON into objects and arrays, and do what you like Iam. LUG 2009

JSON: Import (cont. ) • Java can use the classes on json. org s Interestingly, these classes can also convert XML to JSON Iam. LUG 2009

Web Services Iam. LUG 2009

Web Services • This is NOT some sort of magic wand s Still manual coding involved s You have to create or consume the formatted data i. e. – you can’t just send/receive a raw Notes. Document • However, it’s a common way for apps to transfer data • No time to get into the nitty-gritty of “what is a web service” right now Iam. LUG 2009

Web Services Iam. LUG 2009

Web Services: Export • Some Domino-specific articles on IBM developer. Works s http: //www. ibm. com/developerworks/lotus/library/web- services 1 s http: //www. ibm. com/developerworks/lotus/library/webservices 2 s http: //www. ibm. com/developerworks/lotus/library/webservices 3 • Bottom line is that if you make data available via a web service, it will [often] be easy for other applications to consume the data s Platform independent, language independent, etc. s Microsoft. NET apps love web services Iam. LUG 2009

Web Services: Export (cont. ) • Lotus. Script or Java s Pass around scalar values, arrays, and objects Iam. LUG 2009

Web Services: Import • Notes (and Domino) 8. 0 and higher can natively call external web services s Import the WSDL file into a script library, wrapper code is written for you s Use the script library in agents or actions to call web services s No drivers to worry about, just an HTTP connection • In earlier versions, you can use Java or COM objects to call external web services Iam. LUG 2009

Web Services: Import (cont. ) • Anywhere you can write Lotus. Script or Java, you can call the web service Iam. LUG 2009

Mashing Up Your Data Iam. LUG 2009

Mash Up Smash Up • These techniques aren’t just for importing or exporting entire databases full of information • Very useful for: s Reporting s Graphing s Dashboards s Presenting statistics to your boss right before a performance review Iam. LUG 2009

And… We’re Done THANK YOU! Julian Robichaux nsftools. com SNAPPS jrobichaux@snapps. com Iam. LUG 2009

© Copyright Strategic Net Applications, Inc. 2009. All rights reserved. IBM, the IBM logo, the e-business logo and other IBM products and services are trademarks or registered trademarks of the International Business Machines Corporation, in the United States, other countries or both. Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries or both. Microsoft, Windows NT and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries or both. SNAPPS and SNAPPS Any. Place™ products are trademarks of Strategic Net Applications, Inc. Snapp. Shot® is a registered trademark of Strategic Net Applications, Inc. All other trademarks, company, products or service names may be trademarks, registered trademarks or service marks of others. Duplication, distribution, or presentation of these slides, in whole or in part, is prohibited without express permission from SNAPPS. Iam. LUG 2009