.Xls Template by jyk19184

VIEWS: 41 PAGES: 11

.Xls Template document sample

More Info
									                                                                          Jiasoj
Assets Conversion Template Instructions                                   Jdfljsdoi
                                                                                             sl




This document provides an overview of the Asset Management (AM) Conversion Templates, as
well as a set of instructions on how to save the files in the proper Core-CT format. This
document also details the steps necessary to import a data file (EIS) in the Core-CT accepted
CSV format into the Conversion Templates for data modification.

Overview
The AM Conversion Templates are a tool for the converting agencies to use that will enable
them to provide their asset data to Core-CT in the Core-CT accepted format. The use of the
templates is optional, as some agencies may choose to program an extract from an existing
legacy system. There are three AM Conversion Templates:

    assets_conversion_te mplate_ctam5720_fin.xls – This is the template for asset financial
     information. This file is required, and should be completed first.
    assets_conversion_te mplate_ctam5720_phy.xls – This is the template for physical asset
     information. This file is optional.
    assets_conversion_te mplate_ctam6072.xls – This is the template for Core-CT custom
     fields. This file is optional. If a SERIAL_ID value is supplied in the Financial file, then
     a corresponding row must be entered in this file. (Refer to the data maps for a more
     detailed explanation.)

In order for the Asset Conversion Templates to work successfully for agencies as well as Core-
CT, the following guidelines must be followed:

    Only one business unit should be submitted in each file.
    Agencies must submit at least a Financial file. If agencies choose to load Physical
     information and custom fields they must submit separate files for that data.
    Utilize the data maps you completed for Task 104 to assist in the completion of the data
     files. The Conversion Templates include the Field Name and Field Type in the column
     headings, and a ‘*’ indicates all required fields. Pay particular attention to:
          o Field Type
          o Field Length
          o Required Fields
    When submitted for conversion, files need to be named as described below, where
     ‘XXXXX’ is the AM Business Unit included in the file. For example, if the business unit
     you’re submitting is ‘DASS1’, then the Financial information file should be named
     ‘CTAM5720_FIN_DASS1.CSV’ upon submission. If the files are not named
     according to the naming convention, they will not be accepted by Core-CT.
           -   Asset Financial Information – ‘CTAM5720_FIN_XXXXX.CSV’
           -   Asset Physical Information – ‘CTAM5720_PHY_XXXXX.CSV’
           -   Asset Custom Fields – ‘CTAM6072_XXXXX.CSV’
    The data you submit should match the field types for each field.




                                          Page 1 of 11
                                                                          Jiasoj
Assets Conversion Template Instructions                                   Jdfljsdoi
                                                                                           sl




    The field type and length are indicated in each column of the first row of the templates.
     See the example below:




    Make sure the values you enter are less than or equal in length to the maximum size of
     each field.
    Do NOT modify the formatting of the Excel templates or fields in each of the templates.

Using the Asset Conversion Template to Enter Your Asset Data

Enter your asset data into the Asset Conversion Templates as you would for any MS Excel
spreadsheet. Each row should represent the data for one row of information. For example:
     For many agencies, the Assets_Conversion_Template_CTAM5720_FIN.xls (asset
       financial information) spreadsheet should include one row for each asset being
       converted. However, agencies converting assets with multiple acquisition detail lines
       should enter a row for each acquisition line, as described in the data map.
     The Assets_Conversion_Template_CTAM5720_PHY.xls (asset physical information)
       spreadsheet may optionally include one row for each asset.
     Assets_Conversion_Template_CTAM6072.xls (asset custom information) spreadsheet
       may optionally include one row for each asset. If SERIAL_ID is supplied in the
       Financial spreadsheet, then a corresponding row must be entered in this file. (Refer to
       the data maps for a more detailed explanation.)

You can continue to enter, maintain and correct the information as needed throughout the Mock
Conversion process to get your data ready for the Dress Rehearsal and final Production
Conversion. Please be sure to save your work and make backups of your spreadsheets
frequently.

       1. Saving Your Work
       As you are working on your Conversion Template, save the file as you normally would,
       using ‘File  Save’. You may receive the following message. If you do, click ‘OK’ and
       the ‘Save As’ dialog box will be displayed.




                                         Page 2 of 11
                                                                         Jiasoj
Assets Conversion Template Instructions                                  Jdfljsdoi
                                                                                          sl




       The Conversion Templates provided by Core-CT are in an Excel format, so this will
       automatically save the file with an ‘.xls’ extension. So, as in the example show below,
       DOC might save their Template for Business Unit DOCM1 as:

              Template_CTAM5720_FIN_DOCM1.xls




       2. Make a Backup Copy of Your Spreadsheets
       Always be sure to make a backup copy of your Conversion Templates. The Conversion
       Template spreadsheets contain the data that will be loaded into Core-CT and represents
       your agency’s records of the information captured for reporting and audit requirements.
       Follow your agency’s procedures for maintaining backup copies of agency
       production information. This might include such procedures as backing up data to
       tapes, burning copies to CD’s, copying files to backup storage devices, copying files to
       backup directories, and/or sending copies to off site storage facilities.

Saving the Asset Conversion Template in a ‘.CSV’ Format
This section provides a set of instructions on how to save the Asset Conversion Templates in the
comma separated file format (‘.CSV’) accepted by the Core-CT Conversion team. The
following steps should be executed for each of the three Conversion spreadsheets when you are
ready to send a new or updated CSV file to the Conversion team.

       1. Save Your Work
       As you are working on your Conversion Template, save the file as you normally would,
       using ‘File  Save’. You should also do this now, just before you are going to save the
       Template in CSV format.




                                         Page 3 of 11
                                                                            Jiasoj
Assets Conversion Template Instructions                                     Jdfljsdoi
                                                                                              sl




    2. Make a Copy of Your Spreads heets
    Before you proceed to Step 3, it is suggested you save a second copy of your
    spreadsheets with today’s date appended. Use the command ‘File  Save As…’ and
    append the current date to the filename in the ‘File name’ field, then click Save.




    So, continuing with the DOC example, they would now have two files saved:
           Template_CTAM5720_FIN_DOCM1.xls
           Template_CTAM5720_FIN_DOCM1_10-25-04.xls




    Having a dated copy of the spreadsheet will allow you to refer back to your data as it was
    on the date you produced and submitted a CSV file. The original copy (without a date)
    can be used to continue data entry, to maintain data, and to correct the information. You should
    use the original copy throughout the Mock Conversion process to get your data ready for the
    Dress Rehearsal and final Production Conversion.




                                         Page 4 of 11
                                                                      Jiasoj
Assets Conversion Template Instructions                               Jdfljsdoi
                                                                                     sl




    3. Preparing the Spreadsheet to be Saved
    Once you complete the data entry and have saved a copy of your Conversion Template
    spreadsheet, you need to modify the template by removing the column headings. To
    delete the column headings, complete the following steps for each spreadsheet file:
         Click on the column heading row (listing the field names across the top of the
            spreadsheet), row ‘1’, so that the entire row is highlighted.




        Click on ‘Edit  Delete’. This will delete the header row.




        Save your file again by clicking on ‘File  Save’.

    4. Saving in a ‘.CSV’ Format
    To save the data files in the Core-CT accepted ‘.CSV’ format, complete the following
    steps:
         Click on ‘File  Save As’. The window that is displayed will default to a ‘Save
           as type’ of ‘Microsoft Excel Workbook (.xls).’
         Change the ‘Save as type’ value to ‘CSV (Comma Delimited)(*.csv)’ as shown
           below. Make sure the ‘File name’ value now ends with an extension of ‘.csv’
           instead of ‘.xls’. The file should be named using the Core-CT accepted
           convention, which is described in the Overview section above. For example, this
           DOC file will be saved as ‘CTAM5720_FIN_DOCM1.csv’.




                                     Page 5 of 11
                                                                           Jiasoj
Assets Conversion Template Instructions                                    Jdfljsdoi
                                                                                             sl




        Click ‘Save’. If you receive the warning message, shown below, click ‘Yes’.




        Close the template by clicking on ‘File  Close.’ You may receive another message
         asking if you want to save the ‘.CSV’ file. It should already be saved by now, but click
         ‘Yes’. This file should now be ready send to Core-CT.




                                        Page 6 of 11
                                                                           Jiasoj
Assets Conversion Template Instructions                                    Jdfljsdoi
                                                                                             sl




          If you receive the warning message, shown below, click ‘Yes’.




          You have successfully saved a ‘.CSV’ format file!

Importing Your Extract Data into Excel for Editing

      Many agencies will receive an extract file from the EIS system, while some will use
      custom programs to extract their asset data from legacy systems, both to produce a set of
      Core-CT accepted ‘.CSV’ files. Some agencies may find it useful to import the ‘.CSV’
      extract files into Excel to work on them before submitting the files for conversion. This
      allows agencies to easily review the data, to update fields that could not be automatically
      filled in by a program, and to clean the data with the help of Excel’s features.

      Do NOT simply open the extracted ‘.CSV’ files with Excel by using the File  Open
      command. If you directly open a file, rather than import it, Excel will save the files in a
      way that is not compatible with Core-CT conversion. You must use Excel’s import
      function for your file to work properly.

      To import a ‘.CSV’ file into Excel, follow these steps:
           Open the Core-CT provided Assets Conversion Template that matches the type of
            ‘.CSV’ data you want to import (e.g. financial, physical, or custom fields).




          Click on ‘Data’  ’Import External Data’  ‘Import Data’.



                                          Page 7 of 11
                                                                  Jiasoj
Assets Conversion Template Instructions                           Jdfljsdoi
                                                                                      sl




        The ‘Select Data Source’ window appears. Select the ‘.CSV’ format file you
         want to import, e.g. CTAM5720_FIN_DOCM1.csv.




        Click ‘Open’. A new window appears, titled the ‘Text Import Wizard – Step 1 of
         3’. Keep the default values (as shown below). Ensure the ‘Original Data Type’
         field is set to ‘Delimited’.




                                    Page 8 of 11
                                                                          Jiasoj
Assets Conversion Template Instructions                                   Jdfljsdoi
                                                                                            sl




        Click ‘Next’. In step 2 of the wizard, all checkboxes should be unchecked except
         the ‘Comma’ box. The ‘Text Qualifier’ field should be set to the double quotes
         (e.g. ‘ ” ’).




        Click ‘Next’. In step 3 of the wizard, the first column in the ‘Data Preview’ box
         will be highlighted. You need to select all of the columns in the preview box. To
         do this, move the horizontal scroll to the far right side, hold down the ‘Shift’ key,
         click the last column on right, and release the ‘Shift’ key. All columns should be
         highlighted; if not, scroll to the far left, click the first column on the left, and try
         again.
        Select the ‘Text’ option in the ‘Column data format’ box, then click ‘Finish’.




                                       Page 9 of 11
                                                                    Jiasoj
Assets Conversion Template Instructions                             Jdfljsdoi
                                                                                     sl




        In the ‘Import Data’ window that appears, change the ‘Existing worksheet’ field
         to ‘=$A$2’ (without the quotes). This will ensure the data being imported is
         placed below the Conversion Template column headings.




        Click the ‘Properties…’ button. In the ‘External Data Range Properties’ dialog
         box, uncheck the ‘Adjust column width’ checkbox. Click ‘OK’ when you’re
         done, and click ‘OK’ in the ‘Import Data’ window.




                                    Page 10 of 11
                                                                      Jiasoj
Assets Conversion Template Instructions                               Jdfljsdoi
                                                                                       sl




        The data import is finished. The spreadsheet should now be filled with the
         ‘.CSV’ file data. You should save the spreadsheet before going any further.
         Directions for saving spreadsheets can be found in the ‘Using the Asset
         Conversion Template to Enter Your Asset Data’ section of this document.




        When you are ready to submit the file to the Conversion team in ‘.CSV’ format,
         refer to the following section of this document for directions: ‘Saving the Asset
         Conversion Template in a ‘.CSV’ Format.’




                                     Page 11 of 11

								
To top