 |  | |
By Lance Zakin, HCL CASA, CAAD
Notes and Domino
HCL BP NotesMail | Notes users sometimes request to export Notes data for reporting purposes. Views are usually the best starting point because you can export an existing view or customize a view to display all the data required for the export. Notes lets you export views using the Export menu option to three support file types: Lotus 1-2-3, Structured Text, and Tabular Text.
NOTE: This article appeared in Lotus Advisor magazine. View original article | |
|
The quickest and most effective exporting file type is Lotus 1-2-3 or Lotus 1-2-3 Worksheet, depending upon which Notes version you're using. The Tabular Text file type only exports what you see on your screen, so if your view column aren't wide enough to display data, it will be truncated during the export. The Structured Text flie type isn't customizable because it doesn't rely on the view columns to export data, but rather exports all the field names contained in a document as well as the assigned data values.
The Lotus 1-2-3 file type exports data in all view columns regardless of column width, however, there is a 255-character limit if you open the exported file using Microsoft Excel, and a 511-character limit is you open it using Lotus 1-2-3. The exported filename should contain the ".WK4" extension, which will export the maximum supported characters.
You can develop a LotusScript agent that exports the view to a comma-delimited file (.CSV) that lets you break the limit, otherwise, you can set up an ODBC Source to break the limit.
You must first verify you installed the Lotus NotesSQL ODBC driver by clicking on Start > Settings > Control Panel > Data Sources (ODBC) > Drivers tab. If you don't see it listed, you can download it from the Lotus Developer Domain Website.
Using Excel
The following instructions describe how to use Microsoft Excel and Microsoft Query to export all the view data. You can achieve the same results using Lotus 1-2-3 and Lotus Approach.
Open Microsoft Excel, then click on Data > Get External Data > Create New Query > <New Data Source> > LotusNotesSQLDriver (*.nsf) > Connect > Server > Database > Max Length of Text Fields 10240 > OK.
Entering 10240 as the Max Length of Text Fields is the key to breaking the spreadsheet export character limit.
Click on OK > YOUR_NAME data source > OK > View > Columns > Next > Next > Next > Save Query > YOUR_FILE.DQY > Return Data to MS Excel > Finish > OK. Be sure to click on the Save Query button to save your query as a Microsoft Query file (.DQY) to use in the future for quick exporting.
Now you can export the view. Open Microsoft Excel, then click on Data > Get External Data > Run Database Query > YOUR_FILE.DQY.
You can automate this process for the user by clicking on a view action button. This type of automation isn't necessary if you're the only one running the export. Otherwise, you must install the Lotus Notes SQL ODBC driver, install the Microsoft Query file (.DQY) you created, and configure the ODBC Data Source at each user's workstation.
| |