Quodata Interface with PC Applications

1
Quodata Interface with PC
Applications

• Theresa Lebert
• College of Notre Dame of Maryland
• April 6, 2000

2
What we will review today...

• Sort a file set (Master and Address).
• Use PC Connection Prompt to create ASCII output.
• Import ASCII file into Microsoft Excel.
• Create reports, labels, and merge letters.

3
File sets

• File sets allow you to create a report/output
  with information from multiple files.
• Creating a file set requires knowledge of VMS, an
  editor, and a good understanding of the ways that
  files can be linked together (or knowledge of
  Quodatas File Set Manager option).
• All file sets are kept in a central location. Do
  not change existing file sets.

4
Sort file set
STU STUDENT Option? SORT FILE SET File set name
ltESCgt LIST? MASADD File set pointer to create
ltESCgt MASADD? MASADD Select a subset of records
ltESCgt YES? YES Condition ltESCgt LIST? COLDIV
D Condition ltESCgt EXIT? GRAD.DATE
19980831 Condition ltESCgt EXIT?
ENROLL.TYPE ltgt X Condition ltESCgt
EXIT? EXIT Another OR condition ltESCgtNO? NO Sort
field ltESCgt PRIMARY? L.NAME Sort field ltESCgt
EXIT? F.NAME Sort field ltESCgt EXIT?
EXIT Performing sort. Please wait Building
work index for STUDENTFILEMASTER Building
work index for REGFILPRTYADD Primary Records
extracted 2 Secondary Records
extracted2 Total5 Files
built Pointer file STUDENTTEMPMASADD.LPT
5
Output choices

• ASCII (Option 4)
• Create comma delimited text file

6
PC Connection Prompt w/file set

• Student Option PC Connection Prompt
• Enter number of choice? 4
• Include field names as first record? YES
• Field delimiter to use ,? ,
• Enter input data file/FLS name? MASADD
• Enter input index/LPT file name? MASADD
• Enter output file name? EXAMPLE
• Enter Field names to export (leave blank and
  press Return when done)
• Field 1? ID
• Field 2? TITLE
• Field 3? F.NAME
• Field 4? M.INIT
• Field 5? L.NAME
• Field 6? SUFFIX

7
PC Connection Prompt, contd

• Field 7? ADDRESS.1
• Field 8? ADDRESS.2
• Field 9? ADDRESS.3
• Field 10? CITY
• Field 11? STATE
• Field 12? ZIP
• Field 13? PHONE.OVERLAY/MASK(XXX)XXX-XXXX
• Field 14? COUNTRY
• Field 15? COLDIV/TABLE
• Field 16? GRAD.DATE
• Field 17? ltRETURNgt
• Enter field names to summarize (leave blank and
  press ltRETURNgt to exit)?
• Summary Field 1? ltRETURNgt
• Starting to write File EXAMPLE.PRN.
• EXAMPLE.TXT complete 2 Records.
• STUDENT Option? EXIT

8
Transfer file from ALPHA to PC

• Using SmarTerm -
• Select Tools, FTP Drag Drop
• Log on Alpha
• Select new file from bottom window and drag it to
  the top window.
• Disconnect FTP session by clicking on the bottom
  X button.

9
Import ASCII file into Excel

• Open Microsoft Excel
• Click on Open File button
• Search for the file
• When the Text Import Wizard appears, select
  Delimited -gt Next, Comma -gt Next, Finish.

10
Formatting data

• To set the correct width for all columns,
  highlight entire sheet select Format, Column,
  Autofit Selection.
• To sort the data, highlight entire sheet, select
  Data, Sort. When the sort window appears, select
  the columns to sort (ascending or descending
  order).
• To format the phone number, highlight entire
  Phone_Overlay column select Format, Cells,
  Special, Phone .
• Save data as Excel Worksheet in My Documents
  directory.

11
Items to create from Excel

• Mailing Labels
• Merge Letters
• Envelopes

12
Mailing Labels or envelopes w/out a template

• Open MS Word.
• Select Tools, Mail Merge.
• Click Create, select Mailing Labels (or
  Envelopes).
• Click Active Window.
• Click Get Data, select Open Data Source.
• Choose file location (Look in box) and file
  type (Files of Type box). The file type is
  Excel Worksheet.
• Open the file
• A pop up window will read Entire Spreadsheet,
  click OK.

13
W/out a Template, contd

• Click Setup Main Document button.
• Choose mailing label 5160 (or envelope size -
  10) and click OK.
• Insert your merge fields into the Create Labels
  window. Click OK when finished.
• From the Mail Merge Help box, click Merge.
• Click Merge.
• MS Word will merge all records into a new
  document.
• Save your new document and print.

14
Mailing Labels w/a template

• Open MS Word
• Click on Open File button to open an existing
  template
• Select Tools, Mail Merge.
• Click Get Data, select Open Data Source
• Choose file location (Look in box) and file
  type (Files of Type box). The file type is
  Excel.
• A pop up window will read Entire Spreadsheet,
  click OK.
• From the Mail Merge Help box click Merge.
• Click Merge.
• MS Word will merge all records into a new
  document.
• Save your new document and print.

15
Merge Letters

• Type your letter in MS Word and save (do not
  close the file).
• Select Tools, Mail Merge.
• Click Create, select Form Letters.
• Click Active Window.
• Click Get Data, select Open Data Source
• Choose file location (Look in box) and file
  type (Files of Type box). The file type is
  Excel.
• A pop up window will read Entire Spreadsheet,
  click OK.
• A pop up window will ask you to Edit Main
  Document.
• Your letter reappears with the Merge Tool Bar.

16
Merge Letters, contd

• Insert your merge fields into your letter.
• When finished, select Tools, Mail Merge.
• From the Mail Merge Help box click Merge.
• Click Merge.
• MS Word will merge all records into a new
  document.
• Save your new document and print.

17
What we have learned today...

• Sorting file sets
• Using PC Connection Prompt to create an output
  file
• Importing to Excel
• Creating Merge Letters Mailing Labels

18
Questions and comments...