Creating & Downloading Files In Excel Format

FOCUS provides a variety of formats for you to use when creating hold files to be downloaded into a spreadsheet on your PC.  We recommend you use the LOTUS format to create a comma delimited file, but you can also use EXCEL to create a binary file that captures all the report output including headings.  Before using this format, there are 3 important items to note:

 

  • When fields defined as Smart Dates are downloaded using the EXCEL format, the cells cannot be formatted to display as a date.  For example, 01/15/2006 will download as '1152006' and cannot be changed to display as 'January 15, 2001'.  For this reason, we advise using the LOTUS format when downloading data containing date fields.

  • Hold files in an EXCEL format cannot be browsed, edited or printed before downloading.

  • The file transfer settings for downloading a hold file in an EXCEL format differ from those used to download hold files in a LOTUS format.  Select a binary scheme instead of text, and be sure to provide a file name on your PC with a .xls extension.  

 

The steps below provide a general outline of the process and presume you are familiar with the ad hoc reporting functions.  For detailed information on creating reports, using hold files and downloading, refer to the CIRS User Manual or the Basic CIRS Reporting workbook.  

Step 1:

Create a new report request, or modify an existing report request in your library, and include the following commands ON TABLE HOLD AS filename FORMAT EXCEL where filename is the name of a hold file (i.e., PERMSML, PERMLRG, PERMRPT). For example:  

 

ex ac

table file ac

print ac:ssamask ac:lastname ac:posit16 ac:fte ac:salary

on table hold as permlrg format excel

end

Step 2:

Execute your report request using online or batch execution.  With either method, a message will be generated that indicates the number of records retrieved, the number of lines and the type of file saved.  For example:

 

 NUMBER OF RECORDS IN TABLE=    1451     LINES=    1451

 EXCEL FILE SAVED...

 >

Step 3:

Access the hold file and initiate the download process to transfer data from CIRS to your PC.  For the file transfer settings, select a binary scheme instead of text and provide a filename on your PC with a .xls extension.  For example:

 

image3.gif

 

Note:  If your emulation software is not Attachmate Extra as illustrated above, refer to your specific file transfer software documentation or contact your campus IT support.

Step 4:

Locate and open the downloaded file on your PC. You do not need to open your Excel application before opening the downloaded file, nor do you need to use the import wizard.  

 

image4.gif