29
Fri, Nov
0 New Articles

Use Notes as Your Data Passport

Collaboration & Messaging
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

If you plan any type of data migration or recurring data transfers to your Lotus Notes databases, you should be aware of the native Notes import function, which allows you to transfer almost any type of data into your Notes database without much effort. Although the function has a few limitations, it should be sufficient for many basic import jobs. Generally, the import method works by parsing and identifying comma-separated field values contained in an ASCII text file to correspond to columns in a Notes view. This means that you must first transfer your source data from the AS/400 (or other RDMS) to the ASCII text file.

In this article, I’ve used a working example to show you how to import a tabular text file. For the example, I’ve used data from the infamous sample AS/400 Client Access file QIWS/QCUSTCDT and transferred the file to my PC as a comma-delimited file. (I’m assuming you already know how to get data from your AS/400 to a comma-delimited file on your PC, so I don’t go into detail about how to do that.)

Importing Tabular Text

Tabular text is ASCII text arranged in rows and columns and separated by characters (e.g., commas or semicolons). This file type is also referred to as a delimited text file. When you import a tabular text file into a Notes view, each row of text becomes a Notes document and each column of text (field) becomes a Notes field. If a Notes view does not already exist that contains each field in the ASCII text file, you need to create a special Notes view to receive the data that you’re importing. This special view should contain a column for each field in your ASCII text file, and the columns should be in the same order as the corresponding fields of the ASCII text file occur. If you map the fields properly, the value in each text field becomes the value of the corresponding Notes field in the view selected at time of import.

The Notes import function automatically determines the field data type. For example, if an incoming field value is all numbers, the import function creates a number data type, and if the incoming field value is all characters or a combination of numbers and characters, it creates a text data type. If the field contains a date value, the import function creates a date data type. The data type as defined in Notes does not affect the data type created. In other words, if you map a date to a field defined in Notes as a nondate data

type, Notes stores the value as a date data type. Be careful about how you map and import data and make sure that incoming data values would normally be data types that match the fields you’re mapping to.

Figure 1 lists the ASCII text file discussed in this article. The file is comma- delimited and does not use quotes to designate character strings. In addition, the fields in the ASCII text file are identified only by value, not by name.

Creating a Column Descriptor File

The Lotus Notes documentation states, “If the ASCII file and the Notes view have different formats, create a column descriptor file (.COL) to parse the ASCII file so its individual components correspond to columns in the Notes view or document fields.” This implies that you can perform an import without a column descriptor file, although I was not able to do this successfully. With a column descriptor file, however, I was able to successfully import data as illustrated in this material.

A column descriptor file is an ASCII text file (with a .COL extension) that contains a series of column definition statements that define which data from the source file goes into which Notes field. It can be used to import tabular text files and Lotus 1-2-3 worksheet files. The sample import in this article uses tabular text. This column descriptor file contains specifications that define the name of the view columns (fields), the separator characters used (commas in this case), and any calculated fields derived from formula expressions. Figure 2 contains the column descriptor file used for the working example in this article.

All comments begin with semicolons; other comments are interspersed in the specifications. After the initial comment are the column descriptor statements, whose syntax is as follows:

column_name: UNTIL “separator_character”;

You must define the columns in the same order in which they occur in the ASCII text file. The column_name is the name of the field in the Notes view that receives the value of the source field and is followed by a colon. UNTIL indicates that the value for column_name continues until the statement encounters the separator_character, which is simply the actual character used in your ASCII text source file to separate field values. In Figure 2, you can see that the separator character is a comma.

After you specify all column descriptors, you have the option of calculating your own values by using the Notes formula language. To do this, you must surround the formula statements with a FORMULA-START statement and a FORMULAEND statement (Figure 2).

As you can also see in Figure 2, the CREDITDUE field is set to Yes or No based on the value of the CREDITAMT field. If the value of CREDITAMT is greater than one dollar (1.00), CREDITDUE is set to Yes. Otherwise, CREDITDUE is set to No.

Limitations

As I mentioned, this method should be sufficient for basic data import tasks. It is one of the simplest ways to get data into Notes. However, along with its simplicity come a number of limitations. Notes supports a maximum of 999 characters per text file record. If any of your text file records exceed 999 characters, you must either use a different import method or split the data into multiple records by using multiple files and performing multiple import steps. In addition, if your source text file contains quotes around character strings, you must remove the quotes to prevent them from also surrounding the string value that ends up in your Notes field. (That is undesirable in most cases.) If any of the fields in your source text file contains characters equivalent to the separator character you’re using (in this

sample, commas), the import function will interpret them as separator characters. You’ll need to temporarily replace them with a non-separator character or remove them.

Performing the Import

After you create the ASCII text file containing your source data, your descriptor file and optionally, the view that is to receive the data, follow these steps to perform the import:

1. Select your target Notes database and open the view that you want to receive the data from your ASCII text file.

2. Select Import from the File menu.

3. Select Tabular Text and the name of the source file from the Import dialog.

4. Click Import to display the Tabular Import Text dialog.

5. Select a form to use for the imported source data from the Use Form list. (This form is associated with the document created for each imported record and is normally the same form used in the view that receives the data.)

6. (Optional) Enter the header line count or footer line count. (This might be useful when you use reports that contain headers and footers as your source data.)

7. Enter the number of lines per page in the source file. (I have found that this value is not required when the source file has no headers or footers.)

8. Select Main Document(s) (the default) from the Import as list unless you are creating response documents.

9. (Optional) Select “Calculate fields on form during document import” if you have calculated fields derived from formula expressions in your column descriptor file.
10. (Optional) Check the “Use format file” checkbox if you have created a column descriptor file as done in the example in this article. Then, click “Choose format file” and select the column descriptor (.COL) file you created for import.
11. Click OK to execute the import. After the import completes, the Notes view should be populated with the imported data (see Figure 3).

You Always Have Options

Although I’ve used a comma-delimited tabular text file with variable-length fields, you can import fixed-width fields with column descriptor statements that allow you to specify field start and end positions or start positions and width. You can also import structured text files or Lotus 1-2-3 worksheets to Notes data. Information about these other import options is available in the Help database in Notes.

Before you employ more complex and possibly more expensive ways to import data to Lotus Notes, consider the Notes native import function. It may be a good choice for simple one-time data migration or simple, regularly scheduled imports from other databases that you can’t connect to your Notes database via ODBC or Notes Pump (now known as LEI).

938472,Henning,G K,4859 Elm Ave,Dallas,TX,75217,5000,3,37.00,0.00
839283,Jones,B D,21B NW 135 St,Clay,NY,13041,400,1,100.00,0.00
392859,Vine,S S,PO Box 79,Broton,VT,5046,700,1,439.00,0.00
938485,Johnson,J A,3 Alpine Way,Helen,GA,30545,9999,2,3987.50,33.50 ; Column description specs for data import to Notes view
CUSNUM: UNTIL “,”;

LASTNAME: UNTIL “,”;
INITIAL: UNTIL “,”;
STREET: UNTIL “,”;
CITY: UNTIL “,”;

Figure 1: This is a partial listing of the comma-delimited file used for this article’s sample import job.

STATE: UNTIL “,”;
ZIPCODE: UNTIL “,”;
CREDITLIMIT: UNTIL “,”;
CHARGECODE: UNTIL “,”;
BALANCEDUE: UNTIL “,”;
; Identify end of last field as null (“”)
CREDITAMT: UNTIL “”;
; Specify formulas after all column definition statements
FORMULASTART
; Create value for credit due
FIELD CREDITDUE := @If(CREDITAMT > 1.00; “Yes”; “No”);
FORMULAEND

Figure 2: This column descriptor imports the comma-delimited file in Figure 1.





Use_Notes_as_Your_Data_Passport04-00.png 445x168

Figure 3: The CustInfoView now contains data imported from the text file.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: