National-Louis InfoBits
 OIT Home
OIT Announcements
OIT Newsletter
NLU Home
Print This Page
|
|
Need Mailing Labels? How to Print Labels Using a Banner CSV (Comma Separated Values) File
One of the most common requests for information from Banner is for a list - of people, addresses, telephone numbers, etc. This information is often used for mailing labels. When we receive a request for "Mailing labels" what we actually send you is the electronic file that allows you to generate the mailing labels yourself. By providing you with the electronic file rather than the actual labels, we give you the power to:
- Sort the data in any way you need
- Edit the list to treat subgroups in different ways
- Keep a record of what you sent to whom
- Use the electronic list to track responses
- And, we send you the electronic file via email so you receive it seconds after it is generated
- And note - there are a number of standard reports that you can run yourself to generate these electronic lists anytime you need them. (See example below).
Tools and Skills Needed
Generating mailing labels from a CSV file requires basic familiarity with Microsoft Word and Excel and understanding of the Mail Merge feature of Word. If you are going to run the Banner report yourself, then you also need to know the name of the Banner report and the basics of running a report in Banner.
Summary of the Steps:
- Run a Banner CSV report (such as SWRLELA or SWRGRAF) to printer DATABASE, and save it. [If you receive a report output from Information Technology, start at #2.]
- Open the File in MS Excel and modify, sort, review data as needed and then save file.
- Open MS Word, create a mail merge and use your saved MS Excel file as the data source and print to the labels.
Detailed Steps: These may look intimidating and sound confusing. The best way to learn is by doing - just follow the steps with your "fingers on the keyboard". Once you go through the process it makes sense and is easy to repeat.
- BANNER [If you are running the report yourself]
- In Banner type the name of the job (the report name) in the Direct Access Box on the Main Menu, or go to GJAPCTL (Process Submission Control Form) and type in the job name.
- In GJAPCTL type DATABASE in Printer box
- Select the parameters and associated values for the job you are running. [Standard reports allow you to select populations based on parameters such as Term or College. Each report has its own set of parameters.] Click Values flashlight to search for any parameters available if you are unsure. Also be sure read the gray text just below the Parameters Box for specific instructions about the parameters.
- Submit the job.
- Check the bottom left hand gray banner for the job name, number and file type, such as swrgraf_497334.log, swrgraf_497334.lis, or an error message that indicates a problem with the report you just tried to run.
- Click Review Output in Options Menu, which takes you to the Saved Output Review Form - GJIPREVO. The report name will be in Process and the job number will be in the Number Box.
- You know the job is complete when you are able to click once to the right of the dividing line or press the tab key until the File Name box appears on the right. Please note that some reports will only take a few minutes to run while others may take much longer.
- Ignore the information line error message of "There is no saved output…" it just means the report is not completed yet.
- Then double click in File Name box.
- Then click on Save to File in Options Menu. Please note that the information line will tell where the file is saved (i.e. H or C file) depending on how you have your system set-up in the GJAJPRF - Job Submission Profile Maintenance Form.
You now have a file containing the information you need. Often the file contains more than just mailing label information. You select out the parts you need using Excel and Word.
- MS EXCEL
- Open MS Excel
- Click File and then Open to find and open your file.
- Because the file output from Banner is not in Excel format, when you open the file, Excel will ask you some questions:
- Open file and click Next,
- then click on the Tab box to deactivate it and
- click on the Comma box, [since your file is a CSV (comma separated value) file] to activate it and then click on Finish.
- If one of your columns contains ID number, you should use Excel's Format tool to convert it to "TEXT" in order to preserve any leading zeroes contained in the ID. [If Excel sees 00236475 and thinks it is a number, it will only display 236475]
- When the file opens, click Save As and save the file in the MS Excel format
- Modify, sort, or review your data. Be sure the data is what and how you want it.
- Save file again after making all your changes.
You now have the data file fine tuned for your needs. Next you use Word to merge the data from Excel into the format needed for mailing labels (or anything else).
- MS WORD
- Open MS Word
- Go to Tools in the top Menu Bar and click on Mail Merge.
- For the Mail Merge Menu:
- For 1: Main Document, first click Create, then click Mailing Labels, then click Active Window.
- For 2: Data Source, first click Get Data, then click Open Data Source, then click "Files of type" Down Arrow to change to either MS Excel Worksheets (for Excel file) or All Files (for H or C Drive file). Select your Excel data file and Open File.
- Then click on Setup Main Document. In Label Options under Product number, scroll down and select the labels you want to use, such as Avery 5160-Address which is one of the standard label formats. The label stock number is usually on the box of labels (check your labels to be sure you have the right stock number. If you do not have a box of labels with the stock number, measure the size, then scroll through the list of label options until you find one with the same size.) Then click OK.
- Under Create Labels click Insert Merge Field and select the fields (data elements) you want included on your labels. For example, click FNAME (first name), then press the Space Bar, then click Insert Merge Field and click on LNAME (last name) and press enter. Click Insert Merge Field, click STR1, press Enter, click Insert Merge Field, click STR2, Press Enter, click Insert Merge Field, click CITY, Press Space Bar, click Insert Merge Field, click ST, Press Space Bar, click Insert Merge Field, click ZIP, click OK. Use whatever data fields you want and whatever layout, spacing or design you want. Be sure to follow US Postal addressing regulations in order to save postage costs and to improve delivery time.
- For 3: Merge Data with the document by clicking Merge.
- To print be sure your label stock is in the printer, then click File, click Print, and select printer and print your labels.
- Be sure you save your MS Word file.
As you go through this process, you will note that you can use the same steps to generate personalized letters ("Dear Susan" instead of "Dear Student") and that you can also use these techniques to insert user-specific paragraphs or words into a letter ("You need 13 credits to graduate" rather than "You need more credits to graduate".)
Getting Help
- For technical assistance please contact the HelpDesk at 847-465-0575 ext. 4357 or email them at Helpdesk@nl.edu.
- Save money - for help with US Postal Service addressing regulations, contact Archer Management at the Copy Centers/Mail Centers at x4393.
- For additional information about the use and options within MS Excel and MS Word be sure to use the Help Menus which contain many useful tips.
- Another source of tutorial assistance with Word and Excel is Atomic Learning, a series of "viewlets", available at http://vtrc.nl.edu/AtomicLearningPasswords.doc (this page includes the login IDs and passwords for the system).
|
