Exporting to Excel

Creating a Report to Use
Find a Report that contains most of the information you need. In this example, we will print an Inventory Management report,

· Select a Packages » Inventory Management System » Report Manager » Inventory Activity Reports

· Select Option 2, Report Writer Features and click the Select button.

· Select Insert to add the new report.

· Select the appropriate choice (Standard or User-Defined) for your base report.

· Highlight the base report and click the Select button.

· Select Generate a Text File

The three types of report/file that can be created are:

1 – Generate a Report – Creates a report for output to a printer, the screen, or a Report Spool

2 – Generate a Text File – Creates a text file with the extension of .fil or .csv.

3 – Append to a Text File – Appends (or adds) to a text file created previously. This option will create a new text file if the File Name is not found within the dataset files.

The following window will appear.

· Enter a Description for this report.

· You may specify a valid full file Directory path with up to 65 characters for this file. If you would like to have the report created in the current dataset directory, leave the Directory field blank.

· Enter the File Name for this report. This will also allow you to specify the extension of any output text file generated by the Report Writer. The you have control over the name of the file extension.

· Answer the following questions:

· Create Data Only Output Text File? By answering yes, the report will exclude headings, titles, and blank lines from the report.

· Leading Sign on Numeric Field? By answering yes, the report will allow the negative sign to come across in front of the negative number.

· Show Positive Sign on Numeric Field? By answering yes, the report will allow the positive sign to come across in front of the positive number.

· Zero Fill Numeric Fields? By answering yes, all fields deemed numeric to be zero filled to the length of the field and if empty, the field will fill with zeros.

· Imply Decimal on Numeric Fields? By answering yes, you will allow decimal places to be put in automatically.

· Click the OK button to continue.

· If you have In the Data Only File Format field, enter one of the following choices.

· Standard Export Format – Used for most exports. Creates a .fil file.

· Comma Delimited Fields – Used for exports to be coma delimited when brought in through the Import Wizard. Creates a .fil file.

· Word Perfect Merge File – Used for a merge of the file into a Word Perfect document. Creates a .fil file.

· Comma Separated Values – Used for export fields to be comma separated. Creates a .csv file.

· Spreadsheet With Headings - This allows you to design a spreadsheet that allows anyone to process and link to Excel (or whatever software application is associated with .csv files). This ability will generate a random csv file, process the data, and then end up in Excel with the data and headings supplied in the spreadsheet. These randomly generated files will automatically be cleaned up by the system if they still exist after 3 days.

Do not use the Comma Delimited Fields or Comma Separated Values options if exporting Names or other reports where a comma is used within the field.

· Click the OK button to continue.

· Click Save/Exit to save the report definitions.

You must add a space to each printed field for the commas when using Comma Delimited or Comma Separated Values. For the Spreadsheet with Headings, you must allow 3 spaces to each printed field for commas.

Exporting the Report
Now that you have created the report and specified that it will be saved as a text file, you will need to export the report.

Note- These next steps are used when not using the Spreadsheet With Headings, data only format. The option for Spreadsheet with Headings will automatically link to Excel (or whatever software application is associated with .csv), when running the report out of Agris.

· Select a Packages » Inventory Management System » Report Manager » Inventory Activity Reports

· Highlight Option 1, Print Inventory Activity Report and click the Select button.

· Choose User Defined Report and click the Select button.

· Highlight the report that was previously created to print to a file and click the Select button.

· You will be asked if you want to adjust the parameters. Click Yes or No, depending on your decision.

· Prior to creating the new file, a window will display asking for verification. Note that the message displayed will vary depending on the location and file name entered for this report. If no directory path was specified, the report will default into the current dataset directory.

· Click the OK button.

· A progress bar will display as the report is printed to the file.

· Press the ESC key until the toolbar is active.

Importing to Excel
We will be discussing how to import the file that was exported out of V9.2 without specifying a directory. The process discussed here will be similar to what you would follow regardless of where the report was saved. You would just navigate to the directory that you specified.

· Select Start » Program Files » Excel (or the path where your Excel program is located)

· Select File from the menu bar at the top of the window.

· Select Open from the drop down list.

· Navigate to the Agris directory.

· Highlight Datasets and click the Open button.

· Select the appropriate dataset number where the report is located and click the Open button.

Line Callout 3 (No Border): Click on the drop for Files of type and select All Files.

· Go to the Files of Type field at the bottom of the screen and change the file type to All Files.

· Highlight the file name you created and click the Open button. (You may need to scroll through the list as the files are listed alphabetically.)

· The following window will display. In the next window, choose Fixed Width or Delimited (depending on the Data Format you chose in V9.2) and click the Next button.

· Decide how you would like to divide out the information keeping in mind that each line is a cell divider in Excel.

If you are using a standard report and transferring it to a file, V9 puts a sign for a negative number at the end of the number and Excel does not recognize this.

If you have negative numbers in your report, you must put a line at the end of the amounts that could be negative, leave a space, then put a line one space after that.

· Scroll through your report to make sure you are separating all of the information.

· Click the Next button to access the Column Data Format window.

The column titles will need to be retyped, as they will be broken up.

· The Column Data Format should be General.

· Click the Finish button.

· You should now see your information in an Excel format.

Cut and paste any one row of the column title information you might need to properly label the columns before deleting all the excess information.

· Click on the Row 1 label to highlight the entire row. Right-click inside this row and select Insert from the drop-down menu.

Line Callout 3 (No Border): Highlight Row 1, right-click and select Insert from the drop-down menu.

· Enter the header information in the first row for each column.

Changing Negative Display
Negative numbers after importing into Excel are displayed with a negative sign “-“. If you would prefer to have parenthesis around the negative numbers, use the following steps.

· Select the whole spreadsheet by clicking on the Select All button.

Line Callout 3 (No Border): Click the Select All button to highlight the entire spreadsheet.

· Click on Format from the menu bar.

· Select Cells to display the Format Cells window.

Line Callout 3 (No Border): In the Negative number section, select the format Excel should use to display negative numbers.Line Callout 3 (No Border): Select Number from the Category section of the window.

· Highlight Number from the Category section.

· In the Negative numbers window, highlight the format you would like Excel to use to display negative numbers. Excel gives you four pre-defined options to choose from. If you would like negative numbers to be displayed with the parenthesis, highlight that option and click the OK button.

· Click the OK button.

The display of negative numbers will change from this:

To this (or whatever format you chose):

Saving the Spreadsheet
· To save the spreadsheet, click File on the menu bar.

· Select Save As from the File drop down list.

· Enter the file name for the report.

· Click on the Save as Type drop-down and select Microsoft Excel Workbook.

· Click the Save button.